Excel Code does not work in Access

[Imran J Khan]

Need help.
The following code creates a worksheet from Access, but will not work with a
line added to sort the data. The code is otherwise a well tried and tested.
I need to be able to sort the worksheet, and the Sort Column line below is
tested in Excel and work as desired there. In Access there is an Error 1004,
asking to use single quotes when using the equal sign. I don't know what to
do.
Imran

Public Sub createWksSort(strQuery As String, strWksName As String,
strWhere() As String, strHeader() As String, strFormat() As String,
strParms() As String, blnParms As Boolean)
Dim intI As Integer
Dim intColumns As Integer
Dim rsT As DAO.Recordset
Dim xlApp As Object
Dim xlBook As Object
Dim xlSheet As Object
Dim lngRecCount As Long
Dim blnAll As Boolean
Dim strSql As String
Dim strCell As String
Dim strPage As String
Dim qdf As QueryDef
Dim recArray As Variant
Dim fldCount As Integer
Dim iCol As Integer
Dim iRow As Integer

On Error GoTo fErr
intColumns = UBound(strHeader) - 1
Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Add ' Create a new workbook
xlApp.DisplayAlerts = False
xlApp.DisplayAlerts = True

' Capture reference to first worksheet
xlBook.Worksheets("Sheet1").Activate
Set xlSheet = xlBook.ActiveSheet
xlSheet.Name = strWksName

With xlSheet
.Cells.Font.Name = "Arial"
.Cells.Font.Size = 10
'WHOLE BUNCH OF CODE HERE WORKS WELL.
End With

'Sort Columns; this code works in Excel but not in Access
xlSheet.Columns("A:L").Sort key1:=Columns("C:C"), Order1:=xlAscending,
Header:=xlGuess ', Key2:=Columns("D"), order2:=xlAscending,
Key3:=Columns("A:A"), order3:=xlAscending, Header:=xlGuess




xlBook.Worksheets(strWksName).Activate
xlApp.Visible = True

fExit:
On Error Resume Next
Set xlSheet = Nothing
Set xlBook = Nothing
Set xlApp = Nothing
Exit Sub
End Sub

 

[jaf]

See inline.

Need help.
The following code creates a worksheet from Access, but will not work with a
line added to sort the data. The code is otherwise a well tried and tested.
I need to be able to sort the worksheet, and the Sort Column line below is
tested in Excel and work as desired there. In Access there is an Error 1004,
asking to use single quotes when using the equal sign. I don't know what to
do.
Imran

Public Sub createWksSort(strQuery As String, strWksName As String,
strWhere() As String, strHeader() As String, strFormat() As String,
strParms() As String, blnParms As Boolean)
Dim intI As Integer
Dim intColumns As Integer
Dim rsT As DAO.Recordset
Dim xlApp As Object
Dim xlBook As Object
Dim xlSheet As Object
Dim lngRecCount As Long
Dim blnAll As Boolean
Dim strSql As String
Dim strCell As String
Dim strPage As String
Dim qdf As QueryDef
Dim recArray As Variant
Dim fldCount As Integer
Dim iCol As Integer
Dim iRow As Integer

On Error GoTo fErr
intColumns = UBound(strHeader) - 1
Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Add ' Create a new workbook
xlApp.DisplayAlerts = False
xlApp.DisplayAlerts = True

' Capture reference to first worksheet
xlBook.Worksheets("Sheet1").Activate
Set xlSheet = xlBook.ActiveSheet
xlSheet.Name = strWksName

With xlSheet
.Cells.Font.Name = "Arial"
.Cells.Font.Size = 10
'WHOLE BUNCH OF CODE HERE WORKS WELL.
End With

'Sort Columns; this code works in Excel but not in Access
xlSheet.Columns("A:L").Sort key1:=Columns("C:C"), Order1:=xlAscending,
Header:=xlGuess ', Key2:=Columns("D"), order2:=xlAscending,
Key3:=Columns("A:A"), order3:=xlAscending, Header:=xlGuess

Probably Columns("C:C") should be xlSheet.Columns("C:C")
Same for the DD and AA.
You may also have to add the xlSheet prefix to the xlAscending etc.



John

 

[Jim Thomlinson]

In addition you will have problems with the constants such as xlAscending.
You need to convert those to the actual values. In the Immediate window type

? xlAscending

To get the true constant value.

If you wold like the full list of constant values for XL2002 (that is all I
have here at work) then reply back with an e-mail address and I will send it
to you in a form that can be copied and pasted directly into VBA.

 

[Imran J Khan]

Thank you Jaf. Adding the xlSheet. in front of the .columns worked. However,
it will not work if infront of xlascending as well.

 

[Imran J Khan]

Thank you Jim. Adding the xlSheet. in front of the .columns worked. However,
it will not work if infront of xlascending as well

 

[jaf]

Hi Imran,
Re-read what Jim posted.

"You need to convert those to the actual values. In the Immediate window type

? xlAscending

To get the true constant value."


John