VBA Expected end of statement ERROR
 

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

VBA Expected end of statement ERROR
 

On Nov 18, 10:42*am, Paul Lambson wrote:
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

VBA Expected end of statement ERROR
 

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

VBA Expected end of statement ERROR
 

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)



On Tue, 18 Nov 2008 09:42:08 -0800 (PST), Paul Lambson
wrote:

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