TransferSpreadsheet

[gavin]

I've managed to create a button and write a simple piece of code (that's a
BIG deal for me!) using the TransferSpreadsheet action to export the records
in my table to an Excel spreadsheet. Now what I'd like to be able to is to
add to the code to make that spreadsheet open as soon as the transfer is
complete. I'm assuming that's possible but I've done some Googling (found
nothing useful) and some "trail and error" but I can't get it working.

Any pointers would be very gratefully received.

 

[fredg]

I've managed to create a button and write a simple piece of code (that's a
BIG deal for me!) using the TransferSpreadsheet action to export the records
in my table to an Excel spreadsheet. Now what I'd like to be able to is to
add to the code to make that spreadsheet open as soon as the transfer is
complete. I'm assuming that's possible but I've done some Googling (found
nothing useful) and some "trail and error" but I can't get it working.

Any pointers would be very gratefully received.

DoCmd.TransferSpreadsheet .....etc ....
Applicaiton.FollowHyperlink "C:\PathToFolder\SpreadsheetName.xls"

 

[akphidelt]

You can also do something like this

Private Sub Command68_Click()
Dim objXls As Excel.Application
Dim myFile As String


DoCmd.TransferSpreadsheet acExport, 8, "yourqueryortable", "yourpath", True

Set objXls = CreateObject("Excel.Application")
myFile = "yourpath"
objXls.Workbooks.Open ("" & myFile)
objXls.Visible = True

The main thing to remember is objXls.Visible = True is what opens up the
excel spreadsheet.

 

[RoLaAus]

Gavin, using the suggestion from akphiedelt is referred to as
Automation (calling 1 program from another and using that call to
control actions in it). Doing this will allow you to do many
different things in your VBA code, such as manipulating cells, both
the content and formatting - however if all you want is the
spreadsheet to open, then I would follow fredg's suggestion of
Applicaiton.FollowHyperlink "C:\PathToFolder\SpreadsheetName.xls",
or even do a
Shell "C:\program files\office\excel.exe """ & "C:\filepath
\filename.xls" & """
You'll notice the """ This is because you need to use the quotation
marks to pass the filename to Excel.exe (which you would have to
modify my example to fit where Excel is installed on your pc.

 

[gavin]

Gavin, using the suggestion from akphiedelt is referred to as
Automation (calling 1 program from another and using that call to
control actions in it). Doing this will allow you to do many
different things in your VBA code, such as manipulating cells, both
the content and formatting - however if all you want is the
spreadsheet to open, then I would follow fredg's suggestion of
Applicaiton.FollowHyperlink "C:\PathToFolder\SpreadsheetName.xls",
or even do a
Shell "C:\program files\office\excel.exe """ & "C:\filepath
\filename.xls" & """
You'll notice the """ This is because you need to use the quotation
marks to pass the filename to Excel.exe (which you would have to
modify my example to fit where Excel is installed on your pc.

Many thanks for the great help everyone - it really is much appreciated.

Can I just ask what the advantages/disadvantages of both approaches is?

 

[gavin]

Gavin, using the suggestion from akphiedelt is referred to as
Automation (calling 1 program from another and using that call to
control actions in it). Doing this will allow you to do many
different things in your VBA code, such as manipulating cells, both
the content and formatting - however if all you want is the
spreadsheet to open, then I would follow fredg's suggestion of
Applicaiton.FollowHyperlink "C:\PathToFolder\SpreadsheetName.xls",
or even do a
Shell "C:\program files\office\excel.exe """ & "C:\filepath
\filename.xls" & """
You'll notice the """ This is because you need to use the quotation
marks to pass the filename to Excel.exe (which you would have to
modify my example to fit where Excel is installed on your pc.

You can also do something like this

Private Sub Command68_Click()
Dim objXls As Excel.Application
Dim myFile As String

DoCmd.TransferSpreadsheet acExport, 8, "yourqueryortable", "yourpath",
True

Set objXls = CreateObject("Excel.Application")
myFile = "yourpath"
objXls.Workbooks.Open ("" & myFile)
objXls.Visible = True

The main thing to remember is objXls.Visible = True is what opens up the
excel spreadsheet.

Thanks again for the expert help. Can I be a pain and ask one more question
(I think I'll probably end up with more than one!)?

Rather than transfer the whole of the table to Excel can I transfer a subset
of the data via a query?