If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
TransferSpreadsheet
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. |
#2
|
|||
|
|||
TransferSpreadsheet
On Fri, 29 Feb 2008 17:56:32 GMT, gavin wrote:
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" -- Fred Please respond only to this newsgroup. I do not reply to personal e-mail |
#3
|
|||
|
|||
TransferSpreadsheet
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. "gavin" wrote: 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. |
#4
|
|||
|
|||
TransferSpreadsheet
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. On Feb 29, 11:20*am, akphidelt wrote: 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. "gavin" wrote: 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.- |
#5
|
|||
|
|||
TransferSpreadsheet
"RoLaAus" wrote in message ... 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? |
#6
|
|||
|
|||
TransferSpreadsheet
"RoLaAus" wrote in message ... 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. On Feb 29, 11:20 am, akphidelt wrote: 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? |
Thread Tools | |
Display Modes | |
|
|