none
Opening Excel files in Access using VBA RRS feed

  • Question

  • 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

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

    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

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

    End Sub

    Tuesday, June 5, 2018 5:36 PM

All replies

  • 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")

        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

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


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Tuesday, June 5, 2018 6:49 PM
  • Hi Kalvatron,

    Do you have any issue with this thread?

    If not, I would suggest you mark the helpful reply as answer which is the way to close a thread here.

    If you do, please feel free to let us know.

    Best Regards,

    Tao Zhou


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, June 7, 2018 7:10 AM