下标超出范围错误9 vba

Question

当另一个用户运行我的插件时，我得到了一个下标超出范围的错误，但当我自己运行相同的代码时却没有问题。在设置工作簿的值时会发生这种情况。文件名是通过获取当前日期生成的，并存储为gendate。在此基础上，将根据用户创建的文件路径创建和保存文件名。在本例中，gv.Range("b2").text的值是C:\Users\username\Desktop\ReportGeneration.因此，fp为C:\Users\dmulhausen\Desktop\ReportGeneration\TSReports9_6_201615h5m32s.xlsx这不会为我生成错误，但会为脚本的另一个用户生成错误。

Dim ai As Workbook  'add in data ---Initialized in Report Setup
Dim dwb As Workbook 'destination workbook ---Initialized in Report Setup
Dim ss As Worksheet 'source sheet
Dim ds As Worksheet 'destination sheet or writing sheet
Dim rv As Worksheet 'reporting variables sheet ---Initialized in Report Setup
Dim pv As Worksheet 'ts variables sheet ---Initialized in Report Setup
Dim gv As Worksheet 'global ai variables ---Initialized in Report Setup
Dim tempstr As String
Dim fp As String 'file path ---Initialized in Report Setup
Dim gendate As Date
Dim reportscreated As Integer
Dim initialized As Boolean
Dim sheetnames(1 To 12) As String

Sub reportsetup()
    Set ai = Workbooks("TSReports add in.xlam")
    Set rv = ai.Worksheets("ReportVars")
    Set pv = ai.Worksheets("TS1_2Vars")
    Set gv = ai.Worksheets("globalVars")
    If (IsEmpty(gv.Range("b2").Value)) Then
        MsgBox ("Please select a designated folder for reports")
        With Application.FileDialog(msoFileDialogFolderPicker)
            .AllowMultiSelect = False
            .Show
            If .SelectedItems.Count > 0 Then
                gv.Range("b2").Value = .SelectedItems(1)
            End If
            ai.Save
        End With

    End If
    initialized = True
    gendate = Now()
    tempstr = "TSReports" & Month(gendate) & "_" & Day(gendate) & "_" & Year(gendate) & Hour(gendate) & "h" & Minute(gendate) & "m" & Second(gendate) & "s"
    fp = gv.Range("b2").Text & "\" & tempstr & ".xlsx"
    Workbooks.Add
    Application.DisplayAlerts = False
    Application.AlertBeforeOverwriting = False
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs Filename:=fp
    Set dwb = Workbooks(tempstr) '*******Error occurs here*******

Answer 1

请参阅：Windows().Activate works on every computer except one

这应该可以解决这个问题。

tempstr = "TSReports" & Month(gendate) & "_" & Day(gendate) & "_" & _
            Year(gendate) & Hour(gendate) & "h" & Minute(gendate) & "m" & _
            Second(gendate) & "s" & ".xlsx"

fp = gv.Range("b2").Text & "\" & tempstr 

Workbooks.Add
Application.DisplayAlerts = False
Application.AlertBeforeOverwriting = False
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=fp
Set dwb = Workbooks(tempstr) 

然而，这将更加健壮：

Set dwb = Workbooks.Add
Application.DisplayAlerts = False
Application.AlertBeforeOverwriting = False
Application.DisplayAlerts = False
dwb.SaveAs Filename:=fp