Inserting records into Access table from Excel spreadsheet (1 Viewer)

[reglarh]

I am sure this has been covered many times but I can't seem to find the answer.

I have an Excel spreadsheet and I want to import the contents of Sheet 2 into an existing Access table. I can achieve this by using standard Access menu options but I want to achieve it by using VBA so it can be achieved by an end user. I can ask the user to find the file in VBA but it is the next step I can't achieve. I am using Office 2010.

 

[Frothingslosh]

The simplest method is DoCmd.TransferSpreadsheet.

 

[reglarh]

Yes, I looked at that, but how do you specify a sheet name?

 

[isladogs]

Use this syntax:

DoCmd.TransferSpreadsheet(TransferType, SpreadsheetType, TableName, FileName, HasFieldNames, Range, UseOA)

Some of these are optional

For details see,
https://msdn.microsoft.com/en-us/library/office/ff844793.aspx

 

[Frothingslosh]

Yes, I looked at that, but how do you specify a sheet name?

For the Range parameter, enter your sheet name followed by an exclamation point.

 

[reglarh]

I used the following statement:

DoCmd.TransferSpreadsheet acImport, 9, "BankPayments", FileName, True, Sheet2!

but Sheet2 evaluated to zero and gave an error.

FileName was correct and taken from a filedialog command.

 

[isladogs]

Did you have the full file path or just the name?

 

[reglarh]

The full path name, and other contributors have solved the format of how to include the sheet number. So the code runs, but doesn't import any data!!

The Excel spreadsheet contains column names that match the table field names, but there are two tables field names that are not present in the spreadsheet - these are calculated by further Access VBA coding at the next stage. Could these cause the problem?

 

[isladogs]

Yes - you want the same field names & in the same order
Try adding the 2 extra field names to the spreadsheet & just leave them blank

 

[reglarh]

Yes!!!!!!!

All solved.

Thanks guys.