Importing Many Excel Files in Folder in Access Table Need A Date in Cell C3 associated with each File to be loaded into Field with each record. RRS feed

  • Question

  • I am not sure this is possible. I have researched this and could not find a similar situation that had been addressed with any other forums. I have files that I am importing and Looping through from a single folder using a DoCmd.transferspreadsheet ac Import "My Data Has Headers" True, which works great, this loads all the data into the necessary access table.  However there is a Week Ending Date in cell C3 of the files on the sheets that I am importing and the data that is in cells B5:U1500 I need to have this week ending date added to each record of the associated file that is being imported.  I am not sure this can be done and if it can be, I am sure the code that is needed to accomplish this will be fairly complex and beyond my level of experience.


    Wednesday, May 23, 2018 1:28 PM

All replies

  • Hi,

    Are you saying your TransferSpreadsheet routine does not import the entire spreadsheet so you want to add those two information into the table?

    Wednesday, May 23, 2018 2:50 PM
  • Probably can't be done using TransferSpreadsheet, at least not the date part, but can be done via SQL. Is the "week ending date" in just one Worksheet of a Workbook and will all rows imported from the Worksheets in Workbook have this same date?

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Wednesday, May 23, 2018 4:07 PM
  • Hey Paul,

    The Weekending Date would just be in the one cell C3 of the one worksheet in each workbook.  There will be about a Total of 50-100 workbooks.  Some of the workbooks will have the same date in cell C3, but for the most part they will have different Week Ending Dates. The desire is to have all the Rows that are imported from their respective Worksheet of a Workbook would have this Week Ending Date that is in cell C3 imported or added to each row in the table.  Let me know If I need to clarify better.



    Wednesday, May 23, 2018 5:13 PM
  • No it imports all the data like it is supposed to. However recently I have needed to better add visibility to the week ending date that this data was produced. The only way to get a week ending date to add this functionality report is where the Week Ending Date is located in cell C3 on each sheet of each workbook. Which I know fundamentally this is not the best way to get that data, however this issue wasn't discovered until after the fact. So I am trying to find a code or some kind of method that would capture that week ending date on the 50 to 100 files and be added into a field of the respective rows of data to differentiate the week endings. Let me know if I need to clarify this better.


    Wednesday, May 23, 2018 5:17 PM
  • Hi,

    Okay, I think that makes more sense. Assuming you have a field in the table for the weekending date and it is empty after the import, then here's how I usually handle a situation like this:

    1. Use Excel automation to capture the data from the Excel cell

    2. Store this information in a variable

    3. Import the file into the Access table

    4. Run an UPDATE query to populate the WeekEnding field using the data from the variable

    5. Process the rest of the Excel spreadsheets in the same way as the above

    Hope it helps...

    Wednesday, May 23, 2018 6:09 PM
  • That sounds like it could potentially work. I have the code that I use below. It currently loops through all the files in that folder and uses the DoCMD transfer spreadsheet.  So the files never open.  If I run automation from Microsoft Access VBA on the Excel Files won't I need to open each individual file and adapt my code below from the DocCmd transfer spreadsheet code? I haven't previously stored anything as a variable which I could probably figure it out, but then I am not sure of how to recall that variable to be used in a specific update qry.

    Dim filepath As String
    Dim sheetname As String
    Dim dbs As DAO.Database
    Set dbs = CurrentDb

    'Code for Deleting Tables

    DoCmd.SetWarnings False

    DoCmd.RunSQL ("DELETE * FROM TBL#####s")

    Dim fileName As String

    'Loop through the folder & import each file
    fileName = Dir(("C:\Users\#####\Desktop\RCCA Files Database\RCCA Responses\*.xls"))
    While fileName <> ""
        DoCmd.TransferSpreadsheet acImport, , "TBLRCCAResponses", ("C:\Users\#####\Desktop\#### Files Database\#### Responses\") & fileName, True, ("####Form!B5:U1500")
       'check whether there are any more files to import
        fileName = Dir()

    DoCmd.RunSQL ("DELETE * FROM TBLRCCAResponses WHERE Responses.[Area Manager] IS NULL")
    dbs.Execute "UpdateQry#####", dbFailOnError
    dbs.Execute "UpdateQuery####", dbFailOnError
    dbs.Execute "UpdateQryfor#####", dbFailOnError
    dbs.Execute "UpdateQry#####", dbFailOnError
    dbs.Execute "UpdateQry#####", dbFailOnError
    DoCmd.SetWarnings True

        Exit Sub
    MsgBox Error$
        Resume Import_###_Responses_Click_Exit
    DoCmd.SetWarnings True
    End Sub

    Here is something I found on another Forum that has a similar concept that I was wandering if could be adapted for this project.

    Sub ImportExcelFiles()
                                      Dim strFileName As String, strFolder As String, xDate As Date, strTable As String
                                       strFolder = "C:\Users\Christian Ysebie\Desktop\April Ell2\lunch\"  ' CHANGE THIS WITH ACTUAL FOLDER PATH

                                      strFileName = Dir(strFolder & "*.xlsx")
                                      xDate = xDate = Left(Right(strFileName, 15), 10)
                                      strTable = Replace(Replace(Left(strFileName, Len(strFileName) - 5), " ", "_"), "-", "_")
                                       While strFileName <> ""
                                           DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel12Xml, strTable, strFolder & strFileName, True, "Sheet1!"
                                       'add date [ImpDate] column to table
                                            CurrentDb.Execute "ALTER TABLE  " & strTable & " ADD COLUMN [ImpDate] Date);", dbFailOnError
                                            CurrentDb.Execute "Append " & strTable & " set ImpDate = #" & xDate & "#"
                                          strFileName = Dir()
                                      End Sub

    Wednesday, May 23, 2018 8:16 PM
  • Hi,

    No, you won't necessarily need to open each Excel file to read its contents. When you automate an Excel file, it is loaded into memory. Whether you want it visible or not, it is up to you.

    Here's the logic, as I imagine it, for your situation:

    1. Start loop to read all Excel files in a folder

    2. Fetch the filename

    3. Use Excel Automation to "open" the file and read the content of cell C3 and store it in a variable

    4. Close the Excel file

    5. Import the file using TransferSpreadsheet

    6. Execute an UPDATE query to populate the empty Weekending field by using the data in the variable

    7. Fetch the next filename and repeat steps 3 to 7

    You could potentially create a function to Automate Excel and return the Weekending date, which would replace steps 3 and 4.

    Hope it helps...

    Wednesday, May 23, 2018 9:10 PM