Output Query

  • Thread starter auujxa2 via AccessMonster.com
  • Start date
A

auujxa2 via AccessMonster.com

I have this code in one of my forms, which outputs a crosstab query, and
formats it in Excel. The issue I'm running into, is that It doesn't complete
the first I run it. But if I close excel, and run again, it runs fine
everytime after that. It seems to not complete once it gets to the insert
line section. (again, this only happens the first time i run it)

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: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"

'Set the cursor back on the first cell
objXLSheet1.Range("A1").Select

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

'DoCmd.Maximize
DoCmd.SetWarnings True
 
Top