Trouble with Importing excel file

[Jmaddog]

I have a large excel file that I need to import into access monthly. I receive the spreadsheet from our finance department and its current format will not work as an access table. Any idea on how I can change this? This is a monthly report and I do not want to spend a lot of time each month manipulating the data. The spreadsheet is layed out with department codes (approximatley 100) as column headings and General Ledger #'s as row headings. I would like this reversed. I tried to import as is and I am unable to sort by department number (which would be my key field). Any suggestions on how I can make this work?

 

[Access_guy49]

Is the monthly report always the same size? (same number of columns and rows?)

 

[Jmaddog]

yes, the file is typically same number of rows and columns, it can change if we add a new department number, but this is rare.

 

[Access_guy49]

My suggestion would be to create a macro in Excel that copies the current sheet, Then transposes the data so that your department codes run down the column instead of one for each column. Then give yourself some titles for each column and then bring it into access. So most of your work will be in Excel.

Make a copy of one of your excel files to play with.
Then click on tools > Macro > Record new Macro.
(Now anything you do will be recorded to a script.)

Copy the entire report. Click on a new cell where you want to place the copy of the report (I recomend column A under your existing report, or a new tab) leave space to add your headings (if you need them)
Right click, Paste Special, then check off the "Transpose" box.
Now all your job codes will be in one column.
Highlight the rows where your original data was and delete rows.
Now add your headings if you havn't already and then click the stop recording button on the record macro toolbar.
You should now have an excel sheet that can be imported quickly into access.
If it works you will be able to use the script the next time and as long as the ranges for your copy action doesn't change, you will be able to do this with a click of a button.

(Hope that makes sense. I might not explain it that well if you need more clerification let me know)

 

[Jmaddog]

Thank you - that worked perfectly.
I appreciate the help and your instructions were easy to follow.
Thanks again.