By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,220 Members | 1,512 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,220 IT Pros & Developers. It's quick & easy.

Excel report using old version of MS excel

Shakss2
P: 19
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
Feb 8 '08 #1
Share this Question
Share on Google+
6 Replies


ADezii
Expert 5K+
P: 8,679
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:
Expand|Select|Wrap|Line Numbers
  1. DoCmd.OutputTo acTable, "Test2", "MicrosoftExcelBiff5(*.xls)", strPath", 1
Feb 12 '08 #2

Shakss2
P: 19
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
Feb 13 '08 #3

Shakss2
P: 19
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
Feb 13 '08 #4

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

Shaq
Feb 13 '08 #5

ADezii
Expert 5K+
P: 8,679
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:
Expand|Select|Wrap|Line Numbers
  1. DoCmd.OutputTo acTable, "Test2", "MicrosoftExcelBiff8(*.xls)", strPath, 1
Feb 13 '08 #6

Shakss2
P: 19
Hi...

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

Thanks a lot for ur support

Shaq
Feb 13 '08 #7

Post your reply

Sign in to post your reply or Sign up for a free account.