copy from unopened workbook

[Guest]

Dear All,

I hope you can help with this query. I have inherited a workbook which
copies information from other workbooks. To do this it opens the workbook
(supplying the password), goes to the correct sheet and then copies the
selected range to the first workbook. The problem is that each of the our
workbook it needs to open are over 10Mb each and takes quite a long time to
open.

I wonder if it is possible to copy the information without opening the
workbook? The workbooks to be copied from are password protected.

Also if this is possible, is it possible to apply a filter in the closed
workbook so that only the a certain number of lines are copied across and
also currently the copying takes lines 2 to 10,000 to ensure that all lines
are copied, is it possible to somehow detect the last row of the data? There
are blank lines in the data every so often if this is important.

We have winXp, Office 2000 and all workbooks are password protected.

Thanks very much
Emma

 

[Bob Phillips]

Normally you could use ADO to read the closed workbook, but not if it is
password protected.

If you do open them, you can find the last line of data with

iLastRow = Cells(Rows.Count,"A").End(xlUp).Row

 

[Guest]

Why not just run queries in your VB code ...

Private Sub msubUpdateTable()

DoCmd.RunSQL "DELETE FROM tblMyTable WHERE intID = 9"
DoCmd.RunSQL "UPDATE tblMyTable SET intID = " & txtMyTextBox & " WHERE
intID = 9"

End Sub

Cheers ...