Results 1 to 9 of 9
  1. #1
    skydivetom is offline Expert
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    779

    Exporting tables starting with "tbl_" into Excel. Need to delete XLS file prior to execution.

    Hello Forum Experts:

    I need some assistance with tweaking the VBA code below. Allow me to provide some background first.

    1. I have a routine that generates n tables staring with prefix "tbl_".
    2. The number of tables created in routine GENERATE_TABLES is a function of the source file.
    3. That is, if I run the GENERATE_TABLES routine for Source_A, I may get 8 tables (with prefix "tbl_). However, if I run the same routine for "Source_B", I may get 10 tables.

    All of the above works great and does NOT need any modification.

    Now, here's what I need some assistance with.
    a. Below routine EXPORT_TABLES exports all tables with a prefix = "tbl_" into a spreadsheet.
    b. If I run this export routine after I generated tables for source "Source_A" (8 tables), the XLS will include 8 tabs. Good!
    c. Next, if I re-run GENERATE_TABLES based on "Source_B" (10 tables) and re-run EXPORT_TABLES, I will have 10 tabs in the XLS. Great!
    d. However, if re-run GENERATE_TABLES based on "Source_A" (8 tables) a 2nd time and then re-run EXPORT_TABLES, I still end up with 10 tabs in the XLS.
    e. Based on d, I now have 8 tabs from "Source_A" and 2 tabs from "Source_B". I do NOT want that!

    Here is/are my question(s):



    - How do I need to modify the code so that the XLS will only include the tables/tabs based on current n tables in the database?
    - I would presume, the easiest is to delete the XLS *prior* to each execution of EXPORT_TABLES. If so, how can that be done given that the XLS filename now reads "ExportData_20210616.xls" (naturally, tomorrow's filename will read "ExportData_20210617.xls")?
    - Also, and this is a slightly different question, how can I export the data into XLXS format (vs. XLS format)? Just changing the file extension does NOT work... it results in an error "Excel cannot open the file..." when attempting to actually open up the spreadsheet.

    Code:
    Private Sub Export_Tables_Click()
    
        'Declare variables
        Dim db As DAO.Database
        Dim tdf As DAO.TableDef
        Dim n As Integer
     
        'For loop for exporting tables
        Set db = CurrentDb
        For Each tdf In db.TableDefs
            If Left(tdf.Name, 4) = "tbl_" Then
                
                outputFileName = CurrentProject.Path & "\ExportData_" & Format(Date, "yyyyMMdd") & ".xls"
                DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, tdf.Name, outputFileName, True
                
                n = n + 1
            End If
        Next
        
        If n = 0 Then
            'Do nothing
        Else
            MsgBox n & " tables have been exported.", vbInformation, "Export Tables"
        End If
    
    End Sub

  2. #2
    Minty is online now VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    1,945
    If you use transferspreadsheet it will re-use an existing spreadsheet of the same name.
    You need to check if the file exists first and if so delete it.

    Second question - use acSpreadsheetTypeExcel12Xml to generate an xlsx file.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  3. #3
    Minty is online now VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    1,945
    Some code to help with the above;

    Code:
        If FileExists(sPath & sFileName) Then
            If MsgBox("The output file " & sFileName & " already exists, do you want to overwrite it?", vbInformation + vbYesNo, "File Already Exists!") = vbNo Then
                Exit Sub
            End If
            Kill (sPath & sFileName)
        End If
    File exists function;
    Code:
    Function FileExists(ByVal strFile As String, Optional bFindFolders As Boolean) As Boolean
        'Purpose:   Return True if the file exists, even if it is hidden.
        'Arguments: strFile: File name to look for. Current directory searched if no path included.
        '           bFindFolders. If strFile is a folder, FileExists() returns False unless this argument is True.
        'Note:      Does not look inside subdirectories for the file.
        'Author:    Allen Browne. http://allenbrowne.com June, 2006.
        Dim lngAttributes    As Long
    
    
        'Include read-only files, hidden files, system files.
        lngAttributes = (vbReadOnly Or vbHidden Or vbSystem)
    
    
        If bFindFolders Then
            lngAttributes = (lngAttributes Or vbDirectory)        'Include folders as well.
        Else
            'Strip any trailing slash, so Dir does not look inside the folder.
            Do While Right$(strFile, 1) = "\"
                strFile = Left$(strFile, Len(strFile) - 1)
            Loop
        End If
    
    
        'If Dir() returns something, the file exists.
        On Error Resume Next
        FileExists = (Len(Dir(strFile, lngAttributes)) > 0)
    
    
    End Function
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  4. #4
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    2,487
    Here is a similar one without the prompt:
    Code:
    Public Sub EXPORT_TABLES()
    
    
        'Declare variables
        Dim db As DAO.Database
        Dim tdf As DAO.TableDef
        Dim n As Integer
     
        'For loop for exporting tables
        Set db = CurrentDb
        For Each tdf In db.TableDefs
            If Left(tdf.Name, 4) = "tbl_" Then
                On Error Resume Next ' brute force approach to continue if file not there when attempting to kill it
                outputFileName = CurrentProject.Path & "\ExportData_" & Format(Date, "yyyyMMdd") & ".xlsx"
                Kill outputFileName
                DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, tdf.Name, outputFileName, True
                
                n = n + 1
            End If
        Next
        
        If n = 0 Then
            'Do nothing
        Else
            MsgBox n & " tables have been exported.", vbInformation, "Export Tables"
        End If
    
    
    End Sub
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  5. #5
    skydivetom is offline Expert
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    779
    Minty -- thank you for the update on creating the output in XLSX format... it works great!

    Vlad -- for some reason, the modified version now only outputs a one (1) tab -- the last one of the tables starting with "tbl_" -- into the spreadsheet. Can the code be tweaked so that it kills the XLSX but outputs all tabs vs. just the last one?

    Cheers,
    Tom

  6. #6
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    2,487
    Sorry Tom, just move the file name operations outside of the loop:
    Code:
    Public Sub EXPORT_TABLES()
    
    
    
    
        'Declare variables
        Dim db As DAO.Database
        Dim tdf As DAO.TableDef
        Dim n As Integer
     
        'For loop for exporting tables
        Set db = CurrentDb
    
    On Error Resume Next ' brute force approach to continue if file not there when attempting to kill it
    outputFileName = CurrentProject.Path & "\ExportData_" & Format(Date, "yyyyMMdd") & ".xlsx"
    Kill outputFileName
    
    
    
        For Each tdf In db.TableDefs
            If Left(tdf.Name, 4) = "tbl_" Then            
                DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, tdf.Name, outputFileName, True
                
                n = n + 1
            End If
        Next
        
        If n = 0 Then
            'Do nothing
        Else
            MsgBox n & " tables have been exported.", vbInformation, "Export Tables"
        End If
    
    
    
    
    End Sub
    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  7. #7
    skydivetom is offline Expert
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    779
    Vlad -- that did the "trick"!!! It works as envisioned now. Just beautiful!!!

    Thousand thanks!
    Tom

  8. #8
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    2,487
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  9. #9
    skydivetom is offline Expert
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    779
    Vlad -- I opened a new thread related to the current project. I'd welcome if you have any thoughts on that question/process.

    https://www.accessforums.net/showthr...492#post478492

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 24
    Last Post: 06-25-2021, 06:08 AM
  2. Replies: 7
    Last Post: 03-19-2021, 05:54 PM
  3. Replies: 21
    Last Post: 05-18-2020, 09:42 PM
  4. Replies: 1
    Last Post: 02-03-2019, 09:16 AM
  5. Replies: 0
    Last Post: 03-11-2012, 09:19 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums - Senior Forums