VBA Expected end of statement ERROR

[Paul Lambson]

I created this macro in Microsoft Visual Basic wihin excel and
exported it as a VBS file. The macro runs fine within excel but if I
run it from my desktop I get an end of statement error.

Any Ideas?

Paul

Dim udpatedate As String
Dim myFile As String


Sub OpenBookTest()

Dim myPath As String
Dim myFile As String


myPath = "W:\YIELDMGT\Paul Lambson\SAS"
myFile = "ThanksChart.xls"

Set wbk = Workbooks.Open(Filename:=myPath & "\" & myFile, _
Password:="turkey", WriteResPassword:="turkey", UpdateLinks:=True)
ActiveWorkbook.RefreshAll
ActiveWorkbook.Save
ActiveWorkbook.Close
End Sub

 

[Sam Lambson]

I created this macro in Microsoft Visual Basic wihin excel and
exported it as a VBS file. The macro runs fine within excel but if I
run it from my desktop I get an end of statement error.

Any Ideas?

Paul

Dim udpatedate As String
Dim myFile As String

Sub OpenBookTest()

Dim myPath As String
Dim myFile As String

myPath = "W:\YIELDMGT\Paul Lambson\SAS"
myFile = "ThanksChart.xls"

Set wbk = Workbooks.Open(Filename:=myPath & "\" & myFile, _
Password:="turkey", WriteResPassword:="turkey", UpdateLinks:=True)
ActiveWorkbook.RefreshAll
ActiveWorkbook.Save
ActiveWorkbook.Close
End Sub

So, you don't have access to the excel object natively from VBS, but
you can access it by creating the excel object. I found some code on
creating an object, but I can't say I've ever done it.

Here's the example.
Set objXL = CreateObject("Excel.Application")
Set objWB = objXL.WorkBooks.Open("C:\Scripts\test1.xls")
Set objWS = objXL.ActiveWorkBook.WorkSheets("test1")

Dim CellArray()
ACount = 0
For i = 300 To 350
If objXL.Cells(i, 9).Value = "" Then
CellValue = "Empty"
Else
CellValue = objXL.Cells(i, 9).Value
End If

ReDim preserve CellArray(ACount)
CellArray(ACount) = CellValue
ACount = ACount + 1
Next
objWB.Close
objXL.Quit


from this website http://www.visualbasicscript.com/m_32435/tm.htm

yours,

Sam

 

[Sam Lambson]

so with your code


Dim udpatedate As String
Dim myFile As String

Sub OpenBookTest()

Dim myPath As String
Dim myFile As String

myPath = "W:\YIELDMGT\Paul Lambson\SAS"
myFile = "ThanksChart.xls"

Set objXL = CreateObject("Excel.Application")
Set objWB = objXL.Workbooks.Open(Filename:=myPath & "\" & myFile, _
Password:="turkey", WriteResPassword:="turkey", UpdateLinks:=True)

objWB.RefreshAll
objWB.Save
objWB.Close
objWB.Quit
End Sub

 

[Chip Pearson]

In VBScript, everything is a Variant data type, so you can't use "As
whatever" when declaring variables. Use Dim and nothing else. E.g.

Dim S ' OK
Dim S As String 'Illegal

Cordially,
Chip Pearson
Microsoft MVP
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)