Trying to Run Excel from Access to Format Exported Table, Get Object Required Error

Question

I am trying to automate exporting a table from Access into an Excel report. From my research on Stack Overflow, it is recommended to have a VBA code for the export, and then another code for formatting the Excel sheet. I would greatly prefer to run the formatting of the Excel sheet from Access, so I just push a button and the thing is exported. I absolutely hate running this report because everyone wants it "right now" when I have other pressing things to do. It's for the Company President's office, so a peon like me just has to hop to.

I found this suggested code here: https://stackoverflow.com/a/14401307/6850333, but I'm getting an error of "Object variable or With block variable not set"

Sub FormatACTrade(strFilePath As String) 'I'm running this from another _
sub that exports the tables to excel, I know functions are needed for _
an output but this is a private variable that I'm using within a modle_
so should be ok??

On Error GoTo ErrorHandler

Dim objFile As Object

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = False
objExcel.DisplayAlerts = False
Set objFso = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFso.GetFolder(strFilePath)

If objFso.GetExtensionName(objFile.Path) = "xlsx" Then
     Set objWorkbook = objExcel.Workbooks.Open(objFile.Path)
     For Each sh In objWorkbook.Worksheets
If sh.UsedRange.Address <> "$A$1" Or sh.Range("A1") <> "" Then
        With sh
        [Do stuff]
        End With
    End If
    Next
    objWorkbook.Close True
End If
End Sub

Any suggestions are appreciated. I'm new to coding, and I have to admit the documentation on vba objects, scripting, and applications are difficult to comprehend. I understand I am not properly naming an object, but I don't really understand what the code is even doing at this line Set objWorkbook = objExcel.Workbooks.Open(objFile.Path).

If that's the line it flagged, then it missed. You never set `objFile` to anything. — Comintern, Feb 23 '17 at 20:48
It's not flagged in the compiler, but that's the line where I'm over my head. You're right though, objFile is NOT defined. I just added a line `Dim objFile As Object` but now the error is "Object variable or With block variable not set", I'm not sure what I should put for objFile. The file name? — K. Long, Feb 23 '17 at 20:59
Depends on what you're intending to do. I suspect both instances of `objFile.Path` should be `strFilePath`. — Comintern, Feb 23 '17 at 21:02
Does the `.Path` extension do anything? The original code does have `strFilePath` and `objFile.Path` seperately. The code is supposed to iterate over files within a folder but I really don't need that.There is just one Excel sheet in the folder and I know what it's named. How can I call the "xlsx" file path directly? — K. Long, Feb 23 '17 at 21:12
What do you mean by "call the "xlsx" file path directly"? You might want to edit your question to include a description of what you're trying to do with this code that you found. What is being passed for `strFilePath`? What is the `Sub` supposed to do with that? — Comintern, Feb 23 '17 at 21:17
The previous `Sub` is working and saves the Excel file into the folder designated by `strFilePath`. I want to format this Excel file. I also edited to add the place I got the code from http://stackoverflow.com/a/14401307/6850333. — K. Long, Feb 23 '17 at 21:26

score 1 · Accepted Answer · answered Feb 24 '17 at 01:55

Consider removing any need for objFSO. Previous code needed this object to iterate through files. Since you know the Excel file path, simply pass it in the Workbooks.Open() method:

Sub FormatACTrade(strFilePath As String)    
    Dim objExcel As Object, objWorkbook As Object, sh As Object

    Set objExcel = CreateObject("Excel.Application")
    objExcel.Visible = False
    objExcel.DisplayAlerts = False

    ' OPEN WORKBOOK (IN BACKGROUND)
    Set objWorkbook = objExcel.Workbooks.Open(strFilePath)

    For Each sh In objWorkbook.Worksheets
       If sh.UsedRange.Address <> "$A$1" Or sh.Range("A1") <> "" Then
          With sh
            [Do stuff]
          End With
       End If
    Next sh

    ' CLOSE AND SAVE WORKBOOK
    objWorkbook.Close True
    ' QUIT APP
    objExcel.Quit

    ' FREE RESOURCES
    Set objWorkbook = Nothing
    Set objExcel = Nothing
End Sub

This is working. Thank you. Is there a source for best practices for running another Microsoft Office program in another? I don't know how I would have realized to ` ' FREE RESOURCES ` or quit the application. — K. Long, Feb 27 '17 at 21:58
Great. Please accept (tick mark to side) to confirm resolution. I don't know if there is any single doc for best practices. But I learned these items through various working examples, SO posts included. You learn by doing! — Parfait, Feb 28 '17 at 00:02

Trying to Run Excel from Access to Format Exported Table, Get Object Required Error

1 Answers1