0

I was trying to export A report from Access to Excel using the code below.

   pat = CurrentProject.Path
    Set xlo = CreateObject("Excel.Application")
    Nname = "MonthlyData" & Left(Now(), 5)
 DoCmd.OutputTo acOutputReport, "MonthlyAll", "Excel 97 - Excel 2003 Workbook (*.xls)", pat & "\" & Nname & ".xls", True

It works fine in my system but isn't working on any other I have tried since. I am not able to export to any other excel formats in my system as well. I have the following references selected in my system,

Visual Basics for Applications Microsoft Access 15.0 object Library OLE Automation Microsoft Office 15.0 Access database engine object Library Microsoft Excel 15.0 object Library Microsoft Office 15.0 object Library

I haven't checked for these references yet. Not sure if that is the reason. I am using windows 10 which is not the problem. I have checked for compatibility.

srt
  • 521
  • 3
  • 11
  • 22

2 Answers2

1

You will probably need to use late binding instead of early binding to ensure that whatever version of excel / office is in use your references are created on the fly for the version in use.

This thread will proabably help Convert Early Binding VBA to Late Binding VBA : Excel to Outlook Contacts

Minty
  • 1,616
  • 1
  • 8
  • 13
  • I have done that. Now the error is 2282 the format in which you are attempting to put the current object is not available. I have provided "Excel 97 - Excel 2003 Workbook (*.xls)" as the acFormatXLS creates file in Excel 95 which is blocked in my excel application. – srt Oct 24 '17 at 14:37
1

You provide the DoCmd.OutputTo parameter OutputFormat as string, this is language-specific.

Use the constant acFormatXLS instead, it should work on all systems.

DoCmd.OutputTo acOutputReport, "MonthlyAll", acFormatXLS, pat & "\" & Nname & ".xls", True

Actually, this won't help. I assumed the constant was a numeric value, but

Const acFormatXLS = "Microsoft Excel (*.xls)"

Note: If I try it with your format string "Excel 97 - Excel 2003 Workbook (*.xls)", it works with a German Office 2010. So this is probably not the problem.

Left(Now(), 5) might introduce illegal characters for file names, depending on the regional settings.

Use e.g. this instead:

Nname = "MonthlyData" & Format(Date(), "yy-mm")
Andre
  • 26,751
  • 7
  • 36
  • 80
  • acFormatXLS creates file in Excel95 which is blocked in my excel application. It cannot be unblocked as it is greyed out in File block settings. – srt Oct 24 '17 at 14:30
  • The output file name I am getting in my system is "MonthlyData24-10" – srt Oct 24 '17 at 14:38
  • @srt: See edit. -- Other systems may have different regional date/time settings, and their date format may include `/`, which is illegal in a file name. – Andre Oct 24 '17 at 14:46
  • Thanks. Sry didn't notice it earlier. – srt Oct 24 '17 at 15:00