Read cell values from unopened workbooks

[InfoH]

Hi all,

I am having trouble writing a macro and quite frankly not even sure where I should be starting!

Basically what I need is..

I have a list of excel filenames (inc filepaths) which are all exactly the same format

now I need a macro to open each one, and extract a certain cell value onto a central workbook

is there any simple way to do this?

thanks in advance!

 

[Andrew Poulsom]

Here's an example:

Sub Test()
    Dim Rng As Range
    Dim Cell As Range
    Dim Folder As String
    Dim FileName As String
    Application.DisplayAlerts = False
    With ActiveSheet
        Set Rng = .Range("A1:A5")
        For Each Cell In Rng
            With Cell
                Folder = Left(.Value, InStrRev(.Value, "\"))
                FileName = Replace(.Value, Folder, "")
                With .Offset(0, 1)
                    .Formula = "='" & Folder & "[" & FileName & "]Sheet1'!$A$1"
                    .Value = .Value
                End With
            End With
        Next Cell
    End With
    Application.DisplayAlerts = True
End Sub

In the Formula change the worksheet and range references to suit.

 

[InfoH]

thanks for the code, little confused how it works tho?

basically in column A i've got a list of filepaths & filenames
which i need the macro to access one by one and extract data from and put that data into column B

 

[Andrew Poulsom]

That's what the macro does. It inserts a formula in column B that references A1 on Sheet1 in the workbook in column A. Did you try it?

 

[InfoH]

ah yeah thanks that works great

another similar thing i needed to do which i think may be a lot more difficult is open each workbook
and on sheet 2 of the central spreadsheet it starts to create a list
but the list needs to contain 2 peices of information (just to make it more awkward!)

for each workbook i'd need it to put:

N7 in A Column
and
R7 in B Column

but it'll need to go down.. N7:N10, N12:N15 etc.. (skips 1 line every 4 lines, same for R)

then it does same thing for columns

T7 & X7 etc..

is this going to be too complex?

 

[Andrew Poulsom]

I'm sure it's possible, but can you be more specific about what you want in what cells?

 

[InfoH]

basically i need it to go through each file

and take values from H column and put them into column A on sheet 2 (of the central spreadsheet)
and column L onto the B column

it'd need to be like this:

A B
H L
N R
T X
Z AD
AF AJ
AL AP

so anything on left side to go into column A and anything on right to go into column B
however the 2 columns would need to match up with each other

sorry i'm a pain! lol

 

[Andrew Poulsom]

So you want H7 from the first workbook, N7 from the second workbook, T7 from the third workbook etc?

 

[InfoH]

no in effect all the workbooks are identical except for the data

i need entire columns

H,N,T,Z,AF,AL to be put into column A
and
L,R,X,AD,AJ,AP to be put into column B

for all workbooks

but when they are copied it would need to copy the same number of cells as the data will need to match

ie. what is in H7 is in A1 and what is in L7 is in B1
and so on

 

[Andrew Poulsom]

Sorry, how can you put 6 entire columns into 1 column?