Macro in MS Access to import excel file

Joe4

MrExcel MVP, Junior Admin

Joined

Aug 1, 2002

Messages

72,265

Office Version

1. 365

Platform

1. Windows

• Sep 22, 2004

• #2

This is not as easy as it is in Excel. You actually have to add a lot of code to do this. The code is quite long and cumbersome, but the good news is you don't need to understand it completely, just copy & paste it in and use it.

Here is a link with the code:
http://www.mrexcel.com/board2/viewtopic.php?t=85521&highlight=openfile+dialog+access

 

Upvote 0

Norie

Well-known Member

Joined

Apr 28, 2004

Messages

76,377

Office Version

1. 365

Platform

1. Windows

• Sep 22, 2004

• #3

Does the Excel file(s) the user might wish to import have a particular name in common?

Are they located in the same location?

If so one method could be to have a form with a (unbound) combobox which when the form opens it is populated with the names of the files the user can import.

It would also have a command button that when clicked uses the value of the combobox to import the file and append it to the table.

To give a full solution more info is needed.

 

Upvote 0

S

spatel

New Member

Joined

Sep 22, 2004

Messages

14

• Sep 22, 2004

• #4

Thanks for responding!!

I am going to get 2 .xls payroll files at the end of each month (Hours.xls and Dollars.xls). Currently I manually append the files into Hours.tbl, Dollars.tbl respectively in Access.

I want to create a macro which would be invoked by the click event of a button on a Access Form. The macros is going to append each .xls to its respected tables in access.

The problem is when creating the macros the path for the file to be imported is static. I want to create someting on the form where the user has the abiility to select the file that they are going to import and the destination table that its going to be appended into.

Hope this is enough info....

 

Upvote 0

S

spatel

New Member

Joined

Sep 22, 2004

Messages

14

• Sep 22, 2004

• #5

One more thing.

Files are different names but are in the same location.

 

Upvote 0

Joe4

MrExcel MVP, Junior Admin

Joined

Aug 1, 2002

Messages

72,265

Office Version

1. 365

Platform

1. Windows

• Sep 22, 2004

• #6

Spatel,

Did you look at the link I provided? That gives you the code that is necessary to do what you are asking, to get a "Open File Dialog" box.

 

Upvote 0

S

spatel

New Member

Joined

Sep 22, 2004

Messages

14

• Sep 22, 2004

• #7

Yes I did.

I pasted the whole code into a new module, but how do invoked that from a button on a form. Like what action I need to do in order to fire that code.

 

Upvote 0

Joe4

MrExcel MVP, Junior Admin

Joined

Aug 1, 2002

Messages

72,265

Office Version

1. 365

Platform

1. Windows

• Sep 22, 2004

• #8

You just make reference to the "GetOpenFile" function name.

Posted below is some code where I use it to browse my files, and store whatever file I select into a variable named "myOpenFileName". This code is attached to the "Click" event of a command button I have named "cmdFileOpenBrowse".

You can then open/import/etc the file using this variable name.

Code:

Private Sub cmdFileOpenBrowse_Click()
'   Browse for open file
    
    Dim myOpenFileName As Variant
    myOpenFileName = GetOpenFile

End Sub

 

Upvote 0

S

spatel

New Member

Joined

Sep 22, 2004

Messages

14

• Sep 22, 2004

• #9

Cool. IT almost works!, thanks

however, I want to append it to the hours table in access so do I still have to call that Macro in my click event after I make refererence to the get file function.

ALso, when the dialgoue box opens up, It only lets me select a .mdb file and I can't change the file type to "ALL" to view all files in the directory cause the file I am importing is an .xls file.

thanks bro...

 

Upvote 0

Joe4

MrExcel MVP, Junior Admin

Joined

Aug 1, 2002

Messages

72,265

Office Version

1. 365

Platform

1. Windows

• Sep 22, 2004

• #10

ALso, when the dialgoue box opens up, It only lets me select a .mdb file and I can't change the file type to "ALL" to view all files in the directory cause the file I am importing is an .xls file.

Click to expand...

Under the "GetSaveFile1" function in that code, try changing:

Code:

    strFilter = ahtAddFilterItem(strFilter, _
'                "Access (*.mdb)", "*.MDB")

to

Code:

    strFilter = ahtAddFilterItem(strFilter, _
                  "Microsoft Excel (*.xls)", "*.XLS")

Regarding the append of data to your table, what code are you using to import the file? Most of the import techniques I use will append the data (as matter as fact, I usually need to delete the data with more code if I want to overwrite it).

 

Upvote 0