Creating an Excel Workbook my sources are an access query and multiple workbooks

[RodgerDJr]

All,

I have created a database to track our projects. Each project could be a stand alone project or a child of a parent project. Each project can have many task to track those I have an excel workbook with one sheet for each project. I would like to take all the children task list and roll them into one workbook.

I have been butchering the code I know, I was just trying to get it to work or at least my concept.

Here is my thought process

I need two instances of Excel right?

xlAppMain – New Workbook
xlAppPro - Individual Projects want to copy what is here to the New Workbook.

I know the first cell I want is A2, and the last row I want is H(x).

Well I tried to use:
z = xlAppPro.Worksheets(1).UsedRange.Rows.Count

Well when I created my Template that has created all the Task List in xls I forgot I had some text on row 4000, so unless I want to go through and delete that from some 100 excel sheets I need to look at another option. Even if I do this I have some rows off to the right at least 8. So task may only be 2 or 3 rows.

So I tried this:
With ActiveSheet
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With

For some reason I get row 1 even thought there is data in say A18.

So I went back to an old school method I came up with:

Range("A16384").Select
Selection.End(xlUp).Select
x = ActiveCell.Row

Any ideas?

TIA,
Rodger

 

[SkipVought]

hi,

I have an excel workbook with one sheet for each project

OUCH! Not a sound design, INHO!

Why are you using Excel? Why not store it all in Access?

If you are using Excel, you do not want separate instances of the Excel application. You are already making it more difficult than it needs to be by using "one sheet for each project", and hiding one workbook from another??? For what reason?

Think yer off on the wrong foot, heading in the wrong direction.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[PHV]

What about this ?
z = xlAppPro.Worksheets(1).Range("A2").CurrentRegion.Rows.Count

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886