Import Tabbed Excel Spreadsheet into Access? (a2k (9.0.6926) SP-3 Jet 4.0

Topic

New Reply

I have an Excel spreadsheet with the following 12 Tabs Jan through Dec for an entire year of transactions

Each Tab can have a variable number of line items (from none to many) in the following format:

Permit#	Date	Fee	Value	Name		Address		Desc	CodeType
1-2007	01/02/08	$156	$12,000	Brown, John	1234 Main St	Remodel	A      3
2-2007	01/02/08	$176	$15,000	Brown, Mary	2345 Main St	Deck	C      5

2			$332	$17,000

I want to open a Access database an import the detail in each Excel Tab into one access table

I need some code within access to automate opening the spreadsheet and import Jan into a new table and import Feb through Dec into the same existing new table while ignoring any blank records and the total counts record in each Tab

See attached db for what the Jan Tab looks like

Any examples or help would be appreciated

Thanks, John

Reply | Quote

Replies

I’d remove the totals rows from all the sheets in Excel. You should then be able to import them directly into Access.

If that is not possible, please attach a representative sample workbook instead of a database.

Reply | Quote

Here you go!

Reply | Quote

The sheets aren’t suitable for importing into Access since column E is blank. I’d delete column E and delete all the superfluous rows manually. That’s probably faster than working around these problems using VBA.

Reply | Quote

Thanks for the information

Reply | Quote