Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Code does not work in Access
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: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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Code does not work in Access
See inline.
"Imran J Khan" wrote in message ... 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: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 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Code does not work in Access
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. -- HTH... Jim Thomlinson "jaf" wrote: See inline. "Imran J Khan" wrote in message ... 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: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 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Code does not work in Access
Thank you Jaf. Adding the xlSheet. in front of the .columns worked. However,
it will not work if infront of xlascending as well. "jaf" wrote: See inline. "Imran J Khan" wrote in message ... 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: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 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Code does not work in Access
Thank you Jim. Adding the xlSheet. in front of the .columns worked. However,
it will not work if infront of xlascending as well "Jim Thomlinson" wrote: 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. -- HTH... Jim Thomlinson "jaf" wrote: See inline. "Imran J Khan" wrote in message ... 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: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 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Code does not work in Access
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 "Imran J Khan" wrote in message ... Thank you Jaf. Adding the xlSheet. in front of the .columns worked. However, it will not work if infront of xlascending as well. "jaf" wrote: See inline. "Imran J Khan" wrote in message ... 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: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 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel code convert to Access code - Concat & eliminate duplicates | Excel Programming | |||
Excel to Access - formula does not work... | New Users to Excel | |||
SQL Work in Access but not in Excel via VBA | Excel Programming | |||
Access query does not work in Excel | Excel Programming | |||
Access data -work in Excel- save in Access | Excel Programming |