+ Reply to Thread
Results 1 to 13 of 13

Macro to Import Excel Data into an MS Access File

  1. #1
    Registered User
    Join Date
    03-19-2013
    Location
    India
    MS-Off Ver
    Excel 2003
    Posts
    11

    Macro to Import Excel Data into an MS Access File

    Hi Everybody,

    I have a excel sheet in which there are 5 columns having some data.
    I want that the same data gets populated into MS Access file, when we press Alt+F8 a user form appears asking to import data to an MS Access file.

    Refer the attached excel file and the userform in it.

    Please help.
    Attached Files Attached Files
    Last edited by SaurabhShri1; 03-22-2013 at 07:50 AM.

  2. #2
    Forum Contributor
    Join Date
    12-31-2012
    Location
    Jhang, Pakistan
    MS-Off Ver
    Excel 2010
    Posts
    250

    Re: Macro to Import Excel Data into an MS Access File

    What is the Access File Name, Table Name in which data will be inserted and the Field Names of that Table?

  3. #3
    Registered User
    Join Date
    03-19-2013
    Location
    India
    MS-Off Ver
    Excel 2003
    Posts
    11

    Re: Macro to Import Excel Data into an MS Access File

    It can be anything, what i want is that all the data which is in Excel file should be in Access file.

  4. #4
    Forum Contributor
    Join Date
    12-31-2012
    Location
    Jhang, Pakistan
    MS-Off Ver
    Excel 2010
    Posts
    250

    Re: Macro to Import Excel Data into an MS Access File

    Download both attachments, zip file contains Access mdb.
    Place both files in same directory.
    Each time, previous data will be deleted from access file and
    re-inserted from your excel sheet.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    03-19-2013
    Location
    India
    MS-Off Ver
    Excel 2003
    Posts
    11

    Re: Macro to Import Excel Data into an MS Access File

    Thanks a lot HafizImran.
    Your code is working fine, but when i add some columns in my excel sheet then it is giving error in this row

    .Fields(j - 1).Value = Cells(i, j)

    I am not getting how to fix it

    I mean that do we manually need to create Table(fields)?
    and then with the help of macro, values can be inserted into access file....
    Last edited by SaurabhShri1; 03-25-2013 at 04:59 AM.

  6. #6
    Forum Contributor
    Join Date
    12-31-2012
    Location
    Jhang, Pakistan
    MS-Off Ver
    Excel 2010
    Posts
    250

    Re: Macro to Import Excel Data into an MS Access File

    This is because the access table has just 5 fields in it. You will have to add new fields in that table.

  7. #7
    Registered User
    Join Date
    03-19-2013
    Location
    India
    MS-Off Ver
    Excel 2003
    Posts
    11

    Re: Macro to Import Excel Data into an MS Access File

    ok, thanks for the information,
    Is it possible to give it a file name by using another pop up.
    I mean that instead of hard coding the file name in the code, the user should be asked for a file through a form, from where he can select a file.

  8. #8
    Forum Contributor
    Join Date
    12-31-2012
    Location
    Jhang, Pakistan
    MS-Off Ver
    Excel 2010
    Posts
    250

    Re: Macro to Import Excel Data into an MS Access File

    Yes, get file name with this code, and use Filename variable to refer to access database:
    Please Login or Register  to view this content.

  9. #9
    Registered User
    Join Date
    03-19-2013
    Location
    India
    MS-Off Ver
    Excel 2003
    Posts
    11

    Re: Macro to Import Excel Data into an MS Access File

    ok, one more thing
    lets say i had a input box where the user enters the name of the mdb file(in which the excel data will be copied).

    I want to check that the file name which i had given in the input box, exists in my database or not.
    As an example: I had a .mdb file Excel_to_Access in my database "Access_Converted" but when in input box i enter some other file name (say abc) then a pop up should come that "The specified file does not exist"

    Please refer the attached sheet.
    Attached Files Attached Files

  10. #10
    Forum Contributor
    Join Date
    12-31-2012
    Location
    Jhang, Pakistan
    MS-Off Ver
    Excel 2010
    Posts
    250

    Re: Macro to Import Excel Data into an MS Access File

    Please Login or Register  to view this content.

  11. #11
    Registered User
    Join Date
    03-19-2013
    Location
    India
    MS-Off Ver
    Excel 2003
    Posts
    11

    Re: Macro to Import Excel Data into an MS Access File

    Thanks a lot, it helped
    One more thing, I need to create another table of same structure with some different name.
    I tried searching to create it by using
    cn.execute "create table table_name as select * from Excel_to_Access"
    but it is not working...
    any suggestion for it?

  12. #12
    Forum Contributor
    Join Date
    12-31-2012
    Location
    Jhang, Pakistan
    MS-Off Ver
    Excel 2010
    Posts
    250

    Re: Macro to Import Excel Data into an MS Access File

    Please Login or Register  to view this content.

  13. #13
    Registered User
    Join Date
    03-19-2013
    Location
    India
    MS-Off Ver
    Excel 2003
    Posts
    11

    Re: Macro to Import Excel Data into an MS Access File

    Yippiee...
    I cant believe this Hafiz sir, it worked!!!
    Thanks a lot, you have really helped me...

+ 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