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_No.Open "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.

 

[Dirk Goldgar]

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

You are using late binding. You have:

Dim objXLApp As Object
Dim objXLBook As Object
Dim objXLSheet01 As Object


Set objXLApp = CreateObject("Excel.Application")

.... where with early binding you would have:

Dim objXLApp As Excel.Application
Dim objXLBook As Excel.Workbook
Dim objXLSheet01 As Excel.Worksheet

Set objXLApp = New Excel.Application

With late binding (declaring objects just as Object), you're telling Access,
"Wait until run-time to resolve my references to this object's properties
and methods. Until then, trust me when I refer to them in my code."

Late binding is less efficient than early binding, performance-wise, but
also less vulnerable to library differences between computers. If you
design the application on a PC with one version of the object library, and
then run it on a PC with a different version of the library, you can trust
that it will still work, so long as all the properties and methods you call
upon are available in that version.

With late binding, you don't normally even have a reference set to the
object library. Since there's no reference to the library, there's no
reference to be broken, and thus you are protected from errors due to broken
references.

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.

That can be so. In the Excel environment, any otherwise unqualified method
or property references are naturally assumed to apply to the Excel
application. When I'm doing Excel automation in Access, my experience has
been that unqualified references to Excel methods often lead to problems
closing either the Access application or the Excel application -- they keep
running in the background due to a dangling object pointer that is created
behind tghe scenes.

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

As I mentioned above, normally I would not have this reference if using late
binding. Instead, I would declare the various XL constants myself, so that
I can use named constants in my code.

What I usually do when coding automation is use early binding originally
(with the library reference set, and declaring objects as their proper
types). Then later, when the code is all working and tested, I change the
code to late binding (declaring objects as Object, and adding/uncommenting
declarations of the named constants), and drop the library reference.

None of this discussion really helps you with your current problem, I'm
afraid. You might try using early binding first and seeing if you can
resolve the problem with all the intellisense that would be at your
disposal. Then change back to late binding when you get it working, and
remove the library reference.

I'd also suggest that maybe the people in the Excel newsgroups can see
something in your Excel automation code that I don't see.