lookforsmt
Registered User.
- Local time
- Today, 12:51
- Joined
- Dec 26, 2011
- Messages
- 672
Hi! all
i am looking over the forum with not much luck to find my answer
I am exporting the excel file from query and able to rename part of the file name by today's date. This is working fine.
What i am looking is to now have the date changed to the field data from the column (Entry_Date) in query
To make it simple i am attaching the snap shot of the query where i want to rename the file + the field date
if this possible.
Below is the query which i want to amend with the above field date
I am getting the excel file name as 20-03-18-1DemoExport.xlsx which is today's date.
based on the query snapshot the date mentioned is "27-10-2017" This is the date i want to appear in the filename. If this is possible. Any help will be much appreciated.
Thanks
i am looking over the forum with not much luck to find my answer
I am exporting the excel file from query and able to rename part of the file name by today's date. This is working fine.
What i am looking is to now have the date changed to the field data from the column (Entry_Date) in query
To make it simple i am attaching the snap shot of the query where i want to rename the file + the field date
if this possible.
Below is the query which i want to amend with the above field date
Code:
Private Sub cmdExport_Click()
On Error GoTo Err_Handler
Dim db As DAO.Database
Dim rsDriver As DAO.Recordset
Dim rsSrc As DAO.Recordset
Dim xlApp As Excel.Application
Dim wbDest As Excel.Workbook
Dim wsDest As Excel.Worksheet
Dim Fdia As FileDialog
Dim ThisTable As String
Dim NameSheet As String
Dim FilToSave
Dim i As Long
Dim strSQL As String '-- Create SQL update statement
Me.Dirty = False ' if you've changed any fields, you need to save the form or you'll still be working with the old value
Set xlApp = CreateObject("Excel.Application")
Set wbDest = xlApp.Workbooks.Add
Set db = CurrentDb
Set rsDriver = db.OpenRecordset("Select * from TableList where TableList.Name_chk=Yes") 'new
Do Until rsDriver.EOF
ThisTable = rsDriver![TableName]
NameSheet = rsDriver![SheetName]
Set rsSrc = db.OpenRecordset(ThisTable)
If Not rsSrc.EOF Then
Set wsDest = wbDest.Worksheets.Add
wsDest.Name = NameSheet
For i = 1 To rsSrc.Fields.Count ' this loop puts in the field names.
wsDest.Cells(1, i) = rsSrc.Fields(i - 1).Name ' field numbers start at zero, excel starts at 1
Next i
wsDest.Range("A2").CopyFromRecordset rsSrc
End If
rsDriver.MoveNext
Loop
Set Fdia = FileDialog(msoFileDialogSaveAs)
With Fdia
.InitialFileName = Me.txtPath & Format(Date, "dd-mm-yy") & "-" & Me.txtFile
If .Show Then
FilToSave = .SelectedItems(1)
End If
End With
Exit_Handler:
Exit Sub
wbDest.saveas FilToSave
Set wbDest = Nothing
Set wsDest = Nothing
xlApp.Quit
Set xlApp = Nothing
Set rsDriver = Nothing
Set rsSrc = Nothing
Err_Handler:
MsgBox "Error " & Err.Number & " " & Err.Description, vbExclamation, "Program error"
GoTo Exit_Handler
Exit Sub
End Sub
I am getting the excel file name as 20-03-18-1DemoExport.xlsx which is today's date.
based on the query snapshot the date mentioned is "27-10-2017" This is the date i want to appear in the filename. If this is possible. Any help will be much appreciated.
Thanks