Excel Macro Problem - Format in a Loop

[oneillrsm]

Hi,
I am trying to write a macro to extract data from some files. I have ~300 files and I want to take values, always from the same cells, from all of them for comparison. Obviously I'd rather not do this manually...

The file names are ordered like CeO2a_A_B with A and B always increasing by 1. Originally I wrote this:

For x = 4 To 4
Cells(x, 5).Select
ActiveCell.Value = "=CeO2a_1_10210.dat!R172C8"
Cells(x, 6).Select
ActiveCell.Value = "=CeO2a_1_10210.dat!R179C8"
Cells(x, 7).Select
ActiveCell.Value = "=CeO2a_1_10210.dat!R184C8"
Cells(x, 8).Select
ActiveCell.Value = "=CeO2a_1_10210.dat!R188C8"
Cells(x, 9).Select
ActiveCell.Value = "=CeO2a_1_10210.dat!R193C8"
Next x

This works fine for giving me the values for this one file however to get the data from the other files I'd have to copy, paste then manually change the name for each one (10211, 10212, 10213 etc.). To get round this I've put the code in a loop like this:

q = 1
w = 10210

For x = 4 To 23
Cells(x, 5).Select
ActiveCell.Value = "=CeO2a_q_w.dat!R172C8"
Cells(x, 6).Select
ActiveCell.Value = "=CeO2a_q_w.dat!R179C8"
Cells(x, 7).Select
ActiveCell.Value = "=CeO2a_q_w.dat!R184C8"
Cells(x, 8).Select
ActiveCell.Value = "=CeO2a_q_w.dat!R188C8"
Cells(x, 9).Select
ActiveCell.Value = "=CeO2a_q_w.dat!R193C8"
q = q + 1
w = w + 1
Next x

How do I get the macro to reference the right q and w values? I've tried every variation on - "CeO2a" & Format(q) & "_" & Format(w) & ".dat" - that I can think of but it doesn't work.

What is the correct syntax to do this? I suppose that I have to change "ActiveCell.Value" or the formatting of the file name, but I've no idea which.

Thanks a Lot,
John

 

[ukmikeb]

Hi

Like -

q = 1
w = 10210
 
For x = 4 To 23
Cells(x, 5).Value = "=CeO2a_" & q & "_" & w & ".dat!R172C8"
Cells(x, 6).Value = "=CeO2a_" & q & "_" & w & ".dat!R179C8"
Cells(x, 7).Value = "=CeO2a_" & q & "_" & w & ".dat!R184C8"
Cells(x, 8).Value = "=CeO2a_" & q & "_" & w & ".dat!R188C8"
Cells(x, 9).Value = "=CeO2a_" & q & "_" & w & ".dat!R193C8"
q = q + 1
w = w + 1
Next x

btw you don't need to select the cells.

hth

 

[oneillrsm]

Thanks. It works perfectly now

The other thing that I would like to do is to automatically open and close the files that I'll be using. I can do this with:

n = 1
b = 10210

For n = 1 To 20
Workbooks.Open ("CeO2a_" & n & "_" & b & ".dat")
b = b + 1
Next n

Which I place before the code in the above message. I'd then like to close all of the files that I've just opened. I've tried the following but with no success:

n = 1
b = 10210

For n = 1 To 20
Workbooks.Close ("CeO2a_" & n & "_" & b & ".dat")
b = b + 1
Next n

How do I get this to work?

Thanks Again

 

[SumGuy]

Reference the opened workbook and you can close it much easier:

Dim XlWbk as Workbook
 
For n = 1 To 20
    Set XlWbk = Workbooks.Open ("CeO2a_" & n & "_" & b & ".dat")
    
    ' Your code here
    
    XlWbk.Close
Next n

Hope this helps