save query as excel 97 using VB

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello all,

First let me tell you what I'm trying to do and let me know if there is a
better way.

I am wanting to be able to click on a button and have it export a query and
save it to the an excel format (97), without having to do anything else. It
would also be nice to be able to have it let me select where it is going to
save, but that might just be another question for later.

I can select the query then click on the "save Expot", but really would like
this to be a one button push process.

Is there a way in VB to save as and Excel format? I have saved files before
in code using csv, but would like to save as excel if possible.
 
Hello all again,

I found some of my answer by using a wizard for button's. However it
doesn't do one thing. I Would like to keep a format on the excel sheet, i.e.
color, size, etc. Is this possible ? Here is the code it created. Once
clicked it pops up a box of what format (Excel, Rich Format, etc) I would
like, but then it over writes everything.

Dim stDocName As String

stDocName = "rpTtlConx"
DoCmd.OutputTo acReport, stDocName
 
Is there a way in VB to save as and Excel format? I have saved files before
in code using csv, but would like to save as excel if possible.
Click to expand...

Take a look at the Help for "TransferSpreadsheet" - that's the VBA
method which can export a recordset to an Excel workbook.

John W. Vinson[MVP]
 
Thanks! That is what I was looking for.

John Vinson said:
Take a look at the Help for "TransferSpreadsheet" - that's the VBA
method which can export a recordset to an Excel workbook.

John W. Vinson[MVP]
Click to expand...
 
Fully tested and functional.
Attach code to onclick of command button.


Dim strFilter As String
Dim strSaveFileName As String

'Display dialog
strFilter = ahtAddFilterItem(strFilter, _
"Excel Files (*.XLS)", "*.XLS")
strSaveFileName = ahtCommonFileOpenSave( _
OpenFile:=False, _
Filter:=strFilter, _
Flags:=ahtOFN_OVERWRITEPROMPT Or ahtOFN_READONLY)

If Len(strSaveFileName) > 0 Then 'User entered a filename
DoCmd.TransferSpreadsheet acExport, _
acSpreadsheetTypeExcel8, "EnterQueryNameHere", _
strSaveFileName
Else 'User pressed Cancel
'Do nothing

End If
 
Can you give me an idea of how I could do this using VB.NET in a windows app,
pulling from a database query? Thanks for your help!
 
No, sorry. No idea.

Hyperactive said:
Can you give me an idea of how I could do this using VB.NET in a windows app,
pulling from a database query? Thanks for your help!
Click to expand...
 
If you use this code, you must download a module which Dylan's code is
referencing to. Maybe he forgot to mention?

go to http://www.mvps.org/access/api/api0001.htm

and copy the code from

'******Code Start******

to

'++++++Code End******

and paste it in a new module. Save it as anything and then insert Dylan's
code to your control. It will now work.
 
Back
Top