使用VBA在Access中打开Excel文件

问题描述：

嗨大家好，每次我运行宏时，它都会在Access中冻结。 Excel文件名拼写正确，文件保存在正确的目录中。最后，我需要打开以下excel文件进行修改。非常感谢

Hi Guys, every time I run the macro, it freezes in Access. The Excel file name is spelled correctly and the file is saved in the proper directory. In the end, I need the following excel files to be open for modification. Many thanks

答

您必须创建一个Excel.Application对象变量并进行设置：

You must create an Excel.Application object variable and set it:

Private Sub FixForeignDebtSwap_Click （）

    Dim xlApp As Excel.Application

    Dim Y As String，M As String，FileLoc As String

    Dim MyArray1 As Variant，i As Long，x As Excel.Workbook

    MyArray1 = Array（" xDebts.xlsx"，" xDebtsCFs.xlsx"，" xSwaps.xlsx"，_

       " xSwapFixedLeg.xlsx"，" xSwapFloatingLeg.xlsx"）

Private Sub FixForeignDebtSwap_Click()
    Dim xlApp As Excel.Application
    Dim Y As String, M As String, FileLoc As String
    Dim MyArray1 As Variant, i As Long, x As Excel.Workbook
    MyArray1 = Array("xDebts.xlsx", "xDebtsCFs.xlsx", "xSwaps.xlsx", _
        "xSwapFixedLeg.xlsx", "xSwapFloatingLeg.xlsx")

    On Error Resume Next

   设置xlApp = GetObject（Class：=" Excel.Application"）

    On Error GoTo 0

   如果xlApp什么都没有那么就是
       设置xlApp = CreateObject（" Excel.Application"）

   结束如果是
    xlApp.Visible = True

   如果是IsNull（ReportDate）则为
        FileDir = Null

   否则

        Y =格式（ReportDate，"yyyy"）

        M =格式（ReportDate，" mmm"）

        MainDir =" M：\ \\ tTRM\CM \ CorpocinFinance \ACFA Files \ALM \" &安培; Y& &QUOT; \&QUOT; &安培; M& &QUOT; &QUOT; &安培; Y& " \"

        FileLoc =" M：\ TRM \ CM \ CorporateAinance \ACFA Files \ALM \" &安培; Y& &QUOT; \&QUOT; &安培; M& &QUOT; &QUOT; &安培; Y& " \ data\"

   结束如果

    On Error Resume Next
    Set xlApp = GetObject(Class:="Excel.Application")
    On Error GoTo 0
    If xlApp Is Nothing Then
        Set xlApp = CreateObject("Excel.Application")
    End If
    xlApp.Visible = True
    If IsNull(ReportDate) Then
        FileDir = Null
    Else
        Y = Format(ReportDate, "yyyy")
        M = Format(ReportDate, "mmm")
        MainDir = "M:\TRM\CM\CorporateFinance\ACFA Files\ALM\" & Y & "\" & M & " " & Y & "\"
        FileLoc = "M:\TRM\CM\CorporateFinance\ACFA Files\ALM\" & Y & "\" & M & " " & Y & "\Data\"
    End If

   对于i = LBound（MyArray1）到UBound（MyArray1）

       设置x = xlApp.Workbooks.Open（FileLoc& MyArray1（i））

   下一个

结束子

    For i = LBound(MyArray1) To UBound(MyArray1)
        Set x = xlApp.Workbooks.Open(FileLoc & MyArray1(i))
    Next
End Sub

使用VBA在Access中打开Excel文件

相关推荐