Open an Excel spreadsheet after it has been exported from Access

[glat]

Hello.

I have the following code to save my Access query, export it to my Excel spreadsheet and rename the tab:

If Me.Dirty Then 
Me.Dirty = False 
End If 
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "InvoiceData", "X:\Spreadsheets\Invoices\InvoiceData.xlsx", True, "DailyData"

What I would like to do is have the Excel spreadsheet open once it has been exported. I've seen a lot of ways to do it but not if I want to rename my spreadsheet at the same time. Can this be done?

Any help is much appreciated.

GLAT

[NeoPa]

Hi, and welcome to Bytes.com.

If you use Application Automation to open the spreadsheet you should be able to rename any Worksheet therein.

[glat]

Hi NeoPa,

Thank you for your reply which I have just seen.

My apologies if I have given you the impression I know about coding as unfortunately, I know very little (if anything). The code above I copied from a forum and made it work by changing the references to my documents, so the coding was already done. The topic you directed me to is way above anything I would know about. Is there a more user friendly topic you could point me to help me?

Any help is much appreciated.

Glat.

[ADezii]

It is actually a lot simpler than you may realize. The following Code will:

1. Export the Inventory Query to the C:\Test\ Folder and name it Inventory.xls
2. Create an Instance of Excel
3. Open the previously exported Query and make it visible in Excel
4. Change the Worksheet Name to something unique
5. Save the Workbook while still keeping it open

Dim appExcel As Excel.Application
Dim wb As Excel.Workbook
Const conEXPORT_PATH = "C:\Test\Inventory.xls"

'Export the Inventory Query to an Excel Workbook named C:\Test\Inventory.xls
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Inventory", conEXPORT_PATH, True

Set appExcel = New Excel.Application    'Create a NEW Instance of Excel
    appExcel.Visible = True             'Make the NEW Instance Visible
    appExcel.UserControl = True         'Instance of Excel was created by the User
Set wb = appExcel.Workbooks.Open(conEXPORT_PATH, , False)    'Open the Exported Workbook

'Give the Worksheet a unique Name indicating the current Date/Time
wb.Worksheets(1).Name = "Inventory_" & Format(Now, "mm.dd.yy hh.nn.ss")

wb.Save   '"Save the Workbook, keeping it Open"

P.S. - The Code is in it's simplest state. I used Early Binding (if you are not sure what that is, NeoPa or I can explain it to you) and there is no Error Checking involved.

[NeoPa]

Hi Glat.

GLAT:
The topic you directed me to is way above anything I would know about. Is there a more user friendly topic you could point me to help me?

If there is a way to code without having even a basic level of competence at coding then I don't know anything about it. If I were to guess I could only suggest various AI sources, but as the old maps used to say - That way be dragons. IE. There are serious dangers involved with trusting anything AI and if you don't even have the understanding to know what is good and what is not then not a route I'd want to recommend.

If you have a situation where you need something done and that thing is beyond your current level of competence (and your request is certainly beyond the basic level), then the options I see are (One) to educate/train yourself to the required point or (Two) hire someone to do the work for you. Same as you would if you needed shelves installed but weren't too great at DIY. The concept's the same.

[ADezii]

Always words of wisdom, my friend.

[glat]

You're both right. This is well above what I know but thank you anyway.