Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel,microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel,microsoft.public.excel.programming
|
|||
|
|||
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 | | |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel,microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Target cell reference moves when target is cut and pasted | Excel Discussion (Misc queries) | |||
q; Writing to files | Excel Discussion (Misc queries) | |||
Writing Text Files | Excel Programming | |||
writing to txt files | Excel Programming | |||
Consolidating multiple files into 1 target | Excel Programming |