VBA Question

[carl]

I have a some xlsx files located here: J:\Projects\ORF.

Is there a way to extract the 5th row of each file at this location ?

 

[Gord Dibben]

Workbooks(files) don't have rows.

Each workbook is comprised of one or more worksheets............these worksheets
have rows.

You must be more specific with your description.

But yes..............you can extract data from each of the files(workbooks) in
that folder if you know which worksheet contains the required row.

Check out Ron de Bruin's site.

http://www.rondebruin.nl/tips.htm

Browse through the codes you find under his Copy/Paste/Merge Examples.

Note the "merge data from all workbooks in a folder"

Ron has also supplied an Add-in you could download.


Gord Dibben MS Excel MVP

 

[carl]

Workbooks(files) don't have rows.

Each workbook is comprised of one or more worksheets............these worksheets
have rows.

You must be more specific with your description.

But yes..............you can extract data from each of the files(workbooks) in
that folder if you know which worksheet contains the required row.

Check out Ron de Bruin's site.

http://www.rondebruin.nl/tips.htm

Browse through the codes you find under his Copy/Paste/Merge Examples.

Note the "merge data from all workbooks in a folder"

Ron has also supplied an Add-in you could download.

Gord Dibben     MS Excel MVP






- Show quoted text -

Each workbook contains 1 worksheet. I need to extract the 5th row of
the worksheet.

 

[Gord Dibben]

In that case have a look at Ron's code here.

http://www.rondebruin.nl/summary2.htm


Gord

 

[carl]

In that case have a look at Ron's code here.

http://www.rondebruin.nl/summary2.htm

Gord





- Show quoted text -

Thanks. I looked at my path again. Turns out the workbooks are all
located in separate folders.

The code you pointed me too looks like I need to select all the
workbooks before running it - is that correct ?

 

[Don Guillett]

Thanks. I looked at my path again. Turns out the workbooks are all
located in separate folders.

The code you pointed me too looks like I need to select all the
workbooks before running it - is that correct ?

You would need to fully qualify each folder path for each file. You
could, if NOT too many, even use a formula

 

[carl]

You would need to fully qualify each folder path for each file. You
could, if NOT too many, even use a formula- Hide quoted text -

- Show quoted text -

Thanks. Unfortunately, there are 223

 

[Don Guillett]

Thanks. Unfortunately, there are 223

More info perhaps. You have 223 files in ________(how many) folders
and do you know the files in each folder and the sheet in the file to
get the row????????????????

 

[carl]

More info perhaps. You have 223 files in ________(how many) folders
and do you know the files in each folder and the sheet in the file to
get the row????????????????- Hide quoted text -

- Show quoted text -

Thanks for helping.

I have 223 files in 223 folders. Each folder only contains 1 file. All
the files have the same name. Each file (workbook) contains 1 sheet
named "ORF_Charge". In this sheet, the data I am trying to extract is
in row 5.

 

[Gord Dibben]

I have 223 files in 223 folders. Each folder only contains 1 file. All

the files have the same name. Each file (workbook) contains 1 sheet
named "ORF_Charge". In this sheet, the data I am trying to extract is
in row 5.

I sincerely hope you inherited this storage system from a predecessor.

If not, what were you thinking?

Did you ever hear of "shared files" where one master workbook is kept up-to-date
by multiple users?

Can you get a list of all the folders and paths to those folders?

Can you get that list into a worksheet in a new workbook?

We should be able to use a loop to iterate through that list of path/folders and
pull row 5 from each ORF_Change worksheet since all workbooks have the same
name.

The trick is to get into the 223 folders.


Gord

On Wed, 22 Jun 2011 12:45:18 -0700 (PDT), carl <[email protected]> wrote:

 

[Don Guillett]

Thanks for helping.

I have 223 files in 223 folders. Each folder only contains 1 file. All
the files have the same name. Each file (workbook) contains 1 sheet
named "ORF_Charge". In this sheet, the data I am trying to extract is
in row 5.

Might I also suggest that you rename the files to include an
identifier such as the folder name
myfilefolder.xls
or similar. Then put all in ONE folder and then loop thru all files in
that folder.

 

[carl]

I sincerely hope you inherited this storage system from a predecessor.

If not, what were you thinking?

Did you ever hear of "shared files" where one master workbook is kept up-to-date
by multiple users?

Can you get a list of all the folders and paths to those folders?

Can you get that list into a worksheet in a new workbook?

We should be able to use a loop to iterate through that list of path/folders and
pull row 5 from each ORF_Change worksheet since all workbooks have the same
name.

The trick is to get into the 223 folders.

Gord




- Show quoted text -

Thanks again. This file structure was a result of decompressing. I
used winzip to do the decompression. So if I can get the file path
details into a spreadsheet we might be able to do the extract - J:
\Projects\ORF\Meeds\xlsx\005\ORF.xlsx ?

 

[Gord Dibben]

Don's suggestions could make it easier but changing the names of 223 files in
223 folders may be an onerous task.

If you could get J:\Projects\ORF\Meeds\xlsx\005\ and all other paths into a
list a loop through could be done although getting the list may also be onerous.

After all, the workbook name............ORF.xls...........and the worksheet
ORF_Change do not vary from folder to folder so what the code would do is loop
through the path names pulling data from each workbook into a sheet in your
Master workbook


Gord

 

[carl]

Don's suggestions could make it easier but changing the names of 223 files in
223 folders may be an onerous task.

If you could get J:\Projects\ORF\Meeds\xlsx\005\  and all other paths into a
list a loop through could be done although getting the list may also be onerous.

After all, the workbook name............ORF.xls...........and the worksheet
ORF_Change do not vary from folder to folder so what the code would do isloop
through the path names pulling data from each workbook into a sheet in your
Master workbook

Gord





- Show quoted text -

Ok. I have the list of paths in excel.

So with that list in A1:A223, can you show me the code that will go
into each of these files and extract the 5 row of data ?

 

[Gord Dibben]

I will have to work on it...............not as adept at VBA as many of the
others here.

All folders contain a workbook named ORF.xls

All ORF workbooks contain a sheet named ORF_Change

We want row 5 from each sheet to accumulate in a new sheet in a workbook.

The code would reside in only that one workbook.

The trick for me is to loop through A1:A223 to get the changing paths and use
Ron's code to pull from a closed workbook.

http://www.rondebruin.nl/copy7.htm

Help! anybody.


Gord

 

[isabelle]

hi carl,

i suppose column "A" contains data like "J:\Projects\ORF\Meeds\xlsx\005\ORF.xlsx"
you have to add reference Microsoft ActiveX Data Objects 2.8
if "Microsoft.ACE.OLEDB.10.0" is not installed on your PC, just tell us,
there are many others possibilities.


Sub test()
For i = 1 To 223
ReadFile Range("A" & i), "ORF_Charge", "A5:L5" 'adapt range
Next
End Sub

Function ReadFile(Fichier As String, Sh As String, Rgn As String)
Dim Source As ADODB.Connection
Dim Donnees As Variant
Dim Rst As ADODB.Recordset
Set Source = New ADODB.Connection

With Source
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Provider=Microsoft.ACE.OLEDB.10.0;Data Source=" _
& Fichier & ";Extended Properties=""Excel 10.0;HDR=YES;"""
.Open
End With

Donnees = "SELECT * FROM [" & Sh & "$" & Rgn & "]"

Set Rst = New ADODB.Recordset
Set Rst = Source.Execute(Donnees)

Sheets(2).Range("A" & i).CopyFromRecordset Rst 'adapt sheet name or index
Source.Close
Set Source = Nothing
End Function

 

[isabelle]

correction,

xl2002 --> Excel 10.0
xl2007 --> Excel 12.0

 

[Gord Dibben]

Thanks for jumping into this Isabelle.

My plea for help has garnered some interest<g>


Gord

 

[isabelle]

i Gord,

you understand [g] right Gord, it's with pleasure ;-)
i just remember that this can be done much more simply with old tools "Excel4"
to read a single row is more than enough
i know it's ok on XL2002 to 2007 but i do not know for xl2010

Sub test2()
Dim nRow As Integer, nColumn As Integer, n As Integer
Dim sDir As String
nRow = 5
For i = 1 To 1
sDir = Range("A" & i) 'Range("A1:Ax") contains values like "c:\temp\"
n = n + 1
For nColumn = 1 To 256
Sheets(2).Cells(n, nColumn) = ExecuteExcel4Macro _
("'" & sDir & "[MyFile.xls]ORF_Charge'!R" & nRow & "C" & nColumn & "")
Next
Next
End Sub

 

[isabelle]

sorry my fingers are tired a "h" is missing