[Webinar] Streamline your web hosting managementRegister Today


Using VBA to update spreadhseet

Posted on 2010-04-05
Medium Priority
Last Modified: 2012-05-09
This is a followup to another question I posted on EE.  I have already given the max points on that one so I am following up with another question.Please see
to get the background on this one.

I have VBA code that creates a spreadsheet from within MS Access.  I then have code contributed by and EE member to alter some attributes in the spreadsheet.  The make the EE code compile I added a reference to MS Excel 11.0 Object library to the MDB.  

The sequence of events in my code is to

1. Create the spreadsheet with the statement:

DoCmd.OutputTo acOutputQuery, "qryMortgageExportOutput", acFormatXLS, wkFileName, False

2. Using the logic from 'GrahamMandeno' I revise the spreadsheet to have the checkboxes using the statement:

wkReturn = ExcelCreateColumnCheckboxes(wkFileName, "Confirm Payment", 1)

What is occuring in production, that didn't occur in test, is that I am getting the message "xxxxxxx is an MS Excel 5.0/95 Workbook.  Do you want to overwrite it with the latest Excel format? Yes, No, Cancel.

It is easy enough to answer this question but in reality these spreadsheets are going to be created in bunches, so the user will have to answer the question many times.  Is there any way to suppress the message and have the system automatically answer "yes' each time?

Or, is there any way to keep the condition causing the message from occuring?  Why are the spreadsheets being created in Excel 5.0/95 format.  I didn't get the message when I was running the 'checkbox' logic on an existing spreadsheet as opposed ot one that was created in the same code stream.

Question by:mlcktmguy
LVL 85
ID: 29789334
We'd need to see the ExcelCreateColumnCheckBoxes function in order to help.

Author Comment

ID: 29790804
Is the link that I posted not working?  The entire code is in the question at the link I posted.
LVL 19

Accepted Solution

Ken Butters earned 1000 total points
ID: 29803603
try using docmd.transferSpreadsheet instead of docmd.OutputTo


docmd.TransferSpreadsheet(TransferType, SpreadsheetType, TableName, FileName, HasFieldNames, Range, UseOA)

SpreadsheetType is used to specify the  version of excel being used.
see: http://msdn.microsoft.com/en-us/library/bb214134.aspx

acSpreadsheetTypeExcel3 0 Microsoft Excel 3.0 format
acSpreadsheetTypeExcel4 6 Microsoft Excel 4.0 format
acSpreadsheetTypeExcel5 5 Microsoft Excel 5.0 format
acSpreadsheetTypeExcel7 5 Microsoft Excel 95 format
acSpreadsheetTypeExcel8 8 Microsoft Excel 97 format
acSpreadsheetTypeExcel9 8 Microsoft Excel 2000 format
acSpreadsheetTypeLotusWJ2 4 Japanese version only
acSpreadsheetTypeLotusWK1 2 Lotus 1-2-3 WK1 format
acSpreadsheetTypeLotusWK3 3 Lotus 1-2-3 WK3 format
acSpreadsheetTypeLotusWK4 7 Lotus 1-2-3 WK4 format
acSpreadsheetTypeExcel12 => Value: 9; Format: xls
acSpreadsheetTypeExcel12Xml => Value: 10; Format: .xlsx

LVL 20

Expert Comment

ID: 29833125
Hello again
I posted this as an addendum to your previous question before I noticed that you'd started a new question.  So, just in case you missed it...
I suggest you use TransferSpreadsheet instead of OutputTo.  You get to choose the format of spreadsheet to create - including the version of Excel:
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, _
  "qryMortgageExportOutput", wkFileName,True
Note that acSpreadsheetTypeExcel8 signifies the Excel 97-2003 format.

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…
Suggested Courses
Course of the Month7 days, 17 hours left to enroll

607 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question