Open an Excel File and Run a Macro Q

[1916]

I am not literate in VB but have being looking on the web to try and
automate a process. What I wish to do is

1) Open MS Outllook (if it is not already open)
2) Open an Excel file
3) Run a Maco within this file)
4) Save the opened Excel file and close it
5) Leave MS Outlook & Excel applications opened

From my look I have found the following and adapted it (saved as *.vbs), but it doesn't fully work. Could anyone assist me in achieving
1-5 above?

Thanks


Dim objXL
Set objXL = CreateObject("Excel.Application")
on error resume next
With objXL
.Workbooks.Open ("F:\User\My Documents\Reports\Daily Sales.xls")
.Run "Daily Sales.xls!copy_paste"
.Quit
End With
Set objXL = Nothing

[RobDog888]

What do you mean by "doesnt fully work"?

Is the problem your macro or ???

[westconn1]

first take out on error resume next, this will allow the errors to break and you can see what causes an error

is "F:\User\My Documents\Reports\Daily Sales.xls" a valid folder location, or should user be the current user?

[1916]

okay, I took out the line 'on error resume next' and a Windows script host dialog box appears, it details:-

Line5
Char5
Error: The macro .... copy_paste cannot be found
Code 800A03EC

The macro is correct as when I go into the Excel file its there. The location is also valid as I've copy from Windows Explorer the path

Thanks

[westconn1]

maybe it is trying to run the macro before excel has opened, perhaps a pause before colling the macro

[1916]

How would I enter a pause? I've tried Pause 3 and Sleep 300 after the line Set objXL = CreateObject("Excel.Application") but it errors on it

[westconn1]

you can use sleep, but it is an api call so you would need to declare the api function

to test i just used a loop, not very efficient, but ..........
for i = 1 to 100000
next

[anhn]

This code (untested) may help you to do the task:
(I remove With ... End With as we may confuse between objects.)

Sub RunXLMarcro()
   Dim objOL As Object
   Dim objXL As Object
   Dim objWb As Object
   
   On Error GoTo RunXLMacro_Error
   
   Set objOL = GetObject("Outlook.Application")
   Set objXL = CreateObject("Excel.Application")
   objXL.Visible = True
   
   '-- assumed that the file "F:\User\My Documents\Reports\Daily Sales.xls" exists
   Set objWb = objXL.Workbooks.Open("F:\User\My Documents\Reports\Daily Sales.xls")
   
   '-- you have to make sure that marcro "copy_paste"
   '   is in a Module and declared as : Public Sub copy_paste(...)
   objXL.Run "Daily Sales.xls!copy_paste"
   
   '-- close workbook and save changes
   objWb.Close SaveChanges:=True
   Set objWb = Nothing
   
   objXL.Quit '-- remove this line if you want to keep Excel Open
   Set objXL = Nothing
   Set objOL = Nothing
   
   Exit Sub

RunXLMacro_Error:
   MsgBox Err.Number & " : " & Err.Description
End Sub

[1916]

Thanks guys, I've copied the code exactly as is and the following error appaers

Line2
Char14
Error: Expected end of Statement
Code 800A0401
Source MS VBScript Compilation Error

Sorry for such dumb knowledge, file Path is correct and the macro is in a Public sub

[CDRIVE]

How would I enter a pause? I've tried Pause 3 and Sleep 300 after the line Set objXL = CreateObject("Excel.Application") but it errors on it

'add a delay time to the current time and waits for the current
'time to catch up.
Private Sub Sleep(ByVal Delay As Single)
    Dim x As Single
    x = Timer + Delay
    Do While x > Timer
        DoEvents
    Loop
End Sub 

Now you can call the 'Sleep n' Sub anywhere in your code. Just like in QB4.5.
Sleep 1 ' = 1 sec
Sleep .5 ' = 1/2 sec (500mS)
Sleep .1 ' = 100mS

If your concerned that the 'Sleep' word can be confused with the Sleep API (which suspends the whole OS while it's executing) then change the Sleep sub to:

Private Sub Snooze(ByVal Delay As Single) 

or anything you like, that VB won't recognize as a Reserved word. I keep Sleep for the nostalgia.

[westconn1]

i did test the original code, just changing the workbook and macro names, the code worked ok, once i put the delay in, any loop or using the sleep api for a long enough pause should fix the problem

[anhn]

Thanks guys, I've copied the code exactly as is and the following error appaers

Line2
Char14
Error: Expected end of Statement
Code 800A0401
Source MS VBScript Compilation Error

Sorry for such dumb knowledge, file Path is correct and the macro is in a Public sub

As you said "I've copied the code exactly as is" then I think the error comes from your copy_paste Sub.
Have you test copy_paste?
Post the code of that Sub here.

I don't think you need a delay.
The .Run command is only executed after the Workbook is fully opened.

In case you want a simple delay, you can use this few lines:

Dim t as Single
'... ...
t = Timer
Do While (Timer - t) < 3 '-- wait here for 3 seconds
   DoEvents
Wend

[1916]

See below, its pretty simple, just refresh's data from an Access table and calls a further macro (is the 2nd macro within the main macro the cause?). All of below is within a standard module



Public Sub Copy_Paste()
Application.ScreenUpdating = False
Sheets("Sales").Select
Selection.QueryTable.Refresh BackgroundQuery:=False

PostMonday

Sheets("Input").Select
Range("B5").Select
Application.ScreenUpdating = True

End Sub

[anhn]

Try to remove 3 lines below to see what happens: (I never use Outlook.)

   Dim objOL As Object
   '...
   Set objOL = GetObject(,"Outlook.Application") '<-- this line causes error
   '...
   Set objOL = Nothing

What is "PostMonday"?

[1916]

I removed the 3 lines and get the same error as reported above i.e

Line2
Char14
Error: Expected end of Statement
Code 800A0401
Source MS VBScript Compilation Error

I know the macro itself works as I can initate manually (has done for 12 mths +)

PostMonday just copies and paste data within the xls file to an area within the same file

[anhn]

"Error: Expected end of Statement" is a syntax error some where in your VB project.
If posible, can you post full project.

You can add Stop command at some places and try to figure out which part of code causes error.

[1916]

Thanks for your interest. I could e-mail the file to you if that makes it easier

[westconn1]

when you are writing code to run as a script, it is much harder to test, the only way to see what is happening is to put mesageboxes all through the code or write to a log file so you can see how far the script has proceeded, especially if it calls subs or functions as it will then often return to a previous point in the code

some times it is of value to copy the script into a sub in an excel module as it will find errors much easier, though of course some things don't work right when not run by the wscript host

zip your script and post here if you want

[1916]

Am I correct then in assuming that the code written in VB script must also work in Excel and vice versa?

I thought VB script would 'get me to the point of Excel' then my macro's would run as normal in Excel, then the VB script would get me 'back out of Excel'.

Instead of calling the Excel macro within the VB script should I not just show the full code within the VB script. If so I'll post a stripped down version and post here

Thanks

[anhn]

If it's difficult for you, I suggest you can add some different MsgBox() in some parts of the script, try to narrow down which part that causes problem step by step.

[westconn1]

most vb script will run in an excel code module, but much general vb code will not run in VBS, once you have an excel object you can do most anything you want within excel as long as it does not use vba functions, constants etc that are not available in VBS, remember that all xl constants are not valid outside excel

it is difficult for others to test your code if you automate a program that they don't have installed, such as outlook
i tested the code you posted for opening excel, and that part works ok, i have no idea what is in your macro, so i can't say whether the macro failed to run, or errored out from some code, this is why you need to put an entry to a lof file every few lines so you can tell how far the code ran, as it is possible that the code will error you need to close the logfile after every entry, then reopen and add to the end each time

[1916]

I'll try and break down what I'm trying to do:

1) Open MS Outllook (if it is not already open)
2) Open an Excel file with path : ("F:\User\My Documents\Reports\Daily Sales.xls")
3) Run a Maco within this file (called RefreshData - see code below)
4) Save the opened Excel file and close it
5) Leave MS Outlook & Excel applications opened

The macro/code below works when I'm in Excel but using the VB script supplied, it errors

My ultimate aim is to automate a process that I have to do 7 x 52, so I'm trying to save myself having to work weekends!

Public Sub RefreshData()
Application.ScreenUpdating = False
Sheets("Sales Mix").Select
Selection.QueryTable.Refresh BackgroundQuery:=False

PostMonday ' code for this see below

Sheets("Input").Select
Range("B5").Select
Application.ScreenUpdating = True

End Sub


Sub PostMonday()
Application.ScreenUpdating = False
Dim sh As Worksheet
Dim rng As Range, rng1 As Range
Dim cell As Range, dt As Date
Set sh = Worksheets("Input")
dt = sh.Range("D6").Value
With Worksheets("YTD")
Set rng = .Range(.Cells(7, 1), .Cells(7, 1).End(xlDown))
End With
For Each cell In rng
If cell.Value2 = CLng(dt) Then
Set rng1 = cell
Exit For
End If
Next
If rng1 Is Nothing Then
MsgBox Format(dt, "dd-mm-yy") & " was not found"
Exit Sub
End If
sh.Range("D833").Copy
rng1.Offset(0, 2).PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Worksheets("YTD").Select
Range("C7").Select


Worksheets("Input").Select
Range("B5").Select
Application.CutCopyMode = False


End Sub

[anhn]

I am not expert on VBscript so I guess.
You put the code of Sub RunXLMarcro() in VBscript.
The syntax of Dim in VBscript does not have "As ...".
Try remove "As Object" to see what happens:

   Dim objOL 
   Dim objXL 
   Dim objWb

[westconn1]

Sheets("Sales Mix").Select
Selection.QueryTable.Refresh BackgroundQuery:=False

change this to

Sheets("Sales Mix").QueryTable.Refresh BackgroundQuery:=False

working with a selection is likely to cause problems, only use select if you want to leave the focus in a particular position
also all the ranges in the refesh data sub should address the specific sheet, or set a sheet object, while ths code will work within excel it will be at least unreliable when run from elsewhere