why it not writing to target files?
 

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

why it not writing to target files?
 

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
-------------------
ActiveWorkbook.Worksheets("Sheet1").Range("O1").Va lue = "Martyn"
-------------------


--
Message posted from http://www.ExcelForum.com

why it not writing to target files?
 

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
"Martyn" wrote in message
...
|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
|
|

why it not writing to target files?
 

Thank you BrianB,
now it works perfectly...

"BrianB " wrote in message
...
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:
--------------------
ActiveWorkbook.Worksheets("Sheet1").Range("O1").Va lue = "Martyn"
--------------------



---
Message posted from http://www.ExcelForum.com/



---
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

why it not writing to target files?
 

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


"Jim Rech" wrote in message
...
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
"Martyn" wrote in message
...
|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
|
|




---
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