Importing Data from Excel when data not in same format

[AMB]

Hello. I am a novice at Access and could really use some help.

Each month I receive an Excel file with data I would like to add to Access
so I can query and report on. However, the format is a little different each
month (the order of the columns, etc.) and there are columns that I do not
want to add.

How would I be able to import the roughly 10 data fileds that I would want
each month to the same table?

Thanks!

 

[John W. Vinson]

Hello. I am a novice at Access and could really use some help.

Each month I receive an Excel file with data I would like to add to Access
so I can query and report on. However, the format is a little different each
month (the order of the columns, etc.) and there are columns that I do not
want to add.

How would I be able to import the roughly 10 data fileds that I would want
each month to the same table?

Thanks!

Are the *names* of the columns consistant? If so consider linking to the
spreadsheet (rather than importing it), and creating an Append query to append
data (by field name, not by position) into your target table:

INSERT INTO targettable (thisfield, thatfield, saledate, Employee)
SELECT thisfield, thatfield, saledate, salesperson
FROM linkedspreadsheet;


This may be a "garbage in/garbage out" phenomenon... if you are getting
inconsistantly structured data can you be sure you're not getting inconsistant
contents?

 

[Terry_NOTD]

AMB,

You prefer you can tackle the excel problem first by using a macro to line
up the columns to match the order in which they should be. Also it will
remove the unwanted colums so that the excel data will be in the format that
you are use to working with, and for importing into MS Access.

Open Excel and press Alt+F11 key to open the VB Editor. Select Module from
the Insert menu bar. Copy & Past this code into the empty window and save.

Sub Rearange_Order()
'Adopted
Sheets("variable colums").Select
I = Sheets("variable colums").Index
Sheets.Add
Sheets(I).Name = "Ouput"
Last_Col_Fixed = Sheets("fixed columns").Range("IV1").End(xlToLeft).Column
Last_Col_Variable = Sheets("variable
colums").Range("IV1").End(xlToLeft).Column
I_Col_New = 1
For I = 1 To Last_Col_Fixed
Search_Header = Sheets("fixed columns").Cells(1, I)
Sheets("variable colums").Select
Set C = Range(Cells(1, 1), Cells(1,
Last_Col_Variable)).Find(Search_Header, LookIn:=xlValues)
If (Not (C) Is Nothing) Then
Cells(1, C.Column).EntireColumn.Copy Sheets("New").Cells(1,
I_Col_New)
I_Col_New = I_Col_New + 1
End If
Next I

Rename sheet1- fixed columns and Sheet2 - variable colums. Put your original
column header names in the order they should appear in the worksheet called
'fixed columns'. Copy&Paste the new excel received data into sheet, 'variable
colums'. When you run the macro, a new sheet called 'Output' will be created
with data and colums in the correct order, minus unwanted data.

(e-mail address removed)