Using OutputTo command in a Macro

[i1055]

I am outputting a simple query to MS Excel using the OutputTo action in a macro, my problem is that the file is created in Excel 95 format and I would like it to be in Excel 97.<br>
<br>
Once in Excel I am able to change the format, but I was hoping there is a way in Access to have the query Output in Excel 97.<br>
<br>
Thanks for your assistance.

 

[DougP]

If are using Access '95 then NO it will not convert to Excel '97 because back in 1995 when both (Access '95 and Excel '95) were writen, Excel '97 and Access '97 did not exist yet.<br>
Now if you are in Access '97 then you could write function<br>
like this<br>
<br>
Public Function ExportExcel97()<br>
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, &quot;MyQuery&quot;, &quot;c:\NewExcel97File.xls&quot;<br>
End Function<br>
<br>
to do it for you.<br>
<br>

 

[i1055]

Doug, thanks for the quick reply, the function worked perfectly, although my inexperience with modules, functions, etc. caused me some grief.<br>
<br>
Thanks again.

 

[DougP]

They caused be tremendous grief in the beginning gee that was 5 years ago in Access 2.0<br>
And each time Microsoft upgrades to ’95 ’97 and now 2000 you have to learn a new twist.<br>
<br>
I keep a folder on the Server with code snippets of things I have come across.<br>
I have 165 files there now after 5 years.<br>
How to do certain things in code for Access, VB6, and AutoCAD.<br>
See VBA code works in a lot of applications.<br>
<br>
Each file in the folder is a text document. I use notepad paste the working debugged code in there than name it to something I can find later.<br>
Such as Find.txt finds records using SQL statements and so forth.<br>
<br>
Then I look in the folder find the document open it copy and paste the code in Access and move on.<br>
<br>
So if you learn it you will be learning something very useful.<br>
Code is very powerful and I do things in it that even makes my head spin in delight.<br>
Not to mention the outstanding productivity gains here where I work.<br>
One day you too will be a Jedi Master.<br>
<br>

 

[elizabeth]

DougP have you checked out the Code Librarian that ships with Access 2000 Developer? I haven't yet but sounds like it works similarly to the FMS module which was very nice.

 

[DougP]

No I actually am still using '97 now<br>
We have 2000 and I installed it on one machine and loaded one of our 25 databases and it had problems.<br>
So I have not gotten back to it yet.<br>

 

[i1055]

doug, the function worked fine the first couple of times, but today i received an error message : Run-time error '3274' External table isn't in the specified format.<br>
<br>
i get this error message the first time i run the function, but when i close the error message and run it again, it works perfectly<br>
<br>
i am very very new to using mods and any VB<br>
<br>