Go Back   Access World Forums > Microsoft Access Discussion > Macros

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 11-16-2011, 11:52 AM   #1
vaughan198
Newly Registered User
 
Join Date: Nov 2011
Posts: 40
Thanks: 6
Thanked 0 Times in 0 Posts
vaughan198 is on a distinguished road
Post Import From Excel

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

vaughan198 is offline   Reply With Quote
Old 11-17-2011, 10:40 AM   #2
chrisguk
Newly Registered User
 
Join Date: Mar 2011
Posts: 148
Thanks: 14
Thanked 4 Times in 4 Posts
chrisguk is an unknown quantity at this point
Re: Import From Excel

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
chrisguk is offline   Reply With Quote
Old 11-18-2011, 07:36 AM   #3
vaughan198
Newly Registered User
 
Join Date: Nov 2011
Posts: 40
Thanks: 6
Thanked 0 Times in 0 Posts
vaughan198 is on a distinguished road
Re: Import From Excel

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 is offline   Reply With Quote
Old 11-21-2011, 07:16 AM   #4
vaughan198
Newly Registered User
 
Join Date: Nov 2011
Posts: 40
Thanks: 6
Thanked 0 Times in 0 Posts
vaughan198 is on a distinguished road
Re: Import From Excel

Is anyone able to help with this as really need to get it working. Thanks
vaughan198 is offline   Reply With Quote
Old 01-03-2012, 01:24 AM   #5
lwout
Newly Registered User
 
Join Date: Dec 2011
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
lwout is on a distinguished road
Re: Import From Excel

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)

lwout is offline   Reply With Quote
Reply

Tags
import , macro

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
import multiple excel files with FILTERED Excel spreasheet into an access table WuJu Modules & VBA 5 10-31-2011 04:38 PM
Import from Excel jhawkins49 Tables 0 08-22-2011 09:41 AM
Import Excel esskaykay Modules & VBA 2 01-14-2007 08:59 AM
import excel refaeldakar Modules & VBA 0 03-02-2006 04:19 AM
Import from Excel Billionz General 2 07-14-2002 12:36 PM




All times are GMT -8. The time now is 12:58 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World