Renaming exported excel file (1 Viewer)

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
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
 

Attachments

  • filename part field date.png
    filename part field date.png
    38.7 KB · Views: 66

pbaldy

Wino Moderator
Staff member
Local time
Today, 01:51
Joined
Aug 30, 2003
Messages
36,118
Use a DLookup() to get the date from the query and then use that instead of the Date() function.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 16:51
Joined
May 7, 2009
Messages
19,169
Change this portion:

InitialFileName = Me.txtPath & Format(Date, "dd-mm-yy") & "-" & Me.txtFile

To:

rsDriver.Movefirst
InitialFileName = Me.txtPath & Format(rsDriver!Entry_Date, "dd-mm-yy") & "-" & Me.txtFile
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 16:51
Joined
May 7, 2009
Messages
19,169
Is there date field in the recordset, use that otherwise use baldy method.
 

lookforsmt

Registered User.
Local time
Today, 12:51
Joined
Dec 26, 2011
Messages
672
Thanks, i have added a date field in the table and renamed the file name. Thanks arnlegp & baldy for our advice

warm regards
 

Users who are viewing this thread

Top Bottom