Import Excel into Access VBA (1 Viewer)

E

ECEstudent

Registered User.

Local time

Today, 14:10

Joined

Jun 12, 2013

Messages

153

• Jul 18, 2013

• #1

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:Copy to clipboard

CurrentDb.Execute "CREATE TABLE TableForImport " _
& "(PartNumber CHAR);"
 
 
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "TableForImport", Me.ExcelInput, True

 

E

ECEstudent

Registered User.

Local time

Today, 14:10

Joined

Jun 12, 2013

Messages

153

• Jul 18, 2013

• #2

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!!!

 

J

JHB

Have been here a while

Local time

Today, 23:10

Joined

Jun 17, 2012

Messages

7,732

• Jul 19, 2013

• #3

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 ... ??

 

E

ECEstudent

Registered User.

Local time

Today, 14:10

Joined

Jun 12, 2013

Messages

153

• Jul 22, 2013

• #4

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

 

David R

I know a few things...

Local time

Today, 16:10

Joined

Oct 23, 2001

Messages

2,633

• Jul 22, 2013

• #5

I think you're looking for the FileDialog. Lots of good searches for that, such as http://www.access-programmers.co.uk/forums/showthread.php?t=249579&highlight=filedialog

 

E

ECEstudent

Registered User.

Local time

Today, 14:10

Joined

Jun 12, 2013

Messages

153

• Jul 22, 2013

• #6

lol...It's an unanswered question like mine

 

E

ECEstudent

Registered User.

Local time

Today, 14:10

Joined

Jun 12, 2013

Messages

153

• Jul 22, 2013

• #7

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.

 

C

cgp

New member

Local time

Today, 14:10

Joined

Jul 23, 2013

Messages

4

• Jul 23, 2013

• #8

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

 

E

ECEstudent

Registered User.

Local time

Today, 14:10

Joined

Jun 12, 2013

Messages

153

• Jul 24, 2013

• #9

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.