Active Spreadsheets.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

is it possible to find out (Before import) what datasheets in an excel file
contain data.

In addition can you find the first active coloum & row

also the last coloum & row of an active sheet
 
It depends what you mean by "data" and "active". In general you can do
something like this air code, which relies on Excel's UsedRange
property:

Dim oBook As Excel.Workbook
Dim oSheet As Excel.Worksheet

Set oBook = GetObject("C:\Folder\File.xls")

For Each oSheet in oBook.Worksheets
With oSheet
Debug.Print .Name, .UsedRange.Address, _
.UsedRange.Cells(1,1).Formula
End With
Next

Set oSheet = Nothing
oBook.Close False
Set oBook = Nothing

A sheet whose UsedRange is $A$1 and with no data in $A$1 is empty.

However, Excel's idea of a UsedRange isn't always the same as yours or
mine. You may need to run code to reset the UsedRange on each sheet
before you use it: see
http://www.mvps.org/dmcritchie/excel/lastcell.htm#resetall for ideas.
 
John I am working on access 2000 & it does not like dim obook as excel.workwork

Urgent Help pls
 
Thanks John.

but my database does not like "dim obook as excel.workbook"

can u help please urgently
 
You need to set a reference (Tools|References) to the Microsoft Excel X
Object Library (the value of X depends on the version of Office you are
using.
 
There doesn't appear to be anything in your code that requires Early
Binding, John. Why not simply change

Dim oBook As Excel.Workbook
Dim oSheet As Excel.Worksheet

to

Dim oBook As Object
Dim oSheet As Object

and use it without a reference?

Ok, I'll admit it: I haven't tested. I can't guarantee that Set oBook =
GetObject("C:\Folder\File.xls") will work. If it doesn't, though, it's
simple to add another

Dim xlApp As Object

and use

Set xlApp = CreateObject("Excel.Application")
Set oBook = xlApp.Workbooks.Open(strFile)
 
Hi Doug,

Intellisense is the only reason.

There doesn't appear to be anything in your code that requires Early
Binding, John. Why not simply change

Dim oBook As Excel.Workbook
Dim oSheet As Excel.Worksheet

to

Dim oBook As Object
Dim oSheet As Object

and use it without a reference?

Ok, I'll admit it: I haven't tested. I can't guarantee that Set oBook =
GetObject("C:\Folder\File.xls") will work. If it doesn't, though, it's
simple to add another

Dim xlApp As Object

and use

Set xlApp = CreateObject("Excel.Application")
Set oBook = xlApp.Workbooks.Open(strFile)
Click to expand...
 
Back
Top