473,326 Members | 2,588 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,326 software developers and data experts.

Export to/Save As a copy of template and add date to file name

I have essentially no experience with VBA whatsoever so I'm struggling to piece this together and need help! I got the code below from Kens Access Tips website and it works perfectly, but I need it updated so it saves the worksheet as a copy of the template instead of overwriting the template. I'd also like to add the current date (date the file runs) to the workbook name.

The template is located in a folder titled "C:\UGH\"
The template name is "Follow Up Orders mmddyyyy.xlsx"
The template sheet name is "Orders"
The MS Access table is named "FollowUpOrders"

I want the code to open the template, drop the contents of "FollowUpOrders" into the "Orders" tab, then save the file as "Follow Up Orders 04122018", changing the date to be the current date of whenever it runs, leaving the original template intact.

I sincerely appreciate any and all help you can toss my way!

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2.  
  3.  
  4.  
  5. Function ExportToExcel()
  6.  
  7.  
  8. Dim lngColumn As Long
  9. Dim xlx As Object, xlw As Object, xlsx As Object, xlc As Object
  10. Dim dbs As DAO.Database
  11. Dim rst As DAO.Recordset
  12. Dim blnEXCEL As Boolean, blnHeaderRow As Boolean
  13.  
  14. blnEXCEL = False
  15.  
  16. ' Replace True with False if you do not want the first row of
  17. ' the worksheet to be a header row (the names of the fields
  18. ' from the recordset)
  19. blnHeaderRow = True
  20.  
  21. ' Establish an EXCEL application object
  22. On Error Resume Next
  23. Set xlx = GetObject(, "Excel.Application")
  24. If Err.Number <> 0 Then
  25.       Set xlx = CreateObject("Excel.Application")
  26.       blnEXCEL = True
  27. End If
  28. Err.Clear
  29. On Error GoTo 0
  30.  
  31. ' Change True to False if you do not want the workbook to be
  32. ' visible when the code is running
  33. xlx.Visible = True
  34.  
  35. ' Replace C:\Filename.xlsx with the actual path and filename
  36. ' of the EXCEL file into which you will write the data
  37. Set xlw = xlx.Workbooks.Open("C:\UGH\Follow Up Orders mmddyyyy.xlsx")
  38.  
  39. ' Replace WorksheetName with the actual name of the worksheet
  40. ' in the EXCEL file
  41. ' (note that the worksheet must already be in the EXCEL file)
  42. Set xlsx = xlw.Worksheets("Orders")
  43.  
  44. ' Replace A1 with the cell reference into which the first data value
  45. ' is to be written
  46. Set xlc = xlsx.Range("A1") ' this is the first cell into which data go
  47.  
  48. Set dbs = CurrentDb()
  49.  
  50. ' Replace QueryOrTableName with the real name of the table or query
  51. ' whose data are to be written into the worksheet
  52. Set rst = dbs.OpenRecordset("FollowUpOrders", dbOpenDynaset, dbReadOnly)
  53.  
  54. If rst.EOF = False And rst.BOF = False Then
  55.  
  56.       rst.MoveFirst
  57.  
  58.       If blnHeaderRow = True Then
  59.             For lngColumn = 0 To rst.Fields.Count - 1
  60.                   xlc.Offset(0, lngColumn).value = rst.Fields(lngColumn).Name
  61.             Next lngColumn
  62.             Set xlc = xlc.Offset(1, 0)
  63.       End If
  64.  
  65.       ' write data to worksheet
  66.       Do While rst.EOF = False
  67.             For lngColumn = 0 To rst.Fields.Count - 1
  68.                   xlc.Offset(0, lngColumn).value = rst.Fields(lngColumn).value
  69.             Next lngColumn
  70.             rst.MoveNext
  71.             Set xlc = xlc.Offset(1, 0)
  72.       Loop
  73.  
  74. End If
  75.  
  76. rst.Close
  77. Set rst = Nothing
  78.  
  79. dbs.Close
  80. Set dbs = Nothing
  81.  
  82. ' Close the EXCEL file while saving the file, and clean up the EXCEL objects
  83. Set xlc = Nothing
  84. Set xlsx = Nothing
  85. xlw.Close True   ' close the EXCEL file and save the new data
  86. Set xlw = Nothing
  87. If blnEXCEL = True Then xlx.Quit
  88. Set xlx = Nothing
  89.  
  90.  
  91.  
  92. End Function
Apr 12 '18 #1
1 1442
NeoPa
32,556 Expert Mod 16PB
Let's start with Require Variable Declaration as a freebie ;-)

Now look at line #85. What you're doing there is closing the Workbook, but nowhere do you set a name or do an explicit SaveAs. Workbooks have a .SaveAs method. It's too involved to go into detail here for now but look it up. It isn't hard to find. For the Filename parameter use a string variable that you've previously set using something like :
Expand|Select|Wrap|Line Numbers
  1. strFN = Replace("C:\UGH\FollowUpOrders%D", "%D", Format(Date(), "ddmmyyyy")
After that the xlw.Close should work without any possibility of a prompt so you should be good to go.
Apr 12 '18 #2

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

Similar topics

3
by: CJA | last post by:
Hi I have a script that will compact an Access MDB and a copy to a new folder. I would it to give each export a unique file name. I can do this with an absolute file reference but I am...
2
by: Jamie A. Landers | last post by:
I have a macro that runs to export a report. It dumps a report on the server in RTF format. Here's the problem that I am having. I would like for the report to have a custom file name daily, like...
2
by: Chuck | last post by:
Using a macro to output a query to an Excel file. Is there a way to automatically add todays date in the file spec? As: C:\Access Data\ & todays date.xls Chuck ....
2
by: mamin | last post by:
I have a table that I need to export out to a text fiel, without column headers. I also need to have the file name include currernt date. Can anyone help me? I'm using the following code: ...
3
by: aGSMITH | last post by:
Hello All, I have an exportfile .txt from robocopy utility and I would need to get file name path from the file and save it to file1.txt after get the filename value and save it to file2.txt ...
1
by: milas57 | last post by:
hello everyone im new to php and mysql i been trying to export mysql data in an xml file my code below i have problem picking data in the database to display in the xml file, the xml file only...
1
by: DennisBetten | last post by:
First of all, I need to give some credit to Mahesh Chand for providing me with an excellent basis to export data to excel. What does this code do: As the title says, this code is capable of...
185
by: jacob navia | last post by:
Hi We are rewriting the libc for the 64 bit version of lcc-win and we have added a new field in the FILE structure: char *FileName; fopen() will save the file name and an accessor function will...
1
by: Hoardling1 via AccessMonster.com | last post by:
I have downloaded a Microsoft template .mdb for Access 2003. What I want to do is now save another copy of the template so that I can manipulate the structure to match my setup. Unfortunately,...
2
by: Marisol2 | last post by:
I have some queries in Access 2003 db that I have setup to display as pivots. I can go into design pivot table view and click on and then click on Export to Office MicroSoft Excel. The problem is I...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
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...
1
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: 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...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
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

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.