read - excel (1 Viewer)

[mission2java_78]

I have this nasty german excel file I need to read into a database...anyone know how to read from excel into access.

For instance in excel I have the following


Description KMA LMA TSA
blah 20 2 12.2
...
...
.
.

Now I need to read into a table the description in one field, KMA in another...and so on. There are about 20 other fields not worth mentioning. Each field is in a different cell for excel. For instance Description is in cell D and KMA is in E and so on.

Thanks,
Jon

 

[k209310]

you can import data from excel from the import extrernal data found on the file menu. Select import and then on the file type select Excel file. Then follow the steps.

It allows you to create a table with all of the data from the excel file - you may have create named ranges first in the excel file - its been a while since ive done this.

Hope this helps a little

 

[mission2java_78]

nah im looking for vba / vb code to do this...
jon

 

[k209310]

try this link...

http://www.mrexcel.com/board/viewtopic.php?topic=21909&forum=2

 

[mission2java_78]

That uses the DoCmd.Transferspreadsheet option...
I really need more control...I need to specify certain columns and do checks...this file is ugly. I can send this excel file to you if you'd like so you can see exactly what I am talking about.

Jon

 

[mission2java_78]

I want to use the Excel objects...
and have come up with this:
Dim objExcel As Excel.Application

Set objExcel = CreateObject("Excel.Application")

objExcel.Visible = False

but i need to open a specific excel file...cant seem to find a function to do this...

jon

 

[mission2java_78]

I figured out how to open the file ...
but I need to know how to access each row...like a loop.

Anyone have any ideas on how to loop through this excel sheet?

Here is what iI have so far:
Private Sub Command0_Click()

Dim objExcel As Excel.Application
Dim cRows As Long

Set objExcel = CreateObject("Excel.Application")
objExcel.Workbooks.Open "c:\jhwork\DB_t_01"

objExcel.Visible = True
cRows = objExcel.Rows.Count

MsgBox cRows

End Sub

 

[k209310]

Sorry about the delay ive been away. Looping th=rough the ranges will depend on how the data is organised. I know i keep giving you links but there is a good page which expalins ranges in excel quite well

http://www.microsoftexceltraining.com/VBA/ExcelRanges.htm

The way that you loop through would obviously depends upon how your data is organised.

Are you getting any closer? To get your data back you could use VBA to copy and paste the data from excel in to the required table/field in access.