Situação:

Estou usando o conteúdo da célula "D2" para nomear a planilha. Acabei de editar o código abaixo para capturar várias entradas problemáticas, postar uma mensagem e fornecer um valor que funciona e é óbvio que precisa ser substituído.

Código:

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim BadData As Boolean
    
    If Intersect(Target, ThisWorkbook.Sheets(2).Range("D2")) Is Nothing Then
        Exit Sub
    End If
    
   BadData = False
        
    If Len(Target.Value) > 31 Or IsEmpty(Target.Value) Then
        BadData = True
    ElseIf InStr(Target.Value, "[") <> 0 Then
        BadData = True
    ElseIf InStr(Target.Value, "]") <> 0 Then
        BadData = True
    ElseIf InStr(Target.Value, "*") <> 0 Then
        BadData = True
    ElseIf InStr(Target.Value, "?") <> 0 Then
        BadData = True
    ElseIf InStr(Target.Value, "\") <> 0 Then
        BadData = True
    ElseIf InStr(Target.Value, "/") <> 0 Then
        BadData = True
    ElseIf InStr(Target.Value, ":") <> 0 Then
        BadData = True
    End If
    
    If BadData Then
        MsgBox "You have entered an unacceptable ID value." & vbCrLf & _
                vbCrLf & _
                "The Site No entry must:" & vbCrLf & _
                vbCrLf & _
                "1)  Be a value less than 31 characters long" & vbCrLf & _
                "2)  Not contain the following characters:  /, \, :, ?, *, [, ]" & vbCrLf & _
                "3)  Not be a blank value" & vbCrLf & _
                "4)  Be compatible with excel sheet name restrictions"
                
        Target.Value = "Enter Site ID"
    End If
    
    ThisWorkbook.Sheets(2).Name = Target.Value

End Sub

Embora o código acima pareça funcionar, tenho certeza de que há várias outras maneiras de atingir o mesmo resultado final. A declaração IF múltipla resultando no mesmo valor me incomodou um pouco.

Originalmente, pensei em colocar todas as verificações individuais de InStr em uma declaração OR longa com as verificações Len e IS Empty, mas escolhi não fazer isso, pois imaginei que seria um problema para outra pessoa tentar descobrir o código. Percebi que pode haver algum benefício em ELSEIF impedir que verificações subsequentes sejam feitas se uma verificação TRUE resultar.

Pergunta

Eu queria saber se existe uma maneira melhor de fazer isso?

Acabei de fazer uma pergunta sobre como obter o número da coluna para um nome de coluna conhecido e a resposta foi.index

Estou tentando retornar a última linha usada para uma tabela nomeada inteira. Se eu usasse o método .end(xlup) com base na planilha, ele sempre pararia na borda da tabela, independentemente de a célula inferior da tabela estar vazia ou não. Em vez disso, alterei para o seguinte:

Meu código:

Function TableLastUsedRow(TheTable As ListObject) As Long

Dim RowCount As Long, ColCount As Long, TableLastRow As Long

    With TheTable
        TableLastRow = .ListRows.count
    
        For ColCount = 1 To .ListColumns.count
            If IsEmpty(.DataBodyRange.Cells(TableLastRow, ColCount)) Then
                RowCount = .DataBodyRange.Cells(TableLastRow, ColCount).End(xlUp).Row
                If TableLastUsedRow < RowCount Then TableLastUsedRow = RowCount
            Else
                TableLastUsedRow = TableLastRow
                Exit Function
            End If
        Next ColCount
    End With

End Function

Isso funciona muito bem para retornar a referência de linha da planilha. Como você obtém o número da linha da tabela?

Linha de código atualizada

RowCount = .DataBodyRange.Cells(TableLastRow, ColCount).End(xlUp).ListRow.Index

Isso está gerando o seguinte erro:

Eu uso o seguinte e ele retorna a coluna da planilha em vez do número da coluna do objeto de tabela/lista:

InputTable.ListColumns(strDate).Range.Column

InputTable is a listobject

Atualmente, ele está retornando 10 (número da coluna da planilha) quando deveria estar retornando 8 (número da ListColumns)

Pergunta: como faço para alterar minha referência para retornar a coluna da tabela em vez da coluna da planilha

Essa pergunta parece corresponder exatamente ao que estou procurando em termos de declaração de uma variável que pode ser chamada e retém informações de qualquer lugar.

Tenho um userform que fornece um número de projeto e um caminho de arquivo como string e tenho outro como booleano para determinar se o botão OK ou CANCELAR foi selecionado e como proceder quando o processo retorna ao sub. Da pesquisa que estava fazendo, parece indicar que a maneira mais fácil de fazer isso é por meio de algo chamado variável global. Embora essa possa ser a maneira mais fácil, é a maneira correta?

Tentei colocar minha declaração de variável no topo da folha de código antes do primeiro sub/fun na Sheet3, onde está a maior parte do meu código e onde está o código de clique do botão para iniciar o formulário do usuário. Também tentei colocá-lo no topo do código em ThisWorkBook. E também tentei colocá-lo no topo da página de código para o formulário do usuário associado.

Eu só os declaro em um lugar. Eu não os declaro em vários lugares ao mesmo tempo.

Quando declaro na Planilha3 recebo um erro em um sub colocado no código do userform

Código associado:

Private Sub OKBtn_Click()
    
    strPPTFilepath = PPTPath.text
    strProjectNumber = ProjectNoTextBox.text
    bCancelled = False
    
End Sub

Quando declaro em ThisWorkBook recebo:

E o código que causa o erro está localizado na Planilha3 e é o seguinte:

Private Sub LoadPPT_Click()

Dim frm As PPT_Picker_Form
Dim wbPPT As Workbook
Dim wsPPT As Worksheet
Dim rngTopLeft As Range
Dim lngLastUsedRow As Long
Dim lngLastCo As Long

'Public strPPTFilepath As String
'Public strProjectNumber As String
    
    Set frm = UserForms.Add(PPT_Picker_Form.Name)
    frm.ListData = ThisWorkbook.Worksheets("Project Numbers").ListObjects("Project_Number_List").DataBodyRange
    
    frm.show
    
    Unload frm
    
    If bCancelled Then '<----this is the undeclared variable
        Exit Sub
    End If
    
    'Define PPT Table
        
    Set wbPPT = Workbooks.Add(strPPTFilepath)
    Set wsPPT = wbPPT.Worksheets("Fee Estimate")
    
    'Find top left corner
    With wsPPT.Cells
        Set rngTopLeft = .Find("WBS Code", LookIn:=xlValues)
        
        If rngTopLeft Is Nothing Then
            'nothing found
            'add a manual cell picker routine
          
        End If
    End With
    'Find last row
    'find Right edge
    
   ' strProjectNumber

End Sub

Quando declaro isso na área de código do formulário de usuário, recebo o mesmo erro acima no mesmo local.

Pergunta

Qual é a maneira correta de passar informações de um formulário para um sub na planilha3?

Atualizar

Puta merda, não acredito que esqueci de mostrar o código para declarar minhas variáveis, então aqui está:

'set it so that all subs and functions require variables to be dimensioned
Option Explicit

'variable available across all modules?
Public strPPTFilepath As String
Public strProjectNumber As String
Public bCancelled As Boolean

Estou tentando reutilizar uma consulta de energia para extrair informações de outra consulta de energia. Parece que há várias maneiras de fazer isso. Vejo duas maneiras de fazer isso a partir desta pergunta . Também vejo isso em minhas próprias planilhas. Na minha planilha, aparentemente tenho:

Source = Excel.CurrentWorkbook(){[Name="Project_List"]}[Content],

Em outras consultas tenho:

Source = Project_List,

Existe alguma vantagem em um método ou outro?

No final das contas, o que espero conseguir é consultar os dados uma vez na rede, despejá-los na tabela A e, nas consultas subsequentes, continuar consultando a tabela A e não extrair os dados pela rede para as consultas subsequentes.

Notei que quando eu estava criando minha consulta mais recente usando o Source = Project_List,, a cada passo da consulta de acompanhamento ele estava carregando, na verdade, recarregando todos os arquivos que a consulta Project_list carrega.

Isso é apenas parte do processo de construção? Ou se eu tiver uma consulta A que carrega pela rede e 5 consultas de acompanhamento que se referem à consulta A, o Power Query / Excel estará lendo a consulta pela rede 1 vez ou cinco vezes?

Qual é a maneira correta no Power Query de se referir a uma consulta existente e reduzir a extração de dados pela rede?

Como remover os últimos X caracteres de uma string em uma coluna?

Estou tentando gerar uma lista de nomes de arquivos amigáveis ​​ao usuário que são usados ​​na geração de uma tabela do Power Query. No entanto, ao ler a lista de comandos de texto m, não vi uma abordagem direta para fazer o equivalente a:

 Text.left(#"previous table"{"Filename'}, Text.length(#"previous table"{"Filename'} - 11)

(com base no meu conhecimento do Excel).

Como vi que meus nomes de arquivo têm comprimento variável, mas sabia que sempre queria eliminar os últimos 11 caracteres, pensei que talvez pudesse usar Text.reverse(#"previous table"{"Filename'})e, em seguida, fazer uma divisão na posição 11 e apenas manter o lado direito da divisão e, em seguida, fazer outra reversão. No entanto, ainda não conheço bem a sintaxe m e tudo o que obtive foi um erro quando tentei fazerText.Reverse

Meu código m:

let
    Source = Forecasting,
    #"Removed Other Columns" = Table.SelectColumns(Source,{"Source.Name"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Other Columns"),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Duplicates",{{"Source.Name", "Submitted Reports"}}),
    Custom1 = Text.Reverse(#"Renamed Columns"{{"Submitted Reports"}})
in
    Custom1

Dados de exemplo quando chegamos à etapa Colunas Renomeadas:

Submitted Reports
Bug Bunny 241008923443.xlsm
Daffy Duck 241130234521.xlsm
Wyle E. Cayote 240909124312.xlsm

Resultados desejados:

Submitted Reports
Bug Bunny 241008
Daffy Duck 241130
Wyle E. Cayote 240909

Fundo

Tenho um Power Query que combina várias planilhas do Excel com o mesmo nome de tabela no mesmo diretório em uma única tabela que usarei como dados de origem para uma tabela dinâmica. Ou pelo menos eu usei...

Problema

O problema é que houve uma revisão na tabela nomeada para alguns dos arquivos e uma nova coluna foi adicionada. Basicamente, é uma mistura de arquivos de dados que continuam mudando. Preciso adicionar essa coluna aos arquivos que estão faltando.

O que eu tentei

Passei um bom tempo pesquisando no Google e finalmente encontrei um vídeo no YouTube que entendo até certo ponto. Ele basicamente diz para adicionar uma IFinstrução que verifica se a coluna existe. Se existir, apenas retorne a tabela original. Caso contrário, adicione a coluna.

Meu código M está atualmente

let
    Source = Excel.Workbook(Parameter1, null, true),
    Forecast_Table_Table = Source{[Item="Forecast_Table",Kind="Table"]}[Data],
    #"Added Custom1" = Table.AddColumn(Forecast_Table_Table, "Missing", each if Table.HasColumns(Navigation,"LOS") then Navigation else Table.Combine({Navigation, Table.FromRows({},{"LOS"})})),
    #"Added Custom" = Table.AddColumn(#"Added Custom1", "Custom", each [Project No]&[Task No]&[Office]&[Staff]),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each [Custom] <> null and [Custom] <> ""),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Custom"})
in
    #"Removed Columns"

Foi aqui que tentei adicionar a instrução if como eles fizeram no vídeo

porém quando seleciono essa etapa, ele mostra esse erro:

Pergunta

Como posso corrigir minha IFdeclaração para que ela adicione uma coluna chamada "LOS" se ela ainda não estiver lá?

Atualizar

Minha lista de etapas aplicadas é a seguinte e a etapa verde é onde adicionei a IFdeclaração

O que estou tentando fazer

Estou tentando determinar a primeira coluna em uma tabela nomeada que tem uma data.

O que eu tentei

Como todos os dados em uma linha de cabeçalho de tabela são armazenados como strings, imaginei que teria que primeiro retirar os números de data como uma string e depois convertê-los em um número. Depois, verificar se é um erro caso não haja números na string extraída.

Meu código para encontrar a coluna da tabela é o seguinte e explode na instrução IF.

For Each HeaderCell In LO.HeaderRowRange
    If Not IsError(CLng(Left(HeaderCell.value, 4))) Then
        lngFirstDateCol = HeaderCell.Column
        Exit For
    End If
Next HeaderCell

Adicionei cada parte do check à janela watch. HeaderCell.Value retorna "Project No". Left retorna "Proj". CLng mostra como . IsError mostra como .

Minha linha de cabeçalho de exemplo se parece com a seguinte

Proj No | TTTT | Office | Staff | RFP | 2024/10/4 | 2024/10/11

Com o cabeçalho Example gostaria de retornar o valor de 6

Pergunta

Qual é a abordagem correta para encontrar a primeira coluna que é uma coluna de data? Atualmente, eu a tenho codificada.

Estou tentando vincular tardiamente o processo de abertura de uma mensagem do Outlook para relatar um problema ou uma sugestão para uma planilha. Estou tentando preencher previamente os destinatários TO: e CC:, Linha de Assunto e as primeiras linhas do corpo da mensagem. Não quero enviar a mensagem, pois preciso que o usuário preencha o corpo da mensagem com seus pensamentos.

Encontrei um código que foi originalmente configurado usando early binding. Então, comecei a mutilá-lo para que, esperançosamente, atendesse às minhas necessidades. Então, eu o massacrou tentando fazê-lo funcionar com late binding.

Quando passo pelo código, recebo um Run-time error '13': Type mismatchna segunda linha de criação de objeto que estou supondo que está realmente tentando criar a mensagem. Tentei alterar o olMailItem para 0. Isso resulta em um erro activeX: Run-time error '429': ActiveX component can't create object.

Eu Option Explicitconfigurei.

Private Sub ReportBug_Click()

Dim objOutlook As Object
Dim objOutlookMsg As Object
Dim objOutlookRecip As Object
Dim olMailItem As Object
Dim Recipients As Object

    Set objOutlook = CreateObject("Outlook.Application")
    Set objOutlookMsg = objOutlook.CreateObject(olMailItem)
    
    With objOutlookMsg
        Set Recipients = objOutlookMsg.Recipients
        'Set TO: recipient
        Set objOutlookRecip = .Recipients.Add("[email protected]")
        objOutlookRecip.Type = 1 '1 for to
        'Set CC: recipient
        Set objOutlookRecip = .Recipients.Add("[email protected]")
        objOutlookRecip.Type = 2 '2 for cc
    
        'Set Subject Line
        .Subject = "Forecasting Sheet Bug / Recommendation from " & Me.Cells(4, 2).Value
        'start message body
        .HTMLBody = "To whom it may concern," & vbCrLf & vbCrLf & "With respect to the forecasting sheet I noted the following:" & vbCrLf
        
        'Resolve each Recipient's name.
        For Each objOutlookRecip In .Recipients
          objOutlookRecip.Resolve
        Next
        
        'objOutlookMsg.Send
        .Display
        
    End With
        
    Set objOutlook = Nothing
End Sub

Algumas das perguntas anteriores que analisei foram:

Este aqui é onde aprendi a trocar olTO e olCC com seus valores numéricos. Não consegui encontrar um valor numérico listado para olMailItem.

E esta que fez oMail.CC = "email address here"parecer uma opção mais simples

E este onde eu peguei o código inicial para mim

E juro que teve outro cujo link não consigo mais encontrar.

Questões

Como posso consertar meu goof S e me livrar do erro?

A resposta a esta pergunta afirma que para evitar o erro você precisa adicionar a referência a MS Scripting Run-time.

Usei essa metodologia para excluir um arquivo no meu código, mas como sei que ele será usado em vários computadores que não terão essa referência incluída, acho que tenho duas opções:

1. Use uma metodologia diferente para excluir um arquivo existente.
2. Descubra uma maneira de carregar o arquivo de referência quando o arquivo for aberto.

Este sub está vinculado a um botão ActiveX na planilha

Sub Createbackupfile()

Dim strCurrentName As String
Dim strCurrentPathAndName As String
Dim strCurrentPath As String
Dim strPreviousFilename As String

    strCurrentName = ThisWorkbook.Name
    strCurrentPathAndName = ThisWorkbook.FullName
    strCurrentPath = Replace(strCurrentPathAndName, strCurrentName, "")
    
    NewPath = "OldForecasts\"
    
    With ThisWorkbook
        .SaveAs Filename:=strCurrentPath & StrNewPath & strCurrentName, FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
    End With
    
    With New FileSystemObject
        If .FileExists(CurrentPathAndName) Then
            .deleteFile CurrentPathAndName
        End If
    End With
    
End Sub

Pergunta

O que é uma abordagem alternativa ao usoNew FileSytemObject