Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Mary87 is offline Novice
    Windows 8 Access 2007
    Join Date
    Jan 2015
    Posts
    13

    Importing multiple files from excel into a table and also inserting the file name into a column

    Hi,

    Please can you assist. I am trying to import multiple spreadsheets from a folder into an access database. This is working - however when trying to update the table with the file name, I receive an error.

    Please see below, the code I am using...

    Function DoImport()
    Dim strPathFile As String, strFile As String, strPath As String
    Dim strTable As String


    Dim blnHasFieldNames As Boolean
    ' New
    Dim SQL As String
    ' Change this next line to True if the first row in EXCEL worksheet
    ' has field names
    blnHasFieldNames = True
    ' Replace C:\Documents\ with the real path to the folder that
    ' contains the EXCEL files
    strPath = "C:\QlikView Reports\Importing with file name"
    ' Replace tablename with the real name of the table into which
    ' the data are to be imported
    strTable = "Import"


    strFile = Dir(strPath & "*.xls")
    Do While Len(strFile) > 0
    strPathFile = strPath & strFile
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
    strTable, strPathFile, blnHasFieldNames

    'new
    DoCmd.RunSQL
    "UPDATE [Import] SET Import.[File Name]= & strFile & WHERE Import.[File Name] IS NULL OR Import.[File Name]='';""

    ' Uncomment out the next code step if you want to delete the
    ' EXCEL file after it's been imported
    ' Kill strPathFile



    strFile = Dir()
    Loop
    End Function




    Thank you v much

  2. #2
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Try this:
    Code:
    DoCmd.RunSQL
    "UPDATE [Import] SET Import.[File Name]= '" & strFile & "' WHERE Import.[File Name] IS NULL OR Import.[File Name]='';"

  3. #3
    Mary87 is offline Novice
    Windows 8 Access 2007
    Join Date
    Jan 2015
    Posts
    13
    Thank you for the quick reply.

    I have updated the script, however am now receiving the following error:
    "Compile error:

    Expected: line number or label or statement or end of statement"

    Attached Thumbnails Attached Thumbnails untitled.PNG  

  4. #4
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    You don't have a carriage return between your DoCmd.RunSQL line and the actual code, do you?
    I just copied what you had, thinking it was just a wrapping situation. If that is not the case, it should look like this:
    Code:
    DoCmd.RunSQL "UPDATE [Import] SET Import.[File Name]= '" & strFile & "' WHERE Import.[File Name] IS NULL OR Import.[File Name]='';"

  5. #5
    Mary87 is offline Novice
    Windows 8 Access 2007
    Join Date
    Jan 2015
    Posts
    13
    Thank you JoeM for your quick response.
    I have updated the code, however it does not run.... Did it run for you?

  6. #6
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    I would have to try to recreate your whole scenario in order to see if it works for your situation.
    Note that you have the ability to attach your database here for analysis. Then we could actually work on exactly what you have.
    Just be sure to remove all sensitive information before doing so.

  7. #7
    Mary87 is offline Novice
    Windows 8 Access 2007
    Join Date
    Jan 2015
    Posts
    13
    Thank you JoeM.

    I have attached.
    Attached Files Attached Files

  8. #8
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    OK. I will try to take a look at it tonight (due to corporate security rules, I cannot download files off of the internet from my current location).

    Does the database you uploaded already contained imported records that need this field to be updated?

  9. #9
    Mary87 is offline Novice
    Windows 8 Access 2007
    Join Date
    Jan 2015
    Posts
    13
    Thank you very much Joe.
    No the database contains no records ...

  10. #10
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Could you include a dummy file of a few records to import so we can test the functionality completely?
    Also, in case it is not entirely obvious how you run this process, can you tell us how you run it (is there a button on Form, a Macro you click, etc).

  11. #11
    Mary87 is offline Novice
    Windows 8 Access 2007
    Join Date
    Jan 2015
    Posts
    13
    I have attached some test data.
    This is currently saved in the location: C:\QlikView Reports\Importing with file name
    I load up Access 2007, click on the "Create tab" => Module (under Macros & Code) => and play the "Import with Name" module.
    However it does not run :-(.
    Thank you v much for your help
    Attached Files Attached Files

  12. #12
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    The issue is that you do not have a field name "File Name" in your table. An Update Query can only update values of existing fields, it cannot create fields that do not already exist.
    So you want to add a "File Name" field to your table. Note that you do not have to have a value to import for every field in your table.

  13. #13
    Mary87 is offline Novice
    Windows 8 Access 2007
    Join Date
    Jan 2015
    Posts
    13

    Unhappy

    Thank you for checking Joe, I have inserted another column to the table, however can not run the module.
    Please see attached screenshot- I highlight the line "Public Function DoImprot ()".
    Am I missing a step?
    Click image for larger version. 

Name:	VB Code.jpg 
Views:	12 
Size:	121.0 KB 
ID:	19461

  14. #14
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    What kind of error message does it give you?

    I think I had to do one other thing, and that was place another backslash at the end of your strPath declaration.
    Code:
    strPath = "C:\QlickView Reports\Importing with file name\"
    I didn't bring it up before because in your original post, it sounded like you were getting the import to work, just not the field update. So I thought maybe you just changed the file path for my example. But using the database and file you gave me, I was able to get it to do what you want with those two changes.

    Note that I added the new field to the very end of the table.

  15. #15
    Mary87 is offline Novice
    Windows 8 Access 2007
    Join Date
    Jan 2015
    Posts
    13
    The macro runs after adding the extra backslash.
    However I am receiving an error
    "Run-time error '2391';
    Field 'creation_ts' doesnt exist in destination table 'Import'.
    I dont know why this is happening, because I dont have a column 'creation_ts'

    Thank you v v much for your time and assistance

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Field F9 error when importing Excel file to existing table
    By dougdrex in forum Import/Export Data
    Replies: 2
    Last Post: 12-26-2014, 01:38 PM
  2. Importing large excel file into multiple access files
    By Ghost in forum Import/Export Data
    Replies: 10
    Last Post: 11-05-2013, 11:19 AM
  3. Replies: 10
    Last Post: 12-28-2012, 02:06 PM
  4. Replies: 7
    Last Post: 12-09-2012, 06:20 PM
  5. Importing excel file to append a table
    By vickan240sx in forum Access
    Replies: 1
    Last Post: 06-27-2012, 02:46 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