A
auujxa2 via AccessMonster.com
I'm trying to format a workbook from an output query, but for some reason it
doesn't fully complete the first time. But anytime after, it runs fine. It
exports fine, but it stops after the insert row line. (again, it runs fine
after the first time)
Here is the code:
DoCmd.OutputTo acOutputQuery, "WedgeTbl_Crosstab", acFormatXLS, strFilePath &
strFileName, True
'Set the objects to format
Set objXLApp = GetObject(strFilePath & "\" & strFileName)
'Set objXLBook = Workbooks.Add
Set objXLSheet1 = objXLApp.Worksheets("WedgeTbl_Crosstab")
'Hide columns
'objXLSheet1.Range("D").EntireColumn.Hidden = True
'Find/Replace
objXLSheet1.Range("E3:IV500").Cells.Replace What:="1", Replacement:
="X"
objXLSheet1.Range("E3:IV500").Cells.Replace What:="0", Replacement:=""
objXLSheet1.Range("C3:C500").Cells.Replace What:="", Replacement:
="999"
objXLSheet1.Range("A3:IV500").Sort _
Key1:=objXLSheet1.Range("C3")
objXLSheet1.Range("C3:C500").Cells.Replace What:="999", Replacement:
=""
'objXLSheet1.Range("D2:IV2").NumberFormat = "000"
objXLSheet1.Range("D:IV").Sort _
Key1:=objXLSheet1.Range("D2"), Order1:=xlDescending, Orientation:
=xlLeftToRight
objXLSheet1.Range("A2:IV2").Font.Bold = True
objXLSheet1.Range("B:IV").Cells.HorizontalAlignment = 3
objXLSheet1.Range("E:IV").Select
objXLSheet1.Columns("A").NumberFormat = "##0"
objXLSheet1.Range("E1").Select
objXLSheet1.Cells(1, 1).EntireRow.Insert
objXLSheet1.Range("E1").Select
With objXLSheet1
For Each cell In Range("E1:IV1")
cell.Value = "=LEFT(E2,7)"
Next
Range("E1").Select
Selection.AutoFill Destination:=Range("E1:IV1"), Type:=xlFillDefault
End With
'Set the cursor back on the first cell
objXLSheet1.Range("A1").Select
objXLSheet1.Range("A1").Value = "Department: " & Me.cboDepartments
objXLSheet1.Range("A1").Font.Bold = True
objXLSheet1.Range("A2").Value = "Store # VLookup Column"
objXLSheet1.Range("D1").Value = "Vendor # HLookup Row"
objXLSheet1.Range("A:IV").Columns.AutoFit
'Clean-Up
Set objXLSheet1 = Nothing
Set objXLBook = Nothing
Set objXLApp = Nothing
doesn't fully complete the first time. But anytime after, it runs fine. It
exports fine, but it stops after the insert row line. (again, it runs fine
after the first time)
Here is the code:
DoCmd.OutputTo acOutputQuery, "WedgeTbl_Crosstab", acFormatXLS, strFilePath &
strFileName, True
'Set the objects to format
Set objXLApp = GetObject(strFilePath & "\" & strFileName)
'Set objXLBook = Workbooks.Add
Set objXLSheet1 = objXLApp.Worksheets("WedgeTbl_Crosstab")
'Hide columns
'objXLSheet1.Range("D").EntireColumn.Hidden = True
'Find/Replace
objXLSheet1.Range("E3:IV500").Cells.Replace What:="1", Replacement:
="X"
objXLSheet1.Range("E3:IV500").Cells.Replace What:="0", Replacement:=""
objXLSheet1.Range("C3:C500").Cells.Replace What:="", Replacement:
="999"
objXLSheet1.Range("A3:IV500").Sort _
Key1:=objXLSheet1.Range("C3")
objXLSheet1.Range("C3:C500").Cells.Replace What:="999", Replacement:
=""
'objXLSheet1.Range("D2:IV2").NumberFormat = "000"
objXLSheet1.Range("D:IV").Sort _
Key1:=objXLSheet1.Range("D2"), Order1:=xlDescending, Orientation:
=xlLeftToRight
objXLSheet1.Range("A2:IV2").Font.Bold = True
objXLSheet1.Range("B:IV").Cells.HorizontalAlignment = 3
objXLSheet1.Range("E:IV").Select
objXLSheet1.Columns("A").NumberFormat = "##0"
objXLSheet1.Range("E1").Select
objXLSheet1.Cells(1, 1).EntireRow.Insert
objXLSheet1.Range("E1").Select
With objXLSheet1
For Each cell In Range("E1:IV1")
cell.Value = "=LEFT(E2,7)"
Next
Range("E1").Select
Selection.AutoFill Destination:=Range("E1:IV1"), Type:=xlFillDefault
End With
'Set the cursor back on the first cell
objXLSheet1.Range("A1").Select
objXLSheet1.Range("A1").Value = "Department: " & Me.cboDepartments
objXLSheet1.Range("A1").Font.Bold = True
objXLSheet1.Range("A2").Value = "Store # VLookup Column"
objXLSheet1.Range("D1").Value = "Vendor # HLookup Row"
objXLSheet1.Range("A:IV").Columns.AutoFit
'Clean-Up
Set objXLSheet1 = Nothing
Set objXLBook = Nothing
Set objXLApp = Nothing