Import Excel into Access VBA

E

ECEstudent

Registered User.
Local time
Today, 02:10
Joined
Jun 12, 2013
Messages
153
Hi, I'm trying to import an excel file that includes a column of Part Numbers that I am interested in. I am trying to import that excel file into an access table called 'TableForImport'. That table has a column called 'PartNumber'. Thing is it's not just one Excel file that I am uploading...although I am uploading one file at a time. So the column name for each excel file table is different but they all have one column for the Part Numbers that I am interested in.

My code won't allow me to import the excel file into table 'TableForImport' unless they have the same column name! They never will! How do I accomplish my task without editing the excel file information??

Here is my code:
Code: 
CurrentDb.Execute "CREATE TABLE TableForImport " _
& "(PartNumber CHAR);"
 
 
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "TableForImport", Me.ExcelInput, True
 
Also, I am currently uploading the Excel file from my documents folder only. It can't find it unless it's in the documents folder. How do I change that? I want to upload Excel files from any drives on my computer or even Sharepoint. Please help.

THANK YOU!!!
 
ECEstudent said:
Also, I am currently uploading the Excel file from my documents folder only. It can't find it unless it's in the documents folder. How do I change that? I want to upload Excel files from any drives on my computer or even Sharepoint. Please help.

THANK YOU!!!
Click to expand...
Upload to where, to the site here, to a company server or ... ??
 
I'm not sure I understand your question...I want to be able to upload the excel file from anywhere pretty much. From Desktop, Documents folder, C.drive, P.drive....any folder on the computer
 
I'll try searching about it some more and let you know if I find anything :)
Also, Please let me know if you know anything about it. Thank you.
 
Hi, I am new here but I have a lot of practical experience of getting data into Access, but without using VBA especially. Maybe this process will work, and then you can have a look at the SQL code and move on from there:

I suggest you create a new table with the relevant fields in it (or import one of your source tables and delete unnecessary fields). Then use Linked Tables and Append queries to add additional Part Numbers records to this master table. This means you can overcome Field Name problems by using an alias.

Alternatively, perhaps spend some time cleaning up your Excel files so that they are more consistent?

I hope I have not missed the point, and that this is useful for you.
Clive
 
Thanks Clive :) I'd already figured out a way of getting the first part to work and I'm just stuck on importing an Excel file from different sources.
 
You must log in or register to reply here.

Similar threads

oxicottin
Solved Import from excel and delete unwanted data
Replies
14
Views
517
oxicottin
oxicottin
E
Importing Specific Field Names From Excel into Access Table
Replies
12
Views
519
Pat Hartman
P
R
Solved Import Excel file into existing table with an autonumber primary key field
Replies
6
Views
290
Isaac
Isaac
H
append data from excel to existing table in access vba
Replies
6
Views
732
Pat Hartman
P
C
Quickly copy Excel rows to Access
Replies
12
Views
472
CJ_London
C

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom