Get data from Excel Using Access VBA

[speakers_86]

I am trying to automate data entry into my program. The data comes from an excel sheet. I can't just use import, I have to do it with vba.

I need a routine that:
opens a specified excel document
cycle through all sheets
place the value of A1 in my form

Then there is a table starting at A13 to J13, and goes down for an
unkown number of rows. I need to cycle through each record and move
that data into my subform.

I am okay with vba, but manipulating Excel is something I've never needed to do. I tried to make it work last night using a reference to the excel library, but I couldn't even get the path to open!

 

[John Big Booty]

I'm not sure if the sample here is doing the sort of thing you are after. Currently it only imports from one work sheet, hopefully it will give you a starting point.

 

[John Big Booty]

... hows sunny Kandahar, this time of year

 

[speakers_86]

It gets really cold at night, but during the day it is okay. The sky at night is soo much clearer here. I really thought about ordering a telescope.

edit- That link isn't quite what I am looking for because it uses the transfer spreadsheet method. The excel document is not set up like a table. I only need certain fields, and those fields need to be validated before importing.

 

[John Big Booty]

The stars are amazing when you can get away from the smog and light pollution you might find this gadget of interest.

 

[John Big Booty]

Can you not use the transfer spreadsheet method to transfer each worksheet into a temp table, where you can then validate the data you are after, prior to shifting it to your working table? Then move to the next worksheet.

 

[speakers_86]

There's going to be a lot of these sheets. Won't that method require compacting after a while?

Edit- Also, How can I use the transferspreedsheet method on all shets?

 

[John Big Booty]

I guess so, but you could automate that pretty easily.

 

[speakers_86]

I got it working, and it's working great. Here is a snippet:

    dim xl as excel.application
    set xl=CreateObject("Excel.Application")
    xl.workbooks.open me.txtPath
    xl.visible=false
    
    for i=1 to xl.ActiveWorkbook.Worksheets.Count
        if nz(xl.worksheets(i).range("C1"))<>" Some valdation Text"
    next

Thats just a snippet so others can see how to do it. Another important method I used (not in the snippet) is

xl.Sheets(i).usedange.rows.count

Hope this helps someone else!

 

[speakers_86]

I got it working, and it's working great. Here is a snippet:

    dim xl as excel.application
    set xl=CreateObject("Excel.Application")
    xl.workbooks.open me.txtPath
    xl.visible=false
 
    for i=1 to xl.ActiveWorkbook.Worksheets.Count
        if nz(xl.worksheets(i).range("C1"))<>" Some valdation Text"
    next

Thats just a snippet so others can see how to do it. Another important method I used (not in the snippet) is

xl.Sheets(i).usedange.rows.count

Hope this helps someone else!

 

[wind20mph]

I have the same Issue but mine is Access97 and excel 97, we haven't migrated to the new msoffice yet, so we have a 3.51 DAO. But It seems fine. I just couldn't get the explanation from which the field EmpNo be placed in an Excel Worksheet Cell C5 and EmpName to be placed in Cell D5. And the subform Entries, to be placed in B10 to N10 and will grow automatically up to 31st rows depends on how many records in the subform.

Any code will do. I cannot link the Excel file since its pre formatted.