Syntax problem - strCode

[herby_one]

Hi,
I'm trying to create statements for a macro in Excel. However am getting several errors. This code works fine:

' Add a macro to the module...
Dim strCode As String
strCode = _
"sub MyMacro()" & vbCr & _
" msgbox ""Generated macro"" " & vbCr & _
"end sub"
xlmodule.CodeModule.AddFromString strCode


When I try to change lines I get errors. This is the code:

"sub MyMacro()"
" Workbooks.Open Filename:="C:\Apps\emptyprops.xls"
" Rows("1:1").Select_"
" Selection.Insert Shift:=xlDown_"
" end sub"
xlmodule.CodeModule.AddFromString strCode

thanks in advance

 

[KenHigg]

Is this in the wrong forum? Anyway, I think you may have missed the '& 's and the underscores:

"sub MyMacro()" & _
" Workbooks.Open Filename:="C:\Apps\emptyprops.xls" & _
" Rows("1:1").Select_" & _
" Selection.Insert Shift:=xlDown_" & _
" end sub"
xlmodule.CodeModule.AddFromString strCode

???
kh

 

[herby_one]

thanks for the reply. Sorry, probably should have posted in the macro's forum.

Still getting an error with the underscores. Didn't like the path I was giving so tried putting into a variable. Keep getting a 'Expected End of Statement error' and highlights Rows("1:1")


loc = "C:\Apps\"
file = "emptyprops.xls"
filename = loc + file

strCode = _
"sub MyMacro()" & _
" Workbooks.Open Filename:=filename & _
" Rows("1:1").Select_" & _
" Selection.Insert Shift:=xlDown_" & _
" end sub"

any suggestions?

 

[KenHigg]

Sorry, I'm lost - I was thinking more like the excel forum...

kh