Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.access.gettingstarted,microsoft.public.excel.misc
|
|||
|
|||
Importing Data from Excel when data not in same format
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. "AMB" wrote: 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Importing data from another spreadsheet to a different format | Excel Discussion (Misc queries) | |||
Removing Data Tables formed from importing data from Access | Excel Discussion (Misc queries) | |||
cell format options when importing data | Excel Discussion (Misc queries) | |||
Changing default data format settings when importing CSV files | Excel Discussion (Misc queries) | |||
Data types when importing Excel data to SQLServer | Excel Discussion (Misc queries) |