why it not writing to target files?

[Martyn]

I have modified a code from this NG to help me copy my nick into a number of
*.xls files in a spesific DIR. But when activated it only copies my nick to
the file where the macro resides and not on the "target" files. I know I'm
doing something wrong here with this "active document" part, but can't
figure what the correct syntax should be. Any help is appreciated...
--------------------------------
Sub Insert()
Dim mesaj As String
Dim FilesArray() As String, FileCounter As Integer
Dim FName As String, LoopCounter As Integer
FName = Dir("c:\my documents\test\*.xls")
Do While FName <> ""
FileCounter = FileCounter + 1
ReDim Preserve FilesArray(1 To FileCounter)
FilesArray(FileCounter) = FName
FName = Dir()
Loop
If FileCounter > 0 Then
Application.ScreenUpdating = False
For LoopCounter = 1 To FileCounter
Workbooks.Open "c:\my documents\test\" &
FilesArray(LoopCounter), False
With ActiveSheet
Sheet1.Range("O1").Value = "Martyn"
ActiveWorkbook.Save
ActiveWorkbook.Close
Next
End If
End Sub

 

[BrianB]

The code as written would not run at all because :-

Code
-------------------
With ActiveSheet
-------------------

(has not been used correctly and) you have missed out "End With" whic
would stop compiling before the macro runs.

Delete this line and (instead of Sheet1.Range("O1").Value = "Martyn")

use :-


Code

 

[Jim Rech]

Get rid of "Sheet1". This is a "code name" that refers only to the project
running the code.

ActiveSheet.Range("O1").Value = "Martyn"
ActiveWorkbook.Save
ActiveWorkbook.Close


--
Jim Rech
Excel MVP
|I have modified a code from this NG to help me copy my nick into a number
of
| *.xls files in a spesific DIR. But when activated it only copies my nick
to
| the file where the macro resides and not on the "target" files. I know I'm
| doing something wrong here with this "active document" part, but can't
| figure what the correct syntax should be. Any help is appreciated...
| --------------------------------
| Sub Insert()
| Dim mesaj As String
| Dim FilesArray() As String, FileCounter As Integer
| Dim FName As String, LoopCounter As Integer
| FName = Dir("c:\my documents\test\*.xls")
| Do While FName <> ""
| FileCounter = FileCounter + 1
| ReDim Preserve FilesArray(1 To FileCounter)
| FilesArray(FileCounter) = FName
| FName = Dir()
| Loop
| If FileCounter > 0 Then
| Application.ScreenUpdating = False
| For LoopCounter = 1 To FileCounter
| Workbooks.Open "c:\my documents\test\" &
| FilesArray(LoopCounter), False
| With ActiveSheet
| Sheet1.Range("O1").Value = "Martyn"
| ActiveWorkbook.Save
| ActiveWorkbook.Close
| Next
| End If
| End Sub
| ---------------------------------
| TIA
| Martyn
|
|
|
| ---
| Outgoing mail is certified Virus Free.
| (Giden posta virüssüz olarak belgelendi.)
| Checked by AVG anti-virus system (http://www.grisoft.com).
| Version: 6.0.710 / Virus Database: 466 - Release Date: 23.06.2004
|
|

 

[Martyn]

Thank you BrianB,
now it works perfectly...

The code as written would not run at all because :-

Code:
--------------------
With ActiveSheet
--------------------

(has not been used correctly and) you have missed out "End With" which
would stop compiling before the macro runs.

Delete this line and (instead of Sheet1.Range("O1").Value = "Martyn")

use :-


Code:

 

[Martyn]

Jim,
Thanks a lot. Both BrianB and your suggestions help me to solve the
problem...