Does Importing Excel files into Access require a standard format?

[ALaurie10]

I have data in an excel file(s) which is in Accounting format. This means that my feilds are going down the page and the values across. Do I have to change the excel format of the data? this would be extremely time consuming for each file. Or can I write a code that selects the data from the worksheet that I need to import? I am using Access 2003.
Any help would be greatly appreciated.

[Curben]

I have data in an excel file(s) which is in Accounting format. This means that my feilds are going down the page and the values across. Do I have to change the excel format of the data? this would be extremely time consuming for each file. Or can I write a code that selects the data from the worksheet that I need to import? I am using Access 2003.
Any help would be greatly appreciated.

I Do not know if there is an obscure way to do what your doing, but I do know an easy way to make your excel file importable.

Create a new sheet
Copy all data from current sheet
Right click cel A1 on sheet two
Choose Paste Special
on the paste special box check the box for Transpose.


Now you should be able to import sheet two into access, if you need to do this often, either formulas or VBA Macros in Excel can refresh the data regularly. Depending on how many files you have the formulas option could be copied into multiple books.

Keep an eye on this thread however, I know very little in VBA and I would imagine sooner or later someone may know a way to import with vertical fields, but hopefully my help will get you started

[ADezii]

I have data in an excel file(s) which is in Accounting format. This means that my feilds are going down the page and the values across. Do I have to change the excel format of the data? this would be extremely time consuming for each file. Or can I write a code that selects the data from the worksheet that I need to import? I am using Access 2003.
Any help would be greatly appreciated.

Curben's advice on Transposing the data in Excel seems to be sound. To the best of my knowledge, I see no easy way in Access to accomplish the same result. It would take some VBA trickery to flip-flop the Field designations and the data itself.

[ALaurie10]

Thanks alot. I really appreciate your assistance.

[Curben]

Thanks alot. I really appreciate your assistance.

If you need any help with automating the process for excel, let me know. Excel is my strongsuit.

[ALaurie10]

Yes Curben, I could really use your help. Unfortunately transposing the data is not working for me I have the basic name of my feild going down and the values going across. But there are four values for each feild. That is the first problem. The next problem is that, I many excel files that I need to transpose.

[Curben]

Yes Curben, I could really use your help. Unfortunately transposing the data is not working for me I have the basic name of my feild going down and the values going across. But there are four values for each feild. That is the first problem. The next problem is that, I many excel files that I need to transpose.

By your explenation, i am not sure why transpose would not work, can you be a little more in depth so i can understand, or if you can post up an example file somewhere i can take a look at it and then can provide more direct input

[ALaurie10]

ok, i am using access 2003 and excel 2003, my excel worksheet contain financial data. The Basic Name of each data feild are in cells going down the page( by record), For each Basic Name feild, I have four subfeilds of the Basic Name feild, with corresponding data( my values) that are in cells going across the page (by column). This is the crux of my dilemna.

I would like to be able to import the data of many different excel worksheets, which are all in similar format described above into an access table. Unfortunately, after a general import of my excel worksheet into access, I find that the data is specific or unique to Feild and record. The feilds after my import do not have similar information.

I am wondering if an SQL statement in a query would do the job of identifying specific feilds and records and putting it in an access table for me?

[ALaurie10]

here is an example:

Num Hrs Wk day
Work 10 50 2 5
pay 20 20 5 2

[ALaurie10]

ok that did not post correctly


........... Num........Week.........Hrs............day
Work.....10............5...............50......... .....6
Pay........6.............12..............4........ ........2..


get the idea?

[Curben]

ok that did not post correctly


........... Num........Week.........Hrs............day
Work.....10............5...............50......... .....6
Pay........6.............12..............4........ ........2..


get the idea?

Getting it . . . If I understand your worksheet is only 5 columns (for example) wide and builds onto rows? Or do columns get added in groups with each entry?

For arguments sake, this seems more like what excel is specifically meant to do. Financial spreadsheets and accounting. If it does need to be in access, it can be done, but will take some work and a fair amount of formulas/reporting to reach that goal. I am sure we can reach either a single method that can be reused and updateable w/o much hassles going forward to get all of your files in line. We will just need to go back and forthe a bit to resolve.

Answer the questions I posed and ill mull over some ideas, we will see what i have when you reply.

[ALaurie10]

My excel worksheet goes from A to AH. And I am sure some of the columns are hidden. The some of the data is calulated on the worksheet itself. Other data is referenced from another worksheet which is hidden and which pre-populates the data on the worksheet that holds data which I am trying to import. The hidden worksheet pulls the data from XML's. However the data that I need is determined in my current worksheet.

[Curben]

My excel worksheet goes from A to AH. And I am sure some of the columns are hidden. The some of the data is calulated on the worksheet itself. Other data is referenced from another worksheet which is hidden and which pre-populates the data on the worksheet that holds data which I am trying to import. The hidden worksheet pulls the data from XML's. However the data that I need is determined in my current worksheet.

Without the actual sheet, i can only wrap my mind around this so far. The answer, if there really is one; lies in either a pivot table, or creating a new sheet that pulls data from the first sheet in a standard format.

You can create A header row, then align Vlookups or Hlookups to reorganize data for importing purposes; an example using the table you posted might be
...................WorkNum........................ .....................PayNum
=vlookup("Work",A1:AH100,2,False)......=vlookup("P ay",A1:AH100,2,False)

Or a similiar way to concatenate the data. If that might work, see how you can aligh the data for import, if not, let me know and i will see what i can think of next

[ALaurie10]

Thanks, I am going to try it.

[Curben]

Thanks, I am going to try it.

Best of Luck, Please let me know if you get it to work