Import From Excel (1 Viewer)

vaughan198

Registered User.
Local time
Today, 12:47
Joined
Nov 16, 2011
Messages
40
Hi there. I know that this has been discussed a lot but I can't seem to find quite the right solution to my problem.

I have an excel file that is emailed to me every day with three sheets/tabs in it.

I wish to add these excel records to the bottom of three different tables in my database. The excel file has headers which are the same as the ones in my database.

The filename also changes each day so it would either need to import the most recent file or (better in case there are newer files-like over the weekend) I would be able to select the file.

Any help would be hugely appreciated.

Thanks,

Will
 

chrisguk

Registered User.
Local time
Today, 12:47
Joined
Mar 9, 2011
Messages
148
Hi,

Not sure how familiar you are with VBA but you could use the following. To access VBA from your application just hit Alt+F11. I assume you want to assign a command click function too:

Code:
Function Impo_allExcel()
 
Dim myfile
Dim mypath
mypath = "c:\importxls\myimports\"
Do
myfile = Dir(mypath & "*.xls")
DoCmd.TransferSpreadsheet acImport, 8, "myimport2", mypath & myfile
myfile = Dir
Loop Until myfile = ""
 
End Function
 

vaughan198

Registered User.
Local time
Today, 12:47
Joined
Nov 16, 2011
Messages
40
Thanks for your help but this is not quite what I was looking for.
I have a workbook that has three tabs/sheets that I receive each week. The layout is exactly the same in the database as the excel file. The name changes slightly each week so I wanted to select the file before importing as I would like the excel file to remain in the folder after import.
I need each tab/sheet in the workbook to be added to the bottom of each table in access.
Any help?
 

vaughan198

Registered User.
Local time
Today, 12:47
Joined
Nov 16, 2011
Messages
40
Is anyone able to help with this as really need to get it working. Thanks
 

lwout

New member
Local time
Today, 20:47
Joined
Dec 7, 2011
Messages
1
Maybe this will help?

w w w .accessmvp.com/KDSnell/EXCEL_Import.htm#ImpAllWktsSepTbl

Or this?

I now use a very simple method with a file named "Export.xls" (for import use)
I have "docked*" each tab as an table in the access database and use "insert into"querys to read each tab out of the Excel sheet.
I use the code below :

Private Sub BtnIMport_Checklists_Click()

'page 534 book Access 2002
'Need For each table an "Insert" Query that reads only one Excell file named Export.xls.
'In explorer you'll need to rename each xls file to export.xls (ctrl Z renames it back afterwards)

DoCmd.OpenQuery "Q_Tab1_Insert", acViewNormal, acEdit
DoCmd.OpenQuery "Q_Tab2_Insert", acViewNormal, acEdit

End Sub
=============
It works still in Office 2007 (03-01-2012)
Each week I read export files from other databases (clones) to make 1 complete database
Each database is the same but the input is on different locations (off line)

* I don not know the english name for an excel sheet that is related to an Access database
You'll see the excel logo in access when "docked" (tr. dutch "Gekoppeld")
(I am from the Netherlands and use a Dutch Access 2007)
 

Users who are viewing this thread

Top Bottom