Hi all
I am trying to run code that formats an excel spreadsheet after it is created in Access.
My code is:
When the first file in the loop is created, the formatting works perfectly. When it loops to the second file, the file is created, it opens but then I get an error message
"Run Time Error 1004 Method 'Range' of Object_'Global' failed.
This occurs at Range("A1").Select
Would aprreciate some assistance with this.
Thanks
I am trying to run code that formats an excel spreadsheet after it is created in Access.
My code is:
Code:
Private Sub cmdExportMissingDataFiles_Click()
On Error GoTo ErrorHandler
Dim rs As Recordset: Set rs = CurrentDb.OpenRecordset("SELECT DISTINCT CountryCode, Country FROM qryMissingData")
Dim CreationMoment As String: CreationMoment = " Created on " & Format(Date, "ddmmyy") & " at " & Format(Time, "hhmmss")
Dim strSql As String
Dim AndWhere As String
Do While Not rs.EOF
' this is the right alias
AndWhere = "tblConsolRawData.CountryCode = " & rs!CountryCode
' access the sql string in the querydef and replace the semicolon with the where clause
strSql = Replace(CurrentDb.QueryDefs("QryMissingData").Sql, ";", " AND " & AndWhere)
CurrentDb.CreateQueryDef "CountryFile", strSql
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "CountryFile", "C:\KPMG\Missing data Files\" & "Missing Data For Country Code " & rs!CountryCode, True
DoCmd.DeleteObject acQuery, "CountryFile"
Dim xlApp As Object 'Excel.Application
Dim xlWB As Object 'Excel.Workbook
Dim xlSh As Object 'Excel.Worksheet
Set xlApp = CreateObject("Excel.Application") 'New Excel.Application
Set xlWB = xlApp.Workbooks.Open("C:\KPMG\Missing data Files\" & "Missing Data For Country Code " & rs!CountryCode)
Set xlSh = xlWB.Sheets(1)
xlApp.Visible = True
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=LEN(TRIM(A1))=0"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorLight2
.TintAndShade = 0.599963377788629
End With
Selection.FormatConditions(1).StopIfTrue = False
xlWB.Close True
xlApp.Quit
Set xlApp = Nothing
rs.MoveNext
Loop
ErrorHandler:
If Err.Number <> 0 Then
MsgBox Err.Number & vbCr & Err.Description
' make sure CountryFile is deleted anyway
DoCmd.DeleteObject acQuery, "CountryFile"
End If
Call CountFiles
rs.Close
End Sub
When the first file in the loop is created, the formatting works perfectly. When it loops to the second file, the file is created, it opens but then I get an error message
"Run Time Error 1004 Method 'Range' of Object_'Global' failed.
This occurs at Range("A1").Select
Would aprreciate some assistance with this.
Thanks