情况：

我使用单元格“D2”的内容命名工作表。我刚刚编辑了下面的代码，以捕获各种问题条目、发布消息并提供一个有效且显然需要替换的值。

代码：

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

虽然上面的代码似乎有效，但我确信还有多种其他方法可以实现相同的最终结果。多个 IF 语句导致相同的值让我有点困扰。

我最初想把所有单独的 InStr 检查与 Len 和 IS Empty 检查放在一个长的 OR 语句中，但最后决定不这样做，因为我觉得其他人来尝试并找出代码会很麻烦。我意识到如果检查结果为 TRUE，ELSEIF 可能会有一些好处，可以防止进行后续检查。

问题

我想知道是否有更好的方法来做到这一点？

我刚刚问了一个关于获取已知列名的列号的问题，答案是.index

我正在尝试返回整个命名表的最后使用的行。如果我使用基于工作表的 .end(xlup) 方法，它将始终停在表的边缘，无论表的底部单元格是否为空。相反，我将其更改为以下内容：

我的代码：

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

这对于返回工作表行引用非常有用。 如何获取表格的行号？

更新的代码行

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

这将产生以下错误：

我使用以下内容，它返回工作表的列而不是表/列表对象的列号：

InputTable.ListColumns(strDate).Range.Column

InputTable is a listobject

当前它返回 10（工作表列号），而它应该返回 8（ListColumns 号）

问题：如何更改引用以返回表格列而不是工作表列

就声明一个可以被调用并且可以从任何地方保留信息的变量而言，这个问题似乎与我所寻找的完全一致。

我有一个用户表单，它以字符串形式提供项目编号和文件路径，还有另一个布尔值，用于确定是否选择了“确定”或“取消”按钮，以及当进程返回到子进程时如何继续。从我所做的研究来看，似乎表明最简单的方法是通过全局变量。虽然这可能是最简单的方法，但它是正确的方法吗？

我尝试将变量声明放在代码表顶部，位于 Sheet3 中第一个 sub/fun 之前，我的大部分代码都在这里，并且用于启动用户窗体的按钮单击代码也在这里。我还尝试将其放在 ThisWorkBook 中的代码顶部。我还尝试将其放在相关用户窗体的代码页顶部。

我只在一个地方声明它们。我不会同时在多个地方声明它们。

当我在 Sheet3 上声明时，在用户窗体代码中的子部分出现错误

相关代码：

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

当我在 ThisWorkBook 中声明它时，我得到：

导致错误的代码位于Sheet3中，如下所示：

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

当我在用户表单代码区域中声明它时，我在相同的位置收到与上述相同的错误。

问题

将信息从表单传递到 Sheet3 上的子表单的正确方法是什么？

更新

天哪，我不敢相信我忘了显示声明变量的代码，所以这里是：

'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

我正在尝试重用 Power 查询来从另一个 Power 查询中提取信息。似乎有多种方法可以做到这一点。我从这个问题中看到了两种方法。我也在我自己的电子表格中看到了它。在我的电子表格中，我显然有：

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

在其他查询中我有：

Source = Project_List,

这种方法或另一种方法有什么优点吗？

最终我希望实现的是通过网络查询一次数据，将其转储到表 A 中，然后在后续查询中继续引用表 A，而不是通过网络提取数据进行后续查询。

我注意到，当我使用构建我最近的查询时Source = Project_List,，在后续查询的每个步骤中，它加载实际上重新加载了 Project_list 查询加载的所有文件。

这只是构建过程的一部分吗？或者，如果我有一个通过网络加载的查询 A，并且有 5 个后续查询引用查询 A，那么 Power Query / Excel 会通过网络读取 1 次还是 5 次？

在 Power Query 中引用现有查询并减少跨网络数据提取的正确方法是什么？

如何删除列中字符串的最后 X 个字符？

我正在尝试生成一个用户友好的文件名列表，用于生成 Power Query 表。但是，在阅读 m 文本命令列表时，我没有看到一种直接的方法来执行以下等效操作：

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

（基于我对 Excel 的了解）。

由于我看到我的文件名长度可变，但我知道我一直想消除最后 11 个字符，所以我想也许我可以使用Text.reverse(#"previous table"{"Filename'})然后在位置 11 处进行拆分，只保留拆分的右侧，然后再进行一次反转。但是我还不太了解 m 语法，当我尝试执行此操作时，我得到的只是一个错误Text.Reverse

我的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

当我们进入重命名列步骤时的示例数据：

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

期望结果：

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

背景

我有一个 Power Query，它将同一目录中具有相同表名的多个 Excel 工作簿合并到单个表中，然后我将使用该表作为数据透视表的源数据。或者至少我这样做了...

问题

问题是，某些文件的命名表进行了修订，并添加了一个新列。基本上，这是一个不断变化的混合数据文件。我需要将该列添加到缺少它的文件中。

我尝试过

我花了大量时间用谷歌搜索，终于找到了一个我能理解的 YouTube 视频。它基本上说添加一个IF语句来检查列是否存在。如果存在，则返回原始表。否则添加该列。

我的 M 代码目前

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"

这是我尝试添加 if 语句的地方，就像他们在视频中做的那样

但是当我选择该步骤时，它显示此错误：

问题

我该如何修复我的IF语句以便它添加一个名为“LOS”的列（如果尚不存在）。

更新

我应用的步骤列表如下，绿色步骤是我添加IF语句的地方

我正在尝试做什么

我正在尝试确定命名表中包含日期的第一列。

我尝试过

由于表标题行中的所有数据都存储为字符串，因此我认为我必须首先将日期数字作为字符串剥离，然后将其转换为数字，然后检查在拉出的字符串中没有数字的情况下是否存在错误。

我查找表格列的代码如下，并在 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

我将检查的每个部分都添加到了监视窗口。HeaderCell.Value 返回“Project No”。Left 返回“Proj”。CLng 显示为 。IsError 显示为 。

我的示例标题行如下所示

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

使用示例标头，我想返回值 6

问题

查找第一个日期列的正确方法是什么？目前我已对其进行了硬编码。

我正在尝试延迟绑定打开 Outlook 消息以报告问题或电子表格建议的过程。我正在尝试预先填写收件人：和抄送：收件人、主题行以及邮件正文的前几行。我不想发送邮件，因为我需要用户用他们的想法填充邮件正文。

我找到了一些最初使用早期绑定设置的代码。然后我继续对其进行修改，希望能够满足我的需求。然后我对其进行了修改，试图使其与后期绑定配合使用。

当我逐步执行代码时，我Run-time error '13': Type mismatch在第二行创建对象时得到了一个，我假设它实际上是在尝试创建消息。我尝试将 olMailItem 更改为 0。这导致 activeX 错误： Run-time error '429': ActiveX component can't create object。

我已经Option Explicit設定了。

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

我之前研究过的一些问题如下：

这是我学会用数值替换 olTO 和 olCC 的地方。我找不到 olMailItem 列出的数值。

这个看起来更oMail.CC = "email address here"简单

这是我获取的初始启动代码

我发誓还有一个我再也找不到其链接了。

问题

我该如何修复我的错误S并消除错误？

这个问题的答案指出，为了避免错误，您需要添加对的引用MS Scripting Run-time。

我已经使用此方法在我的代码中删除了一个文件，但是因为我知道它将在多台不包含该引用的计算机上使用，所以我认为我有两个选择：

1. 使用不同的方法删除现有文件。
2. 找出在打开文件时加载参考文件的方法。

该子按钮与工作表上的 ActiveX 按钮绑定

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

问题

使用New FileSytemObject