+ Reply to Thread
Results 1 to 12 of 12

Upload data from excel file to access table using vba

  1. #1
    Forum Contributor
    Join Date
    02-20-2009
    Location
    bristol
    MS-Off Ver
    Excel 2003
    Posts
    882

    Upload data from excel file to access table using vba

    Hi guys

    I have designed a userform where we can browse a file when the user clicks on Browse button and that file name appears in the textbox as below:
    Please Login or Register  to view this content.
    Now there is one upload button also and when the user click on this button then it should upload data from excel file to Access table. Access table has all the fields that are present in Excel file that we browse using the above code. Can anyone please help me to write code for this please. See attached an example of excel file that the user will browse evrytime .

    Thanks
    Aman
    Attached Files Attached Files

  2. #2
    Forum Contributor
    Join Date
    02-20-2009
    Location
    bristol
    MS-Off Ver
    Excel 2003
    Posts
    882

    Re: Upload data from excel file to access table using vba

    Can anyone please figure out my problem? Many Thanks

  3. #3
    Forum Contributor
    Join Date
    02-20-2009
    Location
    bristol
    MS-Off Ver
    Excel 2003
    Posts
    882

    Re: Upload data from excel file to access table using vba

    Hi guys

    Can anyone please point me in the right direction? How can I upload a excelsheet data in Access table using excel/vba.

    Thanks

  4. #4
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,519

    Re: Upload data from excel file to access table using vba

    This will give you some idea

    Please Login or Register  to view this content.
    Last edited by mike7952; 08-23-2012 at 05:18 AM.
    Thanks,
    Mike

    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved.

  5. #5
    Forum Contributor
    Join Date
    02-20-2009
    Location
    bristol
    MS-Off Ver
    Excel 2003
    Posts
    882

    Re: Upload data from excel file to access table using vba

    Hi Mike

    Thanks for your reply. But there is one confusrion here. As I have to write code in excel/vba that will upload data from sheet1 of another excel file to the Access table. The path and name of another excel file is present in textbox1 as mentioned in the code in the above post.

    I hope you can change the code accordingly.

    Thanks a lot.

  6. #6
    Forum Contributor
    Join Date
    02-20-2009
    Location
    bristol
    MS-Off Ver
    Excel 2003
    Posts
    882

    Re: Upload data from excel file to access table using vba

    Hi

    The following code works fine. But the only thing is it uploads data from sheet3 of present workbook to Access table. But in actual I want a functionality so that the user first browse the excel file and when he clicks on Upload button then it should upload that excel file to Access table. I have written the code to browse an escel file but how can we upload that file to Access table?
    Please Login or Register  to view this content.

  7. #7
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,519

    Re: Upload data from excel file to access table using vba

    Try this

    Please Login or Register  to view this content.

  8. #8
    Forum Contributor
    Join Date
    02-20-2009
    Location
    bristol
    MS-Off Ver
    Excel 2003
    Posts
    882

    Re: Upload data from excel file to access table using vba

    Thanks Mike

    I tried the following code but its giving me runtime error : The Microsoft Jet database engine could not find the object 'Orpkans'. Make sure the object exists and you spell its name and path correctly.

    Please Login or Register  to view this content.

  9. #9
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,519

    Re: Upload data from excel file to access table using vba

    You need to change GetExcelData("C:\Users\Mike\Desktop\", "Example dummy data ", "Orphans"). Also I see your Access table field names are different then what I posted. will need to change that part of code as well.

    Please Login or Register  to view this content.


    ---------- Post added at 08:03 AM ---------- Previous post was at 07:57 AM ----------

    Orphans needs to be changed to your Sheet Name in Excel file.

  10. #10
    Forum Contributor
    Join Date
    02-20-2009
    Location
    bristol
    MS-Off Ver
    Excel 2003
    Posts
    882

    Re: Upload data from excel file to access table using vba

    Mike,

    The textbox1 stores both path and filename. So thats why I am using the follwoing line:
    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    The sheet name will be "Orphans" always. Can you tell me why the error message is coming if I change the code as above.

    Thanks

  11. #11
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,519

    Re: Upload data from excel file to access table using vba

    Must have a $ after sheet name

    Please Login or Register  to view this content.

  12. #12
    Forum Contributor
    Join Date
    02-20-2009
    Location
    bristol
    MS-Off Ver
    Excel 2003
    Posts
    882

    Re: Upload data from excel file to access table using vba

    Thanks a lot Mike. Its working absolutely fine. U r Gr8.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1