VBscript to save excel in Server 2008R2

[bigbillybob]

i volunteered to write a script at work and it works on my test environment but on 2008 r2 server doesn't work.
i get a "cannot use parentheses when call a sub error"

i looked at other solutions and tried to implement them by removing the ones in question but still a no go.

Module Module1

    Sub Main()

        Dim app, objworkbook

        app = CreateObject("Excel.Application")

        app.Visible = True 

        With app

            .workbooks.Open("c:\test\macros.xlsm") ' the file with macro
            .Visible = True
            .workbooks.OpenText("C:\test\test.txt", _
                      , , , , , , , , , True, "~") ' file that needs to be updated
            .Visible = True
        End With

        objworkbook = app.Workbooks.OpenText("C:\test\test.txt")

        app.Run("'macros.xlsm'!rowcount")
        With app 
            .ActiveWorkbook.SaveAs(("c:\testsave\Test_" & Year(Now) & "-" & Month(Now) & "-" & Day(Now) & ".txt"))
            .ActiveWorkbook.Close(True)
            .Quit()
        End With
    End Sub

End Module

any help would be greatly appreciated.

 

[bigbillybob]

I got rid of the part that was causing the error now, it states "Expected statement"
when at the

End Module

part.

 

[Geates]

Is your test environment 2008 R2?
Are the versions of Excel in you test on prod environment the same?

I ask because the wscript versions differ on 2008 R2 and the Excel.Application object definition may be different between versions of excel.

I was getting the same error. Then I took out the parenthesis from [tt].workbooks.OpenText[/tt] and there were no further complaints about parenthesis.

[s]Module Module1[/s]

    [s]Sub Main()[/s]

        Dim app, objworkbook

        [highlight #FCE94F]set[/highlight] app = CreateObject("Excel.Application")

        app.Visible = True 

        With app

            .workbooks.Open("c:\test\macros.xlsm") ' the file with macro
            .Visible = True
            .workbooks.OpenText "C:\test\test.txt", , , , , , , , , , True, "~" ' file that needs to be updated
            .Visible = True
        End With

        objworkbook = app.Workbooks.OpenText("C:\test\test.txt")

        app.Run("'macros.xlsm'!rowcount")
        With app 
            .ActiveWorkbook.SaveAs(("c:\testsave\Test_" & Year(Now) & "-" & Month(Now) & "-" & Day(Now) & ".txt"))
            .ActiveWorkbook.Close(True)
            .Quit()
        End With
    [s]End Sub[/s]

[s]End Module[/s]

-Geates

http://ns7.webmasters.com/caspdoc/html/vbscript_language_reference.htm

 

[bigbillybob]

Thank you Geates!
I was following the manual in regards to writing the statement, and by removing the striked sections in question and adding set, made it work.

quick question, by removing the sub can i always do that and add the set as a replacement?

 

[Geates]

I don't think so, the two are related.

I got rid of the sub ... just because. I included [tt]set[/tt] because it is required when creating an object. Otherwise, depending on the object definition being called, it returns the would be object title

consider the following

does not create and object:

app = CreateObject("Excel.Application")
app.visible = true  '<-- Error occurs because we are referencing an [i]object.property[/i] on a non-object

creates an object:

[COLOR=#EF2929][b]set[/b][/color] app = CreateObject("Excel.Application")
app.visible = true  '<-- No error occurs because we are using an [i]object.property[/i] on an object

-Geates

http://ns7.webmasters.com/caspdoc/html/vbscript_language_reference.htm