If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Output Query
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").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.HorizontalAlignmen t = 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 -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/200810/1 |
Thread Tools | |
Display Modes | |
|
|