Full Version: Exporting Data to Excel And Adding A Date File Name
UtterAccess Forums > Microsoft® Access > Access Forms
ajames420
I am trying to export some data to excel from multiple tables. I take approx 20 tables and export them out to 20 different tabs in a workbook. We have come across the issue where a person will forget to change the name of the file after the export. The export happens 2 times per week. I am wondering if there is a way to have the current date it exports automatically append to the of the file name.
xample:
MisshippedByHub.xls = Current
MisshippedByHub022208 = What I would like or something similar.
Is this possible?
THere is the start fo the code for the export file:
CODE
'Exports Data out to Excel Workbook
Set xlApp = New Excel.Application
'/ Here I opened my workbook..
Set xlWB = xlApp.Workbooks.Open("G:\FSPCOMM\NBHQ\PDPOSTMAN\Mis-Shipped Parcel Data\Misshipped By Hub - Detail\MisshippedByHub.xls")
'/ Here I set my worksheet..
Set xlWs = xlWB.Worksheets(1)
Set rs = New ADODB.Recordset
'/ Here I build my recordset..
rs.Open "Select * From ATGA", CodeProject.Connection, _
    adOpenStatic, adLockReadOnly
    Let lngRecCnt = rs.RecordCount
With xlWs
    .Range("a2").Resize(lngRecCnt).EntireRow.Insert
    .Range("a2").CopyFromRecordset rs
End With
    xlApp.Selection.CurrentRegion.Columns.AutoFit
    xlApp.Selection.CurrentRegion.Rows.AutoFit

Thanks for any help you can offer
Doug Steele
The following will rename the file:
ame "G:\FSPCOMM\NBHQ\PDPOSTMAN\Mis-Shipped Parcel Data\Misshipped By Hub - Detail\MisshippedByHub.xls" As "G:\FSPCOMM\NBHQ\PDPOSTMAN\Mis-Shipped Parcel Data\Misshipped By Hub - Detail\MisshippedByHub" & Format(Date(), "mmddyy") & ".xls"
The following will create a copy of the file:
FileCopy "G:\FSPCOMM\NBHQ\PDPOSTMAN\Mis-Shipped Parcel Data\Misshipped By Hub - Detail\MisshippedByHub.xls", "G:\FSPCOMM\NBHQ\PDPOSTMAN\Mis-Shipped Parcel Data\Misshipped By Hub - Detail\MisshippedByHub" & Format(Date(), "mmddyy") & ".xls"
ajames420
Dj,

Basically is there a way to say create file with current date everytime? I want to keep the old files as well?

So if they create it today it would be MisshippedByHub022208.xls
If they run it again tomorrow it would create MisshippedByHub022308.xls and I would still have the MisshippedByHub022208.xls as well.

Thanks
Edited by: ajames420 on Fri Feb 22 11:02:23 EST 2008.
Doug Steele
Your approach requires that the spreadsheet exists in advance. Rather than using
!--c1-->
CODE
  Set xlWB = xlApp.Workbooks.Open("G:\FSPCOMM\NBHQ\PDPOSTMAN\Mis-Shipped Parcel Data\Misshipped By Hub - Detail\MisshippedByHub.xls")

you could create a copy of the spreadsheet first, then open the copy.
CODE
Dim strTodaysFile As String
  
  strTodaysFile = "G:\FSPCOMM\NBHQ\PDPOSTMAN\Mis-Shipped Parcel Data\Misshipped By Hub - Detail\MisshippedByHub" & Format(Date(), "mmddyy") & ".xls"
  FileCopy "G:\FSPCOMM\NBHQ\PDPOSTMAN\Mis-Shipped Parcel Data\Misshipped By Hub - Detail\MisshippedByHub.xls", strTodaysFile
  Set xlWB = xlApp.Workbooks.Open(strTodaysFile)
ajames420
I guess the issue I am having is when I run this it appends all of the new data to then end of the spreadsheets in the work book. I only want each work book to hold that data for that is currently in th table. So a brand new workbook would work best.
Is that an option? Always create a new workbook, define the start of the name then add the date format?
Doug Steele
The option I suggested assumes G:\FSPCOMM\NBHQ\PDPOSTMAN\Mis-Shipped Parcel Data\Misshipped By Hub - Detail\MisshippedByHub.xls is an empty spreadsheet. You make a copy of that empty spreadsheet each day.
If there's nothing special about the spreadsheet, then yes, you could certainly just create a new spreadsheet and name it appropriately.
Hope this resolves the issue for you. I'm off for just over a week, so it's unlikely I'll be able to respond to you before March now.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.