Please someone explain this to me..

[oric]

OK.

I'm trying to write some code that will basically perform the same calculations to a lot of excel files in a folder called "Test Folder". I learned from here that I can use the dir command. Here is my code

Sub Batchtest()
Dim myFile As String, myCurrFile As String
Dim value1
myCurrFile = ThisWorkbook.Name
myFile = Dir("C:\Test Folder\*.xls")
Do Until myFile = ""
Workbooks.Open "C:\Test Folder\" & myFile
Sheets("Nav").Activate
Range("A1").Select
value1 = ActiveCell.Value
Worksheets(2).Range("A1").Value = value1
ActiveWorkbook.Save
ActiveWorkbook.Close
myFile = Dir
Loop

End Sub

All I'm doing on this test run is selecting the value of "A1" which is in the sheet called "Nav" and copying it to sheet2 of the same workbook. After I'm done I save it, close it, move on to next file. The problem is that it doesn't want to put the value on sheet 2.

BUT when I use this code on just one excel file it works.

Sub test()
Dim value1
Sheets("Nav").Activate
Range("A1").Select
value1 = ActiveCell.Value
Worksheets(2).Range("A1").Value = value1
ActiveWorkbook.Save
ActiveWorkbook.Close
End Sub

What is the differece? The guts are pretty much the same but my batch process doesn't like to copy that value to the second sheet.

I'm still pretty newb to this so pardon if this is really easy.
thanks.

 

[Richard Schollar]

Are you actually getting an error message with the code you are running? If not, it may be correctly running the code (including extracting the value of A1 on sheet "Nav" and copying it to A1 on Worksheets(2).Range("A1")). However, specifying the sheet via its index value is very unreliable (it doesn't necessarily follow the order the sheets are arranged, so it is possible that Worksheets(2) is sheet "Nav") - it would be better to specify an actual sheet (ie Worksheet("Sheet2") or whatever) assuming the sheet is always named the same in each file.


Best regards

Richard

 

[oric]

Thanks for the input.

I'm not getting an error message. It's just not writing the value to the second sheet whereas the other code (non-batch) writes it just fine. I'll try and specify the sheet via "Sheet2" nomenclature. Though I don't see why in one case it works and in the other it doesn't.

 

[acw]

Hi

Ran the batchtest code making sure that sheet Nav was the first sheet (index 1) and sheet2 existed, and it ran OK for me. Transferred the data from nav!a1 to sheet2!a1.

What do you have in nav!a1? Is it a formula or a constant?


Tony

 

[oric]

Thanks for running the code.

For some reason I am still not able to get it to work. I am positive my first sheet is called "Nav". I even tried to change it back to "Sheet1" and it still didn't work. Also I'm sure that my second sheet is "Sheet2". I explicitly stated it as "Sheet2" in the code as well but no go.

I just can't figure out why it will work when I run the non-batch code but not the batch one.

Also.. the contents Nav!A1 is just a constant.

Here is exactly what I did.

Made "Test Folder" in C:
Made 3 excel files that had the first sheet labeled (in this case "Nav"). The contents of A1 in all the files were just some numbers (1, 2, etc.).
Put these files in the Test folder.
Tried to run the batch code shown above.
Code runs and I see it openning and closing the files.
When I open the files all I see is the original data. There is no data in Sheet2.

When running the non-batch code I have a file already openned. The code does its thing and then closes that file. When I open it Sheet2 has the appropriate data in it.

weird....

 

[oric]

OK...OK...

I think I must have been on crack or something.

I tried it again and it worked. I don't know what happenned. Maybe it was my voiced threats to the computer...

Thanks for your help.