473,325 Members | 2,712 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,325 software developers and data experts.

Open an Excel spreadsheet after it has been exported from Access

62 32bit
Hello.

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

Expand|Select|Wrap|Line Numbers
  1. If Me.Dirty Then 
  2. Me.Dirty = False 
  3. End If 
  4. 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
Apr 18 '23 #1
6 12992
NeoPa
32,556 Expert Mod 16PB
Hi, and welcome to Bytes.com.

If you use Application Automation to open the spreadsheet you should be able to rename any Worksheet therein.
Apr 18 '23 #2
glat
62 32bit
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.
Apr 19 '23 #3
ADezii
8,834 Expert 8TB
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
Expand|Select|Wrap|Line Numbers
  1. Dim appExcel As Excel.Application
  2. Dim wb As Excel.Workbook
  3. Const conEXPORT_PATH = "C:\Test\Inventory.xls"
  4.  
  5. 'Export the Inventory Query to an Excel Workbook named C:\Test\Inventory.xls
  6. DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Inventory", conEXPORT_PATH, True
  7.  
  8. Set appExcel = New Excel.Application    'Create a NEW Instance of Excel
  9.     appExcel.Visible = True             'Make the NEW Instance Visible
  10.     appExcel.UserControl = True         'Instance of Excel was created by the User
  11. Set wb = appExcel.Workbooks.Open(conEXPORT_PATH, , False)    'Open the Exported Workbook
  12.  
  13. 'Give the Worksheet a unique Name indicating the current Date/Time
  14. wb.Worksheets(1).Name = "Inventory_" & Format(Now, "mm.dd.yy hh.nn.ss")
  15.  
  16. wb.Save   '"Save the Workbook, keeping it Open"
  17.  
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.
Apr 19 '23 #4
NeoPa
32,556 Expert Mod 16PB
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.
Apr 19 '23 #5
ADezii
8,834 Expert 8TB
Always words of wisdom, my friend.
Apr 19 '23 #6
glat
62 32bit
You're both right. This is well above what I know but thank you anyway.
Apr 25 '23 #7

Sign in to post your reply or Sign up for a free account.

Similar topics

0
by: kate funnell | last post by:
Dear All I want to open an excel template from a specific location i.e. C:\MyDocuments\Sheet1 using a button on a form in access 2000. Obviously when the spreadsheet opens it will re-name...
6
by: syvman | last post by:
Hi everyone... I am pulling my hair out trying to do this, and was wondering if someone could give me some assistance... I have an Excel spreadsheet containing several worksheets. I'd like to be...
3
AllusiveKitten
by: AllusiveKitten | last post by:
Hi, I really hope you can help... I am currently trying to write a code that will take an excel spreadsheet & save it to an access Table. I come up with an error "Operation is not allowed when...
5
by: Roo06 | last post by:
I am trying to open a spreadsheet from Acess based on a value in a textbox. I don't how to create the reference to the path and file name and was wondering if someone could help. I am new to VBA and...
3
by: gra | last post by:
Hi Access 2002 I have a Command Buton using some VB code to open an Excel spreadsheet. However, when the spreadsheet opens it doesn't refresh the data. The spreadsheet is a pivot table which...
2
by: BankGirl | last post by:
hi i am trying to open a excel spreadsheet automatically at the push of a button. i am using the below code that i obtained via another help message but i keep getting an error message saying...
2
by: edgalljr | last post by:
I have Access 2000 programmed to run a very simple code to run a query and OutputTo an Excel spreadsheet: DoCmd.OutputTo acOutputQuery, "XLS Applicants", acFormatXLS, "C:\My RMBC...
1
by: rmmiller44 | last post by:
I have a web page with an HTML dropdown box where users select from among various cities, e.g. Atlanta, New York, Los Angeles, etc. The desired output is a table and charts with data for that city. ...
3
by: Teresa Barnacle | last post by:
Hi Need a little help or advice. I have a DBase with 2 tables a. Candidate Personal Details b. Candidate Registration Detail (reg for a qualification) A customer fills out a excel...
1
by: preciouslife73 | last post by:
I have read a few available functions similar to my concerns. And I have noticed some were not answered for a long time. My concerns is that what was provided in the forum, when a query or a table...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.