Importing from Excel to Access 1

[roJak]

If any one can help , that would be fantastic

I need code to import Excel type file to access

The Excel Workbook will have 5 spread sheet , i need to import each spread sheet into a table each in access

EG.

The excel file named "Progress.xls" has 5 spreadsheets ; english , maths , science , history , geography. This is to be imported into "ProgressDB.mdb". Each spreadsheet is to be imported into 5 existing tables , named english , maths , science , history , geography.

Can anyone please help ?

 

[dyarwood]

Write your code in access and use something similar to the below

DoCmd.TransferText [Type eg spreadsheet], "TABLENAME", "ADDRESS OF FILE", [TITLES ON FIRST LINE TRUE/FALSE]

There should be an option to import a single sheet within a spreadsheet. Hope that gives you some ideas.

dyarwood

 

[sfm6s524]

Or, from within Access:

DoCmd.TransferSpreadsheet acImport,,"English","Progress.xls",True,"English!A1:M100"

DoCmd.TransferSpreadsheet acImport,,"Math","Progress.xls",True,"Math!A1:M100"

Etc, with the appropriate xls path and sheet ranges. I'd recommend importing into a temporary table to validate the data (key violations, null records, etc.) before appending it to your real tables.

 

[LebronJames]

Is there a way to schedule this import from Access. Would you be able to in coding say "import excel spreadsheet at 11h00 and 15h00 every day... is there a way to do this?

 

[sfm6s524]

Sure. Use a job scheduler to run your app with the Cmd option, such as C:\Program Files\...\MSAccess.exe C:\AppPath\App.mdb /cmd ImportExcel.
Then, write a function that runs first from an AutoExec macro or the first form:

Function CheckCmd()
If UCase$(Command) = "IMPORTEXCEL" Then
' Code to import from Excel below.
' ...
' Then quit.
Application.Quit
End If
End Function

 

[roJak]

Thanks ... that helped , I managed to import the file. However , after editing code and making it more "user friendly" I came up with certain errors. I'm not able to explain the errors as I am not sure wat the errors are .. if anyone can help , please leave your email and I will email the db. Hope to get more help. Thanks.

 

[sfm6s524]

RoJak, I can take a look (send mdb & xls). firch@frontiernet.net