Results 1 to 11 of 11
  1. #1
    slxia1 is offline Novice
    Windows Vista Access 2013
    Join Date
    Apr 2014
    Posts
    5

    Import Excel files into Access - batch import - first sheet only

    Situation:
    I have a bunch of files in a folder
    01022014.xsl
    01032014.xsl
    01042014.xsl
    all with identical formatting.

    I would like to bulk import the first spreadsheet in the workbook to access.

    I have researched into DoCmd.TransferSpreadsheet Method, but I am struggling as I am only a newbie for excel macros and this is a little beyond me.

    Please don't laugh at my code.
    Code:
    Sub import()
    Dim strFile As String
    strFile = InputBox("Workbookname.xls")
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9,
    "sheet1","C:\Test" & strFile & ".xls", False, "WorksheetName!A1:C30"
    End Sub
    I really welcome both Transferspreadsheet based answers, but also welcome custom built macros so I can be more flexible with it.

  2. #2
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    I would approach this with VBA, particularly if your incoming files follow a specific naming convention (though I'd suggest naming them YYYYMMDD.xsl so it's easier to see them in chronological order). if you are harvesting files from a known location it's not as much of an issue unless other excel files *can* be in that folder in that case you'd probably want to do a little error checking for unexpected files. What is your practice after processing a file? do you copy them to a different location? leave them in the same location? are you creating a log of which files have been processed, can already processed files be updated and therefore need to be re-imported?

    So to cycle through records in a specific folder you can use filesystemobject commands found here:

    http://blogs.technet.com/b/heyscript...ubfolders.aspx

    I would suggest that you use filesystemobject commands to move or copy/delete the 'processed' files to a new location so that only unprocessed files are in your source directory.

    Using the filesystemobject commands you can get the file name and just substitute that into your docmd.transferspreadsheet code instead of having a 'static' file name.

  3. #3
    slxia1 is offline Novice
    Windows Vista Access 2013
    Join Date
    Apr 2014
    Posts
    5
    Hi rpeare,

    Thanks for getting back to me.

    Take "April" as the folder and I have 20140401, after it is processed, it remains there. The second day, I create 20140402 and so forth.
    At the end of the month, I want to bulk import all into ACCESS.

    Below is where I am up, it is able to import (VERY HAPPY), but there are 2 issues with it at the moment.

    1. When importing, it does NOT recognise the first row has headings. Hence returning the result, "cannot find F1 in Table1".
    2. I am not sure how to include cycling the files. Reading that link you gave me, I understand a little bit, but unsure how to incorperate the code into the below.

    Code:
     Sub import()
    Dim strFile As String
    strFile = InputBox("Workbookname.xls")
    DoCmd.TransferSpreadsheet 0, acSpreadsheetTypeExcel9, "FUM", "F:\April\" & strFile & ".xlsx", False, "Sheet1!"
    End Sub
    Code:
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    objStartFolder = "C:\Scripts"
    
    Set objFolder = objFSO.GetFolder(objStartFolder)
    Wscript.Echo objFolder.Path
    Set colFiles = objFolder.Files
    For Each objFile in colFiles
        Wscript.Echo objFile.Name
    Next
    Wscript.Echo
    
    ShowSubfolders objFSO.GetFolder(objStartFolder)
    
    Sub ShowSubFolders(Folder)
        For Each Subfolder in Folder.SubFolders
            Wscript.Echo Subfolder.Path
            Set objFolder = objFSO.GetFolder(Subfolder.Path)
            Set colFiles = objFolder.Files
            For Each objFile in colFiles
                Wscript.Echo objFile.Name
            Next
            Wscript.Echo
            ShowSubFolders Subfolder
        Next
    End Sub

  4. #4
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    [QUOTE=slxia1;221396]Hi rpeare,

    Thanks for getting back to me.

    Take "April" as the folder and I have 20140401, after it is processed, it remains there. The second day, I create 20140402 and so forth.
    At the end of the month, I want to bulk import all into ACCESS.

    Below is where I am up, it is able to import (VERY HAPPY), but there are 2 issues with it at the moment.

    1. When importing, it does NOT recognise the first row has headings. Hence returning the result, "cannot find F1 in Table1".
    2. I am not sure how to include cycling the files. Reading that link you gave me, I understand a little bit, but unsure how to incorperate the code into the below.

    1. Where you have FALSE in your transferspreadsheet command it should be TRUE if you want to ignore the first row, FALSE if you want to include the first row. From what you're saying your data starts on the second row so you want that value to be TRUE
    Code:
     Sub import()
    Dim strFile As String
    strFile = InputBox("Workbookname.xls")
    DoCmd.TransferSpreadsheet 0, acSpreadsheetTypeExcel9, "FUM", "F:\April\" & strFile & ".xlsx", False, "Sheet1!"
    End Sub
    Let's not go into subfolders for this example This code will cycle through c:\scripts and get your file names. If it is correctly doing that (in other words it's correctly listing all the files in the folder)

    Code:
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    objStartFolder = "C:\Scripts"
    
    Set objFolder = objFSO.GetFolder(objStartFolder)
    Wscript.Echo objFolder.Path
    Set colFiles = objFolder.Files
    For Each objFile in colFiles
        sFileName = objFile.Name
        if instr(sfilename, ".xls") > 0 'this is a clunky way of saying if the file is an excel file
            DoCmd.TransferSpreadsheet 0, acSpreadsheetTypeExcel9, "FUM", "c:\scripts\" & sFileName, True, "Sheet1!"
        end if
    Next
    Wscript.Echo
    Be advised that if the root spreadsheet(s) get updated you will have to be a little cannier than bulk importing data if you will be performing the same import for the same file more than once with the possibility that the data has changed over time.

  5. #5
    slxia1 is offline Novice
    Windows Vista Access 2013
    Join Date
    Apr 2014
    Posts
    5

    Still not working

    I have updated but still getting a few errors.
    Question 1
    If you don;t mind can you narrate the lines 2-7?

    Question 2
    More importantly, in ALL my Excel files, Row1 says CASH FLOW, Row2 are the headings, from Row3 onwards are the actual data I want to import. How do I account for this?

    Question 3
    My tab name is dynamic! It always the same as file name, i.e. Apple 010214, Apple 020214



    Code:
    Sub importing()
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    objStartFolder = "C:\Script"
    Set objFolder = objFSO.GetFolder(objStartFolder)
    'wscript.echo objFolder.Path 'what is this for?
    Set colFiles = objFolder.Files
    For Each objFile In colFiles
        sFileName = objFile.Name
        If InStr(sFileName, ".xlsx") > 0 Then 
            DoCmd.TransferSpreadsheet 0, acSpreadsheetTypeExcel9, "FUM", "C:\Script" & sFileName, True, "Sheet1!"
            End If
    Next
    'wscript.echo 'what is this for?
    End Sub
    Last edited by slxia1; 05-12-2014 at 01:14 AM.

  6. #6
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    This is a link to the transferspreadsheet method:

    http://msdn.microsoft.com/en-us/libr...ice.15%29.aspx

    The last in your current statement you are saying that sheet1 is the name of the sheet you want to import which is incorrect based on your post. If the name of the sheet you want to import is the same as the name of the file (without the .xls or .xlsx on the end) you'll have to truncate the file name to get the sheet name

    So let's say your file name is Apple 010214.xlsx (if there's no underscore or other placeholder character I don't know how well this will work) you can get the place of the . with

    instr(sfilename, ".")

    then just take all characters to the right of that with

    right(sfilename, len(sfilename) - instr(sfilename, "."))

    just make sure to debug.print the 'sheet name' before you try to do anything with it

    Then you would have to define a range you wanted to import. If these files have a static number of rows you could set up your 'range' variable to be something like

    sRange = right(sfilename, len(sfilename) - instr(sfilename, ".")) & "!A1:Z26"

    then substitute the srange where you currently have "sheet1!"

    if you have a variable number of rows in each file you there are a number of ways you could 'count' the number of rows in it and define your range dynamically.

    Here's a link to an example of how to do that:

    http://stackoverflow.com/questions/1...2007-worksheet

  7. #7
    slxia1 is offline Novice
    Windows Vista Access 2013
    Join Date
    Apr 2014
    Posts
    5
    I will give the above a try tomorrow.
    For CSV there is only one tab, is there a way to import regardless what the tab name is? i.e. is there a generic filename?

  8. #8
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    if you put in a range without a worksheet name it assumes the first worksheet is what you're referencing. If there's only one worksheet in the file and you put in a range (i.e. "A1:Z26") it should assume the first sheet without you having to provide it.

  9. #9
    breakingme10 is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Jan 2014
    Location
    Oklahoma
    Posts
    153
    This thread was very helpful for me! I have a ton of spreadsheets for my storage unit tenants and I think now I will be able to bulk import them into access without having to redo everything! My only issue would be that my spreadsheets were designed for "print" so they have logo, header, etc and formatted to "look good" so I may need to go through each (150+) excel spreadsheets and reformat them but so far that wouldn't be such a pain.

  10. #10
    slxia1 is offline Novice
    Windows Vista Access 2013
    Join Date
    Apr 2014
    Posts
    5
    Finally have time to look at this again.
    I have another two issues.
    Firstly the spreadsheets are password protected, all with the same password, can I build that into the macro?
    Second, the book I want to import is always the 3rd tab, can I select that in the macro?

  11. #11
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    maybe try this:

    http://accessblog.net/2005/04/how-to...ted-excel.html

    and it's not a macro, it's VBA, if you're not comfortable with that I'm not sure what to suggest.

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

Similar Threads

  1. Import excel sheet data into Ms-access using VBA macros
    By gokul1242 in forum Import/Export Data
    Replies: 2
    Last Post: 10-02-2012, 04:39 AM
  2. Replies: 2
    Last Post: 08-14-2012, 04:24 AM
  3. Replies: 1
    Last Post: 06-27-2012, 07:39 AM
  4. import excel files into access automatically
    By jstei012 in forum Import/Export Data
    Replies: 1
    Last Post: 12-19-2011, 04:12 PM
  5. Replies: 3
    Last Post: 10-06-2011, 06:53 AM

Tags for this Thread

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