Excel to Access using VBA MS access

[Asher David]

Hi,

Can anyone help me how to export specific excel file with only selected column to a specific table in MS access?

I can do that using VBA ms excel but i want to try it in MS Access VBA code.
The step is selecting 1st the ms Access database to be updated and then to follow selecting MS excel file that will be the source of data.

Thanks!

[RobDog888]

How about using Access to import the range?

[Asher David]

Can...

All I want is to move data form excel to specific MS access table.

Meaning there is diff MS access tool (VBA) in between the 2 (MS excel and MS access)

[RobDog888]

Ok, lets restart here.

Where do you need to execute (button click or whatever) the transfer from?

[Siddharth Rout]

Hi Ash

What I could gather from your post is that you are having trouble opening the relevant Excel File and then getting the relevant values from that file to store in the access table...

If I have understood you correctly then this should help...

Paste this code in the click event of a commanbutton say(cmd1)

I am not writing the code to update the table as I am assuming that you know how to do that...

Option Compare Database
 
Private Sub cmd1_Click()
 
'Opening Excel From Access
Dim oXLApp As Excel.Application
'Declare the object variables
Dim oXLBook As Excel.Workbook
Dim oXLSheet As Excel.Worksheet
 
Set oXLApp = New Excel.Application
 
'Open the relevant Excel File
'Replace it with your file name
Set oXLBook = oXLApp.workbooks.Open("C:\Temp\myfile.xls")
 
oXLApp.Visible = True
'Show it to the user
'Comment the above if you don't want the user to see the Excel File
 
'****************** updating the Table ***********************
 
'********** From Excel file, Say from Cell C1:C10 ************
For i = 1 To 10 'Change 10 to the relevant row number
 
'Update the relevant table field using a loop by storing the
'Range("C" & i).Value
 
Next i
 
MsgBox "Table Updated"
 
Set oXLSheet = Nothing
'Disconnect from all Excel objects
Set oXLBook = Nothing
Set oXLApp = Nothing
 
End Sub

Hope this helps...

[RobDog888]

Koolsid, that code is to be executed from within Access. I dont think that will be what the poster wants.

The step is selecting 1st the ms Access database to be updated and then to follow selecting MS excel file that will be the source of data.

Which is why I havent written any code yet.

[Siddharth Rout]

I can do that using VBA ms excel but i want to try it in MS Access VBA code.
The step is selecting 1st the ms Access database to be updated and then to follow selecting MS excel file that will be the source of data.

Hi Rob

What I understood from the quote is that : From within Access, he wants to access the table and then access the Excel File to pull the data...

I might be wrong...

[RobDog888]

Yes, but what threw me off was "The step is selecting 1st the ms Access database to be updated ..."

Maybe doing it in some other environment or selecting another access db from within an access db or ???

[Asher David]

I will not make it very confusing. Actually I have 3 files as follows:

1) MS Access Database (where all the data from excel be transferred here)
2) Multiple excel file ( I have raw data from excel, different workbooks, and the data will be moved to each table inside MS access Database)
3) MS Access Tool : ( this is the one containing a code to update the MS Access database by selecting MS Access database to be updated and then one select multiple excel file workbook. Inside the code I will chooses the file name of each excel file so that I can move the data inside each table inside MS access database)

I hope it not make everybody confused on this. My apology if this not that clear on my first post...... You see i just want to create a database with many tables that contain data's from ms excel.... All the data I have is in MS excel and I just want to put it inside the database... I can do it in MS excel like this:

VBA excel:

Set rS = db.OpenRecordset("RECORD_NAME", dbOpenTable)
On Error Resume Next
r = 6
Do While Len(Range("A" & r).Formula) > 0

With rS
.AddNew
'.Fields("Date") = Range("A" & r).Value
'.Fields("PLMN name") = Range("B" & r).Value


.Fields("Date") = Range("A" & r).Value
.Fields("name") = Range("B" & r).Value
.Fields("Surname") = Range("C" & r).Value
.update
End With

r = r + 1 ' next row
Loop
rS.Close
Set rS = Nothing
db.Close
Set db = Nothing

You see I can select specific column in MS excel and move it to specific column in a specific table.... My problem now is I want to migrate the code inside no.3 (MS Access Tool). Now everybody is wondering why I need to select a specific database.. The reason is we just need the tool to update it. And after 1 month data, I will discharge this database and create a new one again... So i dont want to create a code inside this database since after a month a new one is needed... It will just serve as a storage of data (like swl server) and then there is another tool I have to process all the raw data in this MS Access database.

Thank a lot for your concern.... Hope you all can help me with this...Best regards...

[RobDog888]

See this FAQ thread for the built in function in Access for importing a Excel spreadsheet.

http://vbforums.com/showthread.php?t=402075

[SulPal]

Hey Kool,

I get a compile error from the very 1st line
Dim oXLApp As Excel.Application
Its says "User-defined type not defined"
Is there some switch or something that needs to be set for this work?

<>Sulabh

[RobDog888]

Welcome to the Forums.

You need to add a reference to Excel xx.0 Object Library

[SulPal]

Thanks for the welcome,

but I still have this issue so I looked at your example "Excel 2003 And Early Binding Automation Code Example"
Copied and pasted it. Does that code work for VBA in access? cause thats what im using. So anyway I get the error it just moved from the sub command button to the sub form load when i double click the form. What up... I don't know man... is there some other step im missing?

[RobDog888]

It only deals with Excel so adding the Reference via the menu in the VBA IDE behind the form is all that is required.

[SulPal]

It only deals with Excel so adding the Reference via the menu in the VBA IDE behind the form is all that is required.

In the VBA code screen go to Tools -> References ->Click Microsoft Excel 12.0 Object Library. Done.




I figured it out thanks.

<>Sulabh

[Asher David]

Hi Robb,

But does this code cannot select the column I want to copy (specific column). You see let say I have 20 columns with data's but I want to copy the specific column only.
I tried this one but it seems it can copy all the excel sheets but not specific cell.

Thanks!

[Asher David]

anymore help about by previous query?

Thanks!