I
Imran J Khan
Need help.
I have read previous posts here about explicitly naming Excel objects, but
still the following code will not work. The code is a well tried and tested
one with one new modification, the Sort Column line. Without that line it
works well. I need to be able to sort the worksheet, and this line is tested
in Excel and work as desired there. Error is 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
I have read previous posts here about explicitly naming Excel objects, but
still the following code will not work. The code is a well tried and tested
one with one new modification, the Sort Column line. Without that line it
works well. I need to be able to sort the worksheet, and this line is tested
in Excel and work as desired there. Error is 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