By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,080 Members | 1,855 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,080 IT Pros & Developers. It's quick & easy.

Extracting Excel File items within Access via VBA

P: n/a

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....

Jul 11 '07 #1
Share this Question
Share on Google+
6 Replies


P: n/a
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....

Jul 11 '07 #2

P: n/a
"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

Jul 12 '07 #3

P: n/a
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?

Jul 13 '07 #4

P: n/a
<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

Jul 16 '07 #5

P: n/a
On Jul 16, 2:15 am, "Keith Wilby" <h...@there.comwrote:
<rlntemp-...@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
This worked great, Keith, thank you for this quick response. I
appreciate your help here today.

Jul 17 '07 #6

P: n/a
<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.

Jul 17 '07 #7

This discussion thread is closed

Replies have been disabled for this discussion.