none
Import data into access form excel RRS feed

  • Question

  • Hi All,

    I am trying to import data into access from excel using VBA. I want access macro to search for excel files from a folder and if any excel file is found than import using below steps

    • import from row 6 till last row and till column Z
    • row 5 are headers
    • Table name shall be the UNIQUE order no in cell A6 for e.g SC234

    i have tried using below code which works but i have to mention what range to be imported, provide exact excel file full name and table name.

    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, _
        "TEST", "C:request form.xlsx", True, "A5:Z2000"
    

    Thanks,

    Zav


    • Edited by zaveri cc Friday, August 5, 2016 4:13 PM
    Friday, August 5, 2016 4:11 PM

Answers

  • Hi zaveri cc,

    First as you had mentioned you want to find excel files in folder. so for that visit the link below where you will find the example of that.

    Files in a Directory

    then you want to copy the data from the sheet.

    but you have header in your file. so here you can try to remove the headers programmatically or you can create a temporary sheet without headers and you can then copy the used range of the sheet.

    to create table you can use code below.

    and pass the value of cell A6 for Table Name.

    Sub CreateTableX1() 
     
        Dim dbs As Database 
     
        ' Modify this line to include the path to Northwind 
        ' on your computer. 
        Set dbs = OpenDatabase("Northwind.mdb") 
     
        ' Create a table with two text fields. 
        dbs.Execute "CREATE TABLE ThisTable " _ 
            & "(FirstName CHAR, LastName CHAR);" 
     
        dbs.Close 
     
    End Sub 
    
    

    Disclaimer: This response contains a reference to a third party World Wide Web site. Microsoft is providing this information as a convenience to you. Microsoft does not control these sites and has not tested any software or information found on these sites; therefore, Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or information found there. There are inherent dangers in the use of any software found on the Internet, and Microsoft cautions you to make sure that you completely understand the risk before retrieving any software from the Internet.

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, August 8, 2016 5:29 AM
    Moderator

All replies

  • Hi Zav. Are you saying each file might not have the same number of rows each time?
    Friday, August 5, 2016 4:52 PM
  • Yes.
    Friday, August 5, 2016 6:21 PM
  • Okay, just trying to see if we can minimize the code. Can you manually assign a Range to the spreadsheets? If not, then we'll have to have more code than what you already have.
    Friday, August 5, 2016 6:59 PM
  • Hi zaveri cc,

    First as you had mentioned you want to find excel files in folder. so for that visit the link below where you will find the example of that.

    Files in a Directory

    then you want to copy the data from the sheet.

    but you have header in your file. so here you can try to remove the headers programmatically or you can create a temporary sheet without headers and you can then copy the used range of the sheet.

    to create table you can use code below.

    and pass the value of cell A6 for Table Name.

    Sub CreateTableX1() 
     
        Dim dbs As Database 
     
        ' Modify this line to include the path to Northwind 
        ' on your computer. 
        Set dbs = OpenDatabase("Northwind.mdb") 
     
        ' Create a table with two text fields. 
        dbs.Execute "CREATE TABLE ThisTable " _ 
            & "(FirstName CHAR, LastName CHAR);" 
     
        dbs.Close 
     
    End Sub 
    
    

    Disclaimer: This response contains a reference to a third party World Wide Web site. Microsoft is providing this information as a convenience to you. Microsoft does not control these sites and has not tested any software or information found on these sites; therefore, Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or information found there. There are inherent dangers in the use of any software found on the Internet, and Microsoft cautions you to make sure that you completely understand the risk before retrieving any software from the Internet.

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, August 8, 2016 5:29 AM
    Moderator