Extracting Excel File items within Access via VBA

[rlntemp-gng]

I need to extract information from some Excel files but am stuck with
part of it:

As an example, I have the following Excel File that has 3 tabbed
sheets:
FileName: C:\ExcelData\JulyFile1.xls
SheetName: Sheet1-07032007 RowCount: 325 ColumnCount: 15
SheetName: Sheet2-07102007 RowCount: 450 ColumnCount: 25
SheetName: Sheet3-07172007 RowCount: 625 ColumnCount: 45
(I have 7 other Excel files with similar attributes)

I have code to open an Excel object, but am having trouble extracting
some other items from these files.

In my Access app, I need to loop through a series of Excel Files in a
folder and need to retrieve the filename if it is indeed an .xls
filetype.
Within each file, I need to retrieve the names of the sheet tabs, as
well as the row count and column count and column names within each
sheet.

Does someone have a code sample of how these items can be extracted
from an Excel file via VBA?

Thanks....

[Steve]

Look at the Transferspreadsheet function in the Help file for retrieving
data from Excel. Look at the Dir function in the Help file for getting file
names.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
re******@pcdatasheet.com


<rl*********@yahoo.comwrote in message
news:11*********************@22g2000hsm.googlegrou ps.com...

>
I need to extract information from some Excel files but am stuck with
part of it:

As an example, I have the following Excel File that has 3 tabbed
sheets:
FileName: C:\ExcelData\JulyFile1.xls
SheetName: Sheet1-07032007 RowCount: 325 ColumnCount: 15
SheetName: Sheet2-07102007 RowCount: 450 ColumnCount: 25
SheetName: Sheet3-07172007 RowCount: 625 ColumnCount: 45
(I have 7 other Excel files with similar attributes)

I have code to open an Excel object, but am having trouble extracting
some other items from these files.

In my Access app, I need to loop through a series of Excel Files in a
folder and need to retrieve the filename if it is indeed an .xls
filetype.
Within each file, I need to retrieve the names of the sheet tabs, as
well as the row count and column count and column names within each
sheet.

Does someone have a code sample of how these items can be extracted
from an Excel file via VBA?

Thanks....

[Keith Wilby]

"Steve" <so***@private.emailaddresswrote in message
news:mV*****************@newsread1.news.pas.earthl ink.net...

Look at the Transferspreadsheet function in the Help file for retrieving
data from Excel.

Absolute drivel. TransferSpreadsheet is a method and not a function but
won't do what the OP wants anyway. Please stop posting this kind of crap
just to get your sig line aired. On second thoughts, keep posting ... it
shows the whole community that you haven't a clue.

To the OP:

You need to declare the workbook and each sheet as objects then use code to
interrogate them. Here's a code extract from one of my apps which should
get you started.

Keith.
www.keithwilby.com

Sub test()

Dim objXL As Excel.Application
Dim objWkb As Excel.Workbook
Dim objSht As Excel.Worksheet
Dim objRange As Excel.Range

Set objXL = New Excel.Application
Set objWkb = objXL.Workbooks.Open("C:\ExcelData\JulyFile1.xls")
Set objWkb = objXL.ActiveWorkbook

objXL.Visible = False

Set objSht = objWkb.Sheets(1)
Debug.Print objSht.Name

Set objRange = objSht.Range("A1").CurrentRegion
Debug.Print objRange.Columns.Count

objXL.Quit
Set objSht = Nothing
Set objWkb = Nothing
Set objXL = Nothing

End Sub

[rlntemp-gng]

Set objSht = objWkb.Sheets(1)

Debug.Print objSht.Name

Thank you Keith for your reply.

Is there a way to loop through an .xls file and get the qty and names
of all sheets contained therein?

[Keith Wilby]

<rl*********@yahoo.comwrote in message
news:11*********************@m3g2000hsh.googlegrou ps.com...

>Set objSht = objWkb.Sheets(1)
Debug.Print objSht.Name

Thank you Keith for your reply.

Is there a way to loop through an .xls file and get the qty and names
of all sheets contained therein?

Dim i As Integer
For i = 1 To objWkb.Sheets.Count
Debug.Print Sheets(i).Name
Next i

HTH - Keith.
www.keithwilby.com

[rlntemp-gng]

On Jul 16, 2:15 am, "Keith Wilby" <[e-mail]:

<[e-mail] in message

news:11*********************@m3g2000hsh.googlegrou ps.com...

Set objSht = objWkb.Sheets(1)
Debug.Print objSht.Name

Thank you Keith for your reply.

Is there a way to loop through an .xls file and get the qty and names
of all sheets contained therein?

Dim i As Integer
For i = 1 To objWkb.Sheets.Count
Debug.Print Sheets(i).Name
Next i

HTH - Keith.www.keithwilby.com

This worked great, Keith, thank you for this quick response. I
appreciate your help here today.

[Keith Wilby]

<rl*********@yahoo.comwrote in message
news:11*********************@q75g2000hsh.googlegro ups.com...

>
This worked great, Keith, thank you for this quick response. I
appreciate your help here today.

No problem, glad to help.

Keith.