Sorting in Excel from Access, Multiple Tab

[DCPan]

Hi,

From some strange reason, my code for dynamically formatting multiple tabs
work until I have to "sort" the worksheet.

If I strip out the code for sorting, everything works fine....

HELP!

Public Sub Download_SKU()

'Last Updated On 02/06/2008 by DCPan

'Declare Variables
Dim rst_SKU_No As New ADODB.Recordset

'Declare variables to format the download
Dim objXLApp As Object
Dim objXLBook As Object
Dim objXLSheet01 As Object
Dim strWorkSht As String

'Use the local connection
Call Local_Connect

'Open the recordset with data from the server table specified
rst_SKU_Npen "SELECT DISTINCT tbl_All_Entries.SKU FROM
tbl_All_Entries", _
objLocalDB, adOpenKeyset

'If the recordset is empty
If rst_SKU_No.BOF And rst_SKU_No.EOF Then

Else
'Scroll to the first record
rst_SKU_No.MoveFirst

Do Until rst_SKU_No.EOF = True

'Insert the line items
DoCmd.RunSQL "SELECT tbl_All_Entries.Entry_Type, " & _
"tbl_All_Entries.RA_No, " & _
"tbl_All_Entries.Claim_No, " & _
"tbl_All_Entries.SKU, " & _
"tbl_All_Entries.Prod_Desc, " & _
"tbl_All_Entries.Qty, " & _
"tbl_All_Entries.Unit_Price, " & _
"tbl_All_Entries.Total " & _
"INTO " & rst_SKU_No!SKU & " " & _
"FROM tbl_All_Entries " & _
"WHERE (((tbl_All_Entries.SKU) = '" & rst_SKU_No!SKU & "'))"
& _
"ORDER BY tbl_All_Entries.Unit_Price"

'Insert the line totals
DoCmd.RunSQL "INSERT INTO " & rst_SKU_No!SKU & " " & _
"( Entry_Type, SKU, Prod_Desc, Qty, Unit_Price, Total )"
& _
"SELECT tbl_All_Entries.Entry_Type, " & _
"tbl_All_Entries.SKU, " & _
"tbl_All_Entries.Prod_Desc, " & _
"Sum(tbl_All_Entries.Qty) AS SumOfQty, " & _
"tbl_All_Entries.Unit_Price, " & _
"Sum(tbl_All_Entries.Total) AS SumOfTotal " & _
"FROM tbl_All_Entries " & _
"GROUP BY tbl_All_Entries.Entry_Type, " & _
"tbl_All_Entries.SKU, " & _
"tbl_All_Entries.Prod_Desc, " & _
"tbl_All_Entries.Unit_Price " & _
"HAVING (((tbl_All_Entries.SKU)='" & rst_SKU_No!SKU & "'))"

'Download tabs
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
rst_SKU_No!SKU, strSaveFile, True

'Format the tabs

strWorkSht = rst_SKU_No!SKU

'Set the objects to format
Set objXLApp = CreateObject("Excel.Application")
Set objXLBook = objXLApp.Workbooks.Open(strSaveFile)
Set objXLSheet01 = objXLBook.Worksheets(strWorkSht)

'1 = black
'2 = white
'3 = red
'5 = blue
'10 = green
'13 = purple

'Format the headers
objXLSheet01.Range("A1:H1").Font.Bold = True
objXLSheet01.Range("A1:H1").HorizontalAlignment = xlCenter
'AutoFit the columns
objXLSheet01.Range("A:H").Columns.AutoFit
'Activate Sheet
objXLSheet01.Activate
'Sort columns
objXLSheet01.Columns("A:H").Select
Selection.Sort Key1:=Range("G2"), Order1:=xlAscending,
Key2:=Range("A2") _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1,
MatchCase:= _
False, Orientation:=xlTopToBottom,
DataOption1:=xlSortNormal, DataOption2 _
:=xlSortNormal
'Freeze Panes
objXLSheet01.Range("2:2").Select
objXLApp.ActiveWindow.FreezePanes = True
'Set the cursor back on the first cell
objXLSheet01.Range("A1:A1").Select

'Clean-Up
objXLBook.Save
objXLBook.Close
objXLApp.Quit
Set objXLSheet01 = Nothing
Set objXLBook = Nothing
Set objXLApp = Nothing

'Drop the temporary table
DoCmd.RunSQL "Drop Table " & rst_SKU_No!SKU

'Move to the next record
rst_SKU_No.MoveNext

Loop

End If

'Close Adodb Recordset
rst_SKU_No.Close
Set rst_SKU_No = Nothing

'Close Adodb Connection
objLocalDB.Close
Set objLocalDB = Nothing

Exit_Code:
Exit Sub

End Sub

 

[DCPan]

The weird part is, the sort works for the 1st tab...then the loop reaches the
2nd tab and it fails.

Debugger says something wrong with

Selection.Sort Key1:=Range("G2"), Order1:=xlAscending, Key2:=Range("A2") _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal,
DataOption2 _
:=xlSortNormal

But if something is wrong with the sort, then why did it work for the first
tab?

The error is error 6, overflow.

 

[Dirk Goldgar]

The weird part is, the sort works for the 1st tab...then the loop reaches
the
2nd tab and it fails.

Debugger says something wrong with

Selection.Sort Key1:=Range("G2"), Order1:=xlAscending, Key2:=Range("A2") _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal,
DataOption2 _
:=xlSortNormal

But if something is wrong with the sort, then why did it work for the
first
tab?

The error is error 6, overflow.

I'm not sure, but one thing I notice is that Selection is not qualified in
any way. I would expect it to be qualified with objXLApp --
"objXLApp.Selection.Sort ..." I don't know if that's relevant or not, but
I've had unexpected results automating Excel when I've forgotten to qualify
the method or property references.

 

[DCPan]

Hi Dirk,

I tried inserting either objXLApp or objXLSheet01 in front, and both crashed
the application the first time around.

The selection is qualified in the previous line with

'Activate Sheet
objXLSheet01.Activate
'Sort columns
objXLSheet01.Columns("A:H").Select

I guess I'll just dynamically create another table and order the data during
the insert....

Thanks though!

 

[Dirk Goldgar]

Hi Dirk,

I tried inserting either objXLApp or objXLSheet01 in front, and both
crashed
the application the first time around.

That certainly shouldn't happen, since Selection is a property of the Excel
application object.

I notice you're using late binding for your Excel objects. Do you still
have a reference set to the Excel object library? If not, how are the xl
constants (e.g., xlAscending, xlGuess, xlTopToBottom, xlSortNormal) being
defined?

 

[DCPan]

Sorry, I'm not familiar with late binding though I've read it a couple of
times in this forum.

I usually record a macro in Excel then copy the VBA over to Access. I guess
that's why it doesn't always work right.

Regarding your question, I have the reference set to the Microsoft Excel
11.0 Object Library.