MS Access VBA coding Excel 1

[Knicks]

I have code that I've created that is super close to working. It essentially loops thru a record set creating query defs and then transfers to Excel and creates a file. Each loop creates the query def, deletes def, and outputs an Excel file. After Excel file is created I open the Excel file and set auto width and background color for header. I then loop through the file to highlight any "Missing" or "Mismapped" data. That works fine. Its the last loop where I am attempting to test each column (minus the header) for any data, if no data I want it to hide the column. Here the problem occurs on the 2nd file, the first file it works fine on, so I know I'm really close. Any help would be appreciated, I think it is a variable that may need to be reset or placed in a different section of code...

Error Message: Run Time Error '1004', Method Range of Object Failed
'lRealLastRow = objExcel_App2.Cells.Find("*", Range("A1"), xlValues, , xlByRows, xlPrevious).Row ---- This line fails below, near the bottom of code

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strTemp As String
Dim STRsql As String
Dim QueryName As String
Set db = CurrentDb
Set rs = db.OpenRecordset("qrySP-CW-Caseworker")
Dim txtMonth As String
Dim FileName As String
txtMonth = DLookup("[maxMonth]", "qryMaxMonth")

Do Until rs.EOF
strTemp = rs![case worker]
Debug.Print strTemp


Dim qdf As DAO.QueryDef
QueryName = strTemp
STRsql = "SELECT * FROM [tblSP-CW_Main] WHERE (((case worker)='" & (strTemp) & "'));"
Debug.Print STRsql
Debug.Print QueryName

Set qdf = CurrentDb.CreateQueryDef(QueryName, "SELECT * FROM [tblSP-CW_Main] WHERE (([cap worker])='" & (strTemp) & "');")
FileName = "O:\Programs\Support Services\Reports\Data Quality\" & txtMonth & "\" & txtMonth & "_" & QueryName & ".xlsx"
Debug.Print FileName
'DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, QueryName, FileName, True
DoCmd.TransferSpreadsheet acExport, , QueryName, FileName, True
CurrentDb.QueryDefs.Delete QueryName
Set qdf = Nothing
Dim objExcel_App2 As Excel.Application ' <--
Dim xlsExcel_Sheet2 As Excel.Worksheet
Dim xlrMyRange2 As Excel.Range
Dim Cloop As Excel.Range
Dim Ecolumn As Excel.Range
Set objExcel_App2 = New Excel.Application ' <--
objExcel_App2.Visible = False
objExcel_App2.Workbooks.Open FileName:="O:\Programs\Support Services\Reports\Data Quality\" & txtMonth & "\" & txtMonth & "_" & QueryName & ".xlsx"
If Val(objExcel_App2.Application.Version) >= 8 Then
Set xlsExcel_Sheet2 = objExcel_App2.Worksheets(1) ' <--
Else
End If
Set xlrMyRange2 = xlsExcel_Sheet2.Range("A1:W1")
xlrMyRange2.Font.Bold = True
xlrMyRange2.Interior.Color = RGB(204, 229, 255)
objExcel_App2.Cells.Columns.AutoFit

Set Cloop = xlsExcel_Sheet2.Range("A2:W30")
With Cloop
For Each Cell In Cloop
If Cell.Value = "Missing" Or Cell.Value = "Mismapped" Then
Cell.Interior.Color = RGB(255, 255, 0)
Cell.Font.Bold = True
End If

Next Cell

End With
Dim lRealLastRow, lRealLastCol As Long
Dim i As Long
i = 1 'also failed when not set, but the same on 2nd Excel, 1st Excel file it works
[highlight #FCE94F]lRealLastRow = objExcel_App2.Cells.Find("*", Range("A1"), xlValues, , xlByRows, xlPrevious).Row[/highlight]
lRealLastCol = objExcel_App2.Cells.Find("*", Range("A1"), xlValues, , xlByColumns, xlPrevious).Column
For i = 1 To lRealLastCol
' If lRealLastRow - WorksheetFunction.CountBlank(Intersect(Columns(i), ActiveSheet.UsedRange)) <= 1 Then
objExcel_App2.Cells.Columns(i).EntireColumn.Hidden = True
Else
End If
' Next i


objExcel_App2.Application.ActiveWorkbook.Save
objExcel_App2.Application.ActiveWorkbook.Close
objExcel_App2.Quit
Set objExcel_App2 = Nothing


rs.MoveNext
Loop
rs.Close
MsgBox ("done")

 

[Knicks]

I have also tried substituting this section of code out

Dim lRealLastRow, lRealLastCol As Long
Dim i As Long
i = 1 'also failed when not set, but the same on 2nd Excel, 1st Excel file it works
lRealLastRow = objExcel_App2.Cells.Find("*", Range("A1"), xlValues, , xlByRows, xlPrevious).Row
lRealLastCol = objExcel_App2.Cells.Find("*", Range("A1"), xlValues, , xlByColumns, xlPrevious).Column
For i = 1 To lRealLastCol
If lRealLastRow - WorksheetFunction.CountBlank(Intersect(Columns(i), ActiveSheet.UsedRange)) <= 1 Then
objExcel_App2.Cells.Columns(i).EntireColumn.Hidden = True
Else
End If
Next I


And using this section of code instead. Once again it works on the 1st Excel file and fails on the 2nd Excel file. The message shows as METHOD 'COLUMNS OF OBJECT'_GLOBAL FAILED (ERROR 1004)

Dim LC As Integer, j As Integer
LC = objExcel_App2.Cells(1, Columns.Count).End(xlToLeft).Column
For j = 2 To LC
Columns(j).Hidden = objExcel_App2.WorksheetFunction.CountA(Columns(j)) < 2
Next j

 

[MajP]

You should fully qualify your range method.
Something like
SomeWorksheetobject.Range("A1")

The problem when you do not fully qualify, it infers that it is the active worksheet. Should get out of the habit of using ActiveWorksheet of active whatever. When you are looping like this there is a good chance you have no idea what is active or if even something is active.

Also if using automation between applications, get in the habit of this.

 

[Knicks]

I have tried various ways to tell Excel where the programming is taking place. Part of my problem is I don't totally get Excel, the other is the deeper principles of VBA. This needs to be done from the Access program (I have Excel reference set) and I find much less support when the code starts in another program

I have tried doing something like this and I get the same error

Set Ecolumn = xlsExcel_Sheet2.Range("A1:W30")
LC = Ecolumn.Cells(1, Columns.Count).End(xlToLeft).Column
For j = 2 To LC
columns(j).Hidden = objExcel_App2.WorksheetFunction.CountA(Columns(j)) < 2
Next j

 

[MajP]

Not sure what error you get, but my guess you need to fully qualify here as well

the columns method is a method of a range or worksheet object.
So if working in excel you can infer this, but if automating I would always define that as

SomeWorkSheetOrRangeObject.columns.count instead of simply Columns.count

 

[Knicks]

Okay,
I was able to qualify another piece of code to perform the same task. I can see that I needed to reference the Excel object first and then also use the Active sheet. This works below

Dim r As Range
Dim nLastRow As Long
Dim nLastColumn As Integer
Dim i As Integer
Dim HideIt As Boolean
Dim j As Long

Set r = objExcel_App2.ActiveSheet.UsedRange
nLastRow = r.Rows.Count + r.Row - 1
nLastColumn = r.Columns.Count + r.Column - 1

For i = 1 To nLastColumn
HideIt = True
For j = 2 To nLastRow
If r.Cells(j, i).Value <> "" Then
HideIt = False
End If
Next
If HideIt = True Then
r.Columns(i).EntireColumn.Hidden = True
End If
Next

 

[MajP]

Again I would avoid active anything. Instead if you know the sheets name or index use that.
My guess it is worksheets(0) the first sheet. Again try to avoid selecting anything and using active anything.

Here is the discussion

Global Objects in Microsoft Office Applications

Microsoft Office is designed to use Global objects in much the same way that VB uses them for its native functions. This makes coding easier for macros written in Microsoft Visual Basic for Applications (VBA). VBA code makes wide use of unqualified property and method calls to keep the code simple and to keep the code understandable for beginners.

For example, the following Microsoft Word VBA code sets some text after the third paragraph in a document:
Sub AddSomeText()
ActiveDocument.Paragraphs(3).Range.Select
Selection.Collapse wdCollapseEnd
Selection.TypeText "Some new text."
End Sub


The code uses two objects, ActiveDocument and Selection. Both objects are native to Word VBA. To access these objects, the code makes unqualified method calls on two property procedures on the Word Global object. (The property procedures use the same name and return the respective object.) The process is equivalent to the following:

Public oGlobalWordApp As Word.Application
Sub AddSomeText()
If oGlobalWordApp Is Nothing Then
' Sets variable to the running instance of the Word
' Global object (which is nearly the same as Word.Application)...
Set oGlobalWordApp = Word.Global
End If
oGlobalWordApp.ActiveDocument.Paragraphs(3).Range.Select
oGlobalWordApp.Selection.Collapse wdCollapseEnd
oGlobalWordApp.Selection.TypeText "Some new text."
End Sub


Look at the two code samples. You can see why most developers prefer to use unqualified code. Unqualified code is easier to understand and is less trouble to write. Additionally, when unqualified code is used in the native container for which it is designed, it is also completely safe.
The Problems in Using Unqualified Code with Office

Problems may occur when you reference a Global object that does not live inside your process space. In this situation, many of the assumptions about global objects break down. These assumptions include things such as the following: that there is only one instance; that calls to the object are native to the host; or that calls are kept alive for the length of the host application. When these assumptions break down, buggy behavior may result.

Office applications use Global objects and live out-of-process. Therefore, VB code that uses early binding to automate these applications is especially vulnerable to problems involving unqualified method calls. Note that unqualified method calls only occur if you reference the type library for the Office application in the References dialog box. You typically do this when you use early binding. Code that uses late binding is always qualified. It does not experience these problems if you do not reference the type library.

In itself, unqualified code frequently runs just fine in VB, even when it references out-of-process objects. This makes the problems that you experience harder to deal with, because the code appears to work successfully some of the time. There is, however, one warning sign to look for, as follows:
If the code always appears to work the first time that it is run, and if the errors or the unexpected behaviors occur only during subsequent calls to the same code, an unqualified method call is the cause.
This problem occurs because the code creates a new instance of the Office application each time that it runs, but the unqualified code caused VB to set a hidden reference to the old instance. Therefore, subsequent calls on the hidden variable (for an unqualified method) are made to the wrong server.

Some of the errors or unexpected behaviors that you might experience if you make an unqualified call to an Office object are as follow:
Error 462 or Error -2147417848: The code uses an unqualified method on an Office instance that has been ended (for example, by calling the Quit method). If the code is structured to create a new Office application instance each time that it is run, it will typically close the instance at the end of the task. If this code is run a second time, the unqualified call is unsuccessful. VB calls the previous instance, but the remote server has unloaded.
Application Does Not Shut Down: Unqualified calls in VB set a hidden variable reference to the Office Global object. Therefore, an Office application may fail to shut down, even if you call the Quit method, because it still has outstanding references. This behavior typically occurs on the first instance that the code uses. However, complex programs may cause it to occur with multiple instances.
Error 91 or Object is Missing Errors: You may see errors or unexpected results relating to the application state. You may also see unexpected results relating to an empty object. These behaviors occur if the code creates a new Office instance each time that it is run and if it does not quit the previous instance properly. Or, these behaviors occur if the code creates a new Office instance each time that it is run and if the Office application does not shut down, as mentioned in the preceding issue. These errors occur if the document or other objects that you are trying to access are located in another instance of the application than the Global object that VB has referenced, and if the objects (such as ActiveDocument or ActiveChart) do not exist in the global instance.
Code Runs Without Error, But Document Is Incorrect: Unqualified calls can be invoked for an instance other than the one that you explicitly set up. Therefore, some tasks (especially those calls to Selection or Range) may be successfully performed, but these tasks are performed on the wrong document. Avoid the use of non-specific range or non-specific selection objects that can run on any document.
The exact error or behavior may differ depending on the Office application that is involved and depending on the context in which the problem occurs.
Qualifying the Code to Avoid Errors

The best guideline is to avoid using any Office object that you do not explicitly call from a parent object that you set in a specific variable. In other words, look for code that uses Office objects without qualifying which Office instance or document that it is supposed to refer to. For example, this code uses an unqualified call to display the count of open workbooks in Microsoft Excel:

Sub CreateThreeBooks()
Dim oXL As Excel.Application
Dim i As Long

' Create Excel instance (make it visible for test)...
Set oXL = New Excel.Application
oXL.Visible = True

' Open a few empty workbooks...
For i = 1 To 3
oXL.Workbooks.Add
Next i

' How many books did we open?
MsgBox "Number of workbooks: " & Workbooks.Count, vbMsgBoxSetForeground

' Shutdown Excel (or do we?)...
oXL.Quit
Set oXL = Nothing
' Check the Processes list. Excel.exe is still running!
End Sub

when you run the code, it appears to run correctly the first time. However, Excel continues to run, even though you called the Quit method. If you call the code again, the message box now incorrectly displays 0 for the workbook count. Your code may now fail where you would expect it to succeed.

To resolve both problems, you must fully qualify the Workbooks object that you reference for the count, as follows:

MsgBox "Number of workbooks: " & oXL.Workbooks.Count, vbMsgBoxSetForeground


Some Office objects and functions are typically unqualified in VBA code. They can cause problems if you use that same code in VB and if you do not add the appropriate qualifications. Here is a list of common objects and functions that are typically unqualified in VBA code.
/quote]

 

[MajP]

Sorry more readable here

Global Objects in Microsoft Office Applications

Microsoft Office is designed to use Global objects in much the same way that VB uses them for its native functions. This makes coding easier for macros written in Microsoft Visual Basic for Applications (VBA). VBA code makes wide use of unqualified property and method calls to keep the code simple and to keep the code understandable for beginners.

For example, the following Microsoft Word VBA code sets some text after the third paragraph in a document:
Sub AddSomeText()
ActiveDocument.Paragraphs(3).Range.Select
Selection.Collapse wdCollapseEnd
Selection.TypeText "Some new text."
End Sub


The code uses two objects, ActiveDocument and Selection. Both objects are native to Word VBA. To access these objects, the code makes unqualified method calls on two property procedures on the Word Global object. (The property procedures use the same name and return the respective object.) The process is equivalent to the following:

Public oGlobalWordApp As Word.Application
Sub AddSomeText()
If oGlobalWordApp Is Nothing Then
' Sets variable to the running instance of the Word
' Global object (which is nearly the same as Word.Application)...
Set oGlobalWordApp = Word.Global
End If
oGlobalWordApp.ActiveDocument.Paragraphs(3).Range.Select
oGlobalWordApp.Selection.Collapse wdCollapseEnd
oGlobalWordApp.Selection.TypeText "Some new text."
End Sub


Look at the two code samples. You can see why most developers prefer to use unqualified code. Unqualified code is easier to understand and is less trouble to write. Additionally, when unqualified code is used in the native container for which it is designed, it is also completely safe.
The Problems in Using Unqualified Code with Office

Problems may occur when you reference a Global object that does not live inside your process space. In this situation, many of the assumptions about global objects break down. These assumptions include things such as the following: that there is only one instance; that calls to the object are native to the host; or that calls are kept alive for the length of the host application. When these assumptions break down, buggy behavior may result.

Office applications use Global objects and live out-of-process. Therefore, VB code that uses early binding to automate these applications is especially vulnerable to problems involving unqualified method calls. Note that unqualified method calls only occur if you reference the type library for the Office application in the References dialog box. You typically do this when you use early binding. Code that uses late binding is always qualified. It does not experience these problems if you do not reference the type library.

In itself, unqualified code frequently runs just fine in VB, even when it references out-of-process objects. This makes the problems that you experience harder to deal with, because the code appears to work successfully some of the time. There is, however, one warning sign to look for, as follows:
If the code always appears to work the first time that it is run, and if the errors or the unexpected behaviors occur only during subsequent calls to the same code, an unqualified method call is the cause.
This problem occurs because the code creates a new instance of the Office application each time that it runs, but the unqualified code caused VB to set a hidden reference to the old instance. Therefore, subsequent calls on the hidden variable (for an unqualified method) are made to the wrong server.

Some of the errors or unexpected behaviors that you might experience if you make an unqualified call to an Office object are as follow:
Error 462 or Error -2147417848: The code uses an unqualified method on an Office instance that has been ended (for example, by calling the Quit method). If the code is structured to create a new Office application instance each time that it is run, it will typically close the instance at the end of the task. If this code is run a second time, the unqualified call is unsuccessful. VB calls the previous instance, but the remote server has unloaded.
Application Does Not Shut Down: Unqualified calls in VB set a hidden variable reference to the Office Global object. Therefore, an Office application may fail to shut down, even if you call the Quit method, because it still has outstanding references. This behavior typically occurs on the first instance that the code uses. However, complex programs may cause it to occur with multiple instances.
Error 91 or Object is Missing Errors: You may see errors or unexpected results relating to the application state. You may also see unexpected results relating to an empty object. These behaviors occur if the code creates a new Office instance each time that it is run and if it does not quit the previous instance properly. Or, these behaviors occur if the code creates a new Office instance each time that it is run and if the Office application does not shut down, as mentioned in the preceding issue. These errors occur if the document or other objects that you are trying to access are located in another instance of the application than the Global object that VB has referenced, and if the objects (such as ActiveDocument or ActiveChart) do not exist in the global instance.
Code Runs Without Error, But Document Is Incorrect: Unqualified calls can be invoked for an instance other than the one that you explicitly set up. Therefore, some tasks (especially those calls to Selection or Range) may be successfully performed, but these tasks are performed on the wrong document. Avoid the use of non-specific range or non-specific selection objects that can run on any document.
The exact error or behavior may differ depending on the Office application that is involved and depending on the context in which the problem occurs.
Qualifying the Code to Avoid Errors

The best guideline is to avoid using any Office object that you do not explicitly call from a parent object that you set in a specific variable. In other words, look for code that uses Office objects without qualifying which Office instance or document that it is supposed to refer to. For example, this code uses an unqualified call to display the count of open workbooks in Microsoft Excel:

Sub CreateThreeBooks()
Dim oXL As Excel.Application
Dim i As Long

' Create Excel instance (make it visible for test)...
Set oXL = New Excel.Application
oXL.Visible = True

' Open a few empty workbooks...
For i = 1 To 3
oXL.Workbooks.Add
Next i

' How many books did we open?
MsgBox "Number of workbooks: " & Workbooks.Count, vbMsgBoxSetForeground

' Shutdown Excel (or do we?)...
oXL.Quit
Set oXL = Nothing
' Check the Processes list. Excel.exe is still running!
End Sub

when you run the code, it appears to run correctly the first time. However, Excel continues to run, even though you called the Quit method. If you call the code again, the message box now incorrectly displays 0 for the workbook count. Your code may now fail where you would expect it to succeed.

To resolve both problems, you must fully qualify the Workbooks object that you reference for the count, as follows:

MsgBox "Number of workbooks: " & oXL.Workbooks.Count, vbMsgBoxSetForeground


Some Office objects and functions are typically unqualified in VBA code. They can cause problems if you use that same code in VB and if you do not add the appropriate qualifications. Here is a list of common objects and functions that are typically unqualified in VBA code.

 

[MajP]

And to emphasize

If the code always appears to work the first time that it is run, and if the errors or the unexpected behaviors occur only during subsequent calls to the same code, an unqualified method call is the cause.

This problem occurs because the code creates a new instance of the Office application each time that it runs, but the unqualified code caused VB to set a hidden reference to the old instance. Therefore, subsequent calls on the hidden variable (for an unqualified method) are made to the wrong server

Sound similar?

 

[Knicks]

That's some of the more interesting nuanced coding information I've read in a while. It is actually really important for the work I do as I tend to use MS Access as a portal into Excel, Word, and Outlook. Often the code you find for a process is only from it's native program and not outside from another program first, so its important to understand how to qualify code.

I think on the first bit of code that failed I was sort of sniffing around the problem, I got that the first run being successful meant something was still active and needed to be reset, but I think that bit of Excel code was a tad complex for me to get a handle on where to refer to the object and worksheet.

The final piece of code I used was simple enough to put in the qualifiers and thus worked when I edited the code. I am able to generate about 20 Excel files using query def and transfer spreadsheet then open each Excel file and highlight data and hide columns, all in about 15 seconds