CODEWITHSUNDEEP
vbaexcel
Merni
Merni

Reputation: 2912

Cant save the worksheet in vba script

I am looping thru all .xls documents in a directory and changing one line in each files component "ThisWorkbook"

Everything works fine, the code opens the files and I can see in run time that the line "test" is inserted into ThisWorkbook. But the save does not work. Is the save function only for sheets? How do I save the changes in the Component?

  excelfile = Dir(path & "*.xls")
  Do While excelfile <> ""
    If excelfile <> "merni.xlsm" Then
        Set wbResults = Workbooks.Open(Filename:=path & excelfile)
        wbResults.Unprotect Password:=""
        DoEvents
        Set codeModule = wbResults.VBProject.VBComponents("ThisWorkbook").codeModule
        With codeModule.InsertLines(3, "test")
        End With
        wbResults.Save
        wbResults.Close
    End If
    excelfile = Dir
  Loop

Upvotes: 0

Views: 760

Answers (1)

Dick Kusleika
Dick Kusleika

Reputation: 33145

This code worked for me

Sub AddTest()

    Dim wb As Workbook
    Dim cm As CodeModule

    Set wb = Workbooks.Open("C:\Users\dick\Book3.xls")
    Set cm = wb.VBProject.VBComponents.Item("ThisWorkbook").CodeModule
    cm.InsertLines 3, "test"
    wb.Save
    wb.Close

End Sub

But with this line

    With cm.InsertLines(3, "test"): End With

It won't event compile. InsertLines is a method and doesn't return an object so I'm pretty sure you can't use that with a With block.

Upvotes: 2

Related Questions