Excel report using old version of MS excel

[Shakss2]

Hello all,

Im displaying a report in excel.

below is my code:

Dim strPath As String
' Current path
strPath = CurrentProject.Path & "\Adhoc_Report.xls"
'New workbook object
Dim sh As Workbook
DoCmd.OutputTo acOutputTable, "test2", acFormatXLS, strPath, 1
'Setting obj
Set sh = CreateObject(strPath)
'sh.ActiveSheet.Cells.EntireColumn.AutoFit
sh.ActiveSheet.Cells.ColumnWidth = 23
sh.ActiveSheet.Cells.EntireRow.AutoFit

The excel workbook is a Microsoft excel 5.0/95 Workbook.
But the system has the 2003 version installed.
cuz of this old version my report has cell limit of 255 only which truncatest the data.

I strongly believe I should be able to specify the version to be used in my code.
help with the code will be grately appreciated.

Thanks
Shaq

[ADezii]

Hello all,

Im displaying a report in excel.

below is my code:

Dim strPath As String
' Current path
strPath = CurrentProject.Path & "\Adhoc_Report.xls"
'New workbook object
Dim sh As Workbook
DoCmd.OutputTo acOutputTable, "test2", acFormatXLS, strPath, 1
'Setting obj
Set sh = CreateObject(strPath)
'sh.ActiveSheet.Cells.EntireColumn.AutoFit
sh.ActiveSheet.Cells.ColumnWidth = 23
sh.ActiveSheet.Cells.EntireRow.AutoFit

The excel workbook is a Microsoft excel 5.0/95 Workbook.
But the system has the 2003 version installed.
cuz of this old version my report has cell limit of 255 only which truncatest the data.

I strongly believe I should be able to specify the version to be used in my code.
help with the code will be grately appreciated.

Thanks
Shaq

Try:

DoCmd.OutputTo acTable, "Test2", "MicrosoftExcelBiff5(*.xls)", strPath", 1

[Shakss2]

Hey Thanks for that reply...

But im getting the following error if I use this:
Runtime error 429
ActiveX component cant create object

But if I use the below code, it works:
DoCmd.OutputTo acOutputTable, "test2", acFormatXLS, strPath, 1

Below is the complete code:
Dim strPath As String
' Current path
strPath = CurrentProject.Path & "\Adhoc_Report.xls"
' New workbook object
Dim sh As Workbook
DoCmd.OutputTo acOutputTable, "test2", acFormatXLS, strPath, 1
' This gives the above error:
' DoCmd.OutputTo acTable, "test2", "MicrosoftExcelBiff5(*.xls)", "strPath", 1

' Setting obj
Set sh = CreateObject(strPath)
sh.ActiveSheet.Cells.ColumnWidth = 23
sh.ActiveSheet.Cells.EntireRow.AutoFit

Shaq

[Shakss2]

Sorry for the previous post...

It was my mistake...
That seem to work but it is still using the old version 5.
How can i force it to use version 7 or 8...

Below is the complete code:
Dim strPath As String
' Current path
strPath = CurrentProject.Path & "\Adhoc_Report.xls"
' New workbook object
Dim sh As Workbook

DoCmd.OutputTo acTable, "test2", "MicrosoftExcelBiff5(*.xls)", "strPath", 1

' Setting obj
Set sh = CreateObject(strPath)
sh.ActiveSheet.Cells.ColumnWidth = 23
sh.ActiveSheet.Cells.EntireRow.AutoFit

Shaq

[Shakss2]

I also tried:
DoCmd.OutputTo acTable, "test2", "MicrosoftExcelBiff8(*.xls)", "strPath", 1
But it is still using version 5

Shaq

[ADezii]

I also tried:
DoCmd.OutputTo acTable, "test2", "MicrosoftExcelBiff8(*.xls)", "strPath", 1
But it is still using version 5

Shaq

'I see no reason why this shouldn't work:

DoCmd.OutputTo acTable, "Test2", "MicrosoftExcelBiff8(*.xls)", strPath, 1

[Shakss2]

Hi...

I did this...n it worked...
DoCmd.OutputTo acTable, "test2", acSpreadsheetTypeExcel9, "strPath", 1

Thanks a lot for ur support

Shaq