Results 1 to 4 of 4
  1. #1
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038

    Access Export Routine (into Excel file)

    Experts:



    As part of a "data cleanup" routine, I export an Access table to an Excel spreadsheet.

    The structure of the Excel does NOT change -- the only thing which does change is the # of records to be exported. The routine is shown below:


    Code:
           'Drop Data Cleanup Table
           MySql_DropRecords = "DELETE 002_tblRawData_DataCleanup.* FROM 002_tblRawData_DataCleanup;"
           CurrentDb.Execute MySql_DropRecords
           
           'Append records to Data Cleanup Table
           DoCmd.OpenQuery "qryDataCleanup_01_IncidentTime_B"
           DoCmd.OpenQuery "qryDataCleanup_02_PLADName_B"
           DoCmd.OpenQuery "qryDataCleanup_03_Echelon_B"
           DoCmd.OpenQuery "qryDataCleanup_04_MajorCommandEch2_B"
           DoCmd.OpenQuery "qryDataCleanup_05_TYCOMEch3_B"
           DoCmd.OpenQuery "qryDataCleanup_06_CONUS_B"
           DoCmd.OpenQuery "qryDataCleanup_07_City_B"
           DoCmd.OpenQuery "qryDataCleanup_08_State_B"
           DoCmd.OpenQuery "qryDataCleanup_09_Country_B"
           DoCmd.OpenQuery "qryDataCleanup_10_LocationDesc_B"
                 
          
           'Enable warnings
           DoCmd.SetWarnings (True)
                                  
          
           On Error GoTo ErrorHandler
              
           Dim MYPATH As String
              
           'Network file path
           MYPATH = "\\...\...\DataCleanup.xlsx"
                  
           DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "002_tblRawData_DataCleanup", MYPATH, True, "_002_tblRawData_DataCleanup"
                                      
    ErrorHandler:
                If Err.Number = 3278 Then
                    'Do nothing
                Else
                    MsgBox (DCount("GroupID", "002_tblRawData_DataCleanup") & " records were successfully exported in preparation for data cleanup!"), vbInformation, "Step 2 - Clean Data"
                End If

    Overall, this process works fine. However, I've seen that I frequently have to replace the Excel file with **BACKUP EXCEL** file (which is an exact copy). Why? If my first cleanup (in Table "002_tblRawData_DataCleanup") contains, e.g., 20 records, then 20 records are exported into the file. So far so good... now, for cleaning up another data category, I may have only 5 records to be exported. Once I run the export routine, the Excel file still shows the previous 20 records vs. the newer 5 records though.

    Once I replace the Excel file with the EXCEL BACKUP file and re-run the routine, I then see 5 records.

    My question: Why do I need to keep on replacing the EXCEL file with its backup version in order for the routine to export the correct # of records? Is there anything wrong w/ my VBA script?

    Thank you,
    EEH

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    the export overwrites what is there...
    1. export 20
    2. export 5, you get 5 new ones and the rest are old.
    so,
    delete the file
    THEN export

    Killfile sMyFile
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "002_tblRawData_DataCleanup", sMyFile, True, "_002_tblRawData_DataCleanup"

    put this in a module so all can use it:
    Code:
    Public Sub KillFile(ByVal pvFile)
    Dim FSO
    On Error Resume Next
    Set FSO = CreateObject("Scripting.FileSystemObject")
    'FileReadOnly pvFile, False
    FSO.DeleteFile pvFile
    Set FSO = Nothing
    End Sub

  3. #3
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    ranman256:

    Thank you for the feedback... as always, I truly appreciate it.

    Allow me to clarify, the existing process works sometimes while other times it doesn't. That is, if I run it with "20", I get "20... then I run it with "5" and I "get 5". But then, if I re-run it with "20" again, it may not update and I still only see 5 records (vs. 20).

    I will try your recommendation tomorrow (at work). In general, I don't mind deleting/recreating the file. However, when recreating the file each time, I lose the existing format (somewhat critical/important) in the Excel file. Thus, I would have to open a backup file and apply the "Format Painter".

    My question: Is there another way to ensure the re-run will delete all rows beyond the header row (in Excel) and then add the new records? If not, how could I ensure that the "Format Painter" is automatically re-applied to the Excel once I ran the Access VBA routine?

    Thanks,
    EEH

  4. #4
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    My question: Is there another way to ensure the re-run will delete all rows beyond the header row (in Excel) and then add the new records? If not, how could I ensure that the "Format Painter" is automatically re-applied to the Excel once I ran the Access VBA routine?
    1) You might be able to use a template workbook. These allow you to enter data, but FORCE you to do a "Save As" to change the workbook name. You can never overwrite the template.
    2) You could use Automation to format the workbook from Access.
    3) Have you tried refreshing the workbook (pressing the F5 key)?


    It looks like you didn't post the complete code.
    However, I have a few suggestions. Try these in a COPY of your dB first!!!

    Code:
    CurrentDb.Execute MySql_DropRecords
    I would use
    Code:
    CurrentDb.Execute MySql_DropRecords, dbFailOnError
    For the queries. Am I right that they are Append queries?
    If Yes, then consider using "CurrentDb.Execute" instead of "DoCmd.OpenQuery".
    If/when you use "CurrentDb.Execute", you don't need the set warnings commands. When you use "CurrentDb.Execute", be sure to also use ", dbFailOnError" . ("CurrentDb.Execute" is also faster)

    So your code would look like
    Code:
           'Drop Data Cleanup Table
           MySql_DropRecords = "DELETE * FROM 002_tblRawData_DataCleanup;"   '<<-- note changes
           CurrentDb.Execute MySql_DropRecords, dbFailOnError
           
           'Append records to Data Cleanup Table
           CurrentDb.Execute "qryDataCleanup_01_IncidentTime_B", dbFailOnError
           CurrentDb.Execute "qryDataCleanup_02_PLADName_B", dbFailOnError
           CurrentDb.Execute "qryDataCleanup_03_Echelon_B", dbFailOnError
           CurrentDb.Execute "qryDataCleanup_04_MajorCommandEch2_B", dbFailOnError
           CurrentDb.Execute "qryDataCleanup_05_TYCOMEch3_B", dbFailOnError
           CurrentDb.Execute "qryDataCleanup_06_CONUS_B", dbFailOnError
           CurrentDb.Execute "qryDataCleanup_07_City_B", dbFailOnError
           CurrentDb.Execute "qryDataCleanup_08_State_B", dbFailOnError
           CurrentDb.Execute "qryDataCleanup_09_Country_B", dbFailOnError
           CurrentDb.Execute "qryDataCleanup_10_LocationDesc_B", dbFailOnError
                 
          
           'Enable warnings
    '       DoCmd.SetWarnings (True)   '<<-- also comment out/delete the previous DoCmd.SetWarnings (False)


    Two more things:
    It looks like you are using A2010. In the "TransferSpreadsheet" command, you have the SpreadsheetType as "acSpreadsheetTypeExcel12".
    "acSpreadsheetTypeExcel12" is the A2007 format. For A2010, you should be using "acSpreadsheetTypeExcel12Xml". This is the format for A2010 - A2016.




    Finally, you are Exporting so you shouldn't have anything for the "Range" parameter.

    From HELP:
    A string expression that's a valid range of cells or the name of a range in the spreadsheet. This argument applies only to importing. Leave this argument blank to import the entire spreadsheet. When you export to a spreadsheet, you must leave this argument blank. If you enter a range, the export will fail.

    Having said that, it seem that sometimes the export works and sometimes not when the RANGE parameter is used when exporting. BE AWARE.

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

Similar Threads

  1. Replies: 5
    Last Post: 02-14-2018, 04:21 AM
  2. Import VBA routine does not recognize the Excel File
    By jyellis in forum Import/Export Data
    Replies: 4
    Last Post: 09-08-2017, 01:20 PM
  3. Replies: 1
    Last Post: 01-24-2013, 11:47 PM
  4. Replies: 2
    Last Post: 08-05-2012, 06:32 PM
  5. Excel file that I export from Access is extremely large
    By Ronald Mcdonald in forum Access
    Replies: 1
    Last Post: 05-25-2012, 03:32 PM

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