PDA

View Full Version : Auto open file at fixed length of time



vishwakarma
07-25-2011, 09:29 PM
Hello to All!!

Recently i had an requirement where i need to pull data from a closed workbook. I did some research and found a code from the website(link pasted below) :-

http://www.ozgrid.com/VBA/ExtractFromClosedWorkbook.htm

The code is just what I wanted; but now I have a new requirement. I need to auto open the file at some fix interval of time and after the macro is excecuted I want to close it automatically.


Please help, I have been looking for the solution for the past few days now but not able to achieve the desired result.

Thanks,

Bob Phillips
07-26-2011, 12:34 AM
Why not just create a VBScript file to do what you want, and schedule it from Windows?

vishwakarma
07-26-2011, 01:23 AM
How can we do that?

And is there no way by which we do it through macros?



Thanks,

Bob Phillips
07-26-2011, 02:46 AM
VBScript using VB, so it is effectively macros. The beauty is there is no need to drive it from within Excel, you can create a new instance of Excel from the script.

vishwakarma
07-27-2011, 08:28 PM
Hi xld,

can you please provide a sample, on how we can do that, so far i've created a macro which is opening and closing the file after saving it but it is not effective as it sometimes breaks down.

help appreciated...


Thanks a lot..

vishwakarma
07-27-2011, 08:37 PM
below is the code, if you guys want to refer and provide me some help :help

In the Private Module of "ThisWorkbook"

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Application.OnTime dTime, "PullInSheet1", , False

End Sub

Private Sub Workbook_Open()

Application.OnTime Now + TimeValue("00:00:01"), "PullInSheet1"

End Sub



And Code in a Standard Module

Public dTime As Date
Sub PullInSheet1()

dTime = Now + TimeValue("00:00:11")
Application.OnTime dTime, "PullInSheet1"

'''''''''''''''''''''''''''''''

'Written By OzGrid Business Applications

'www.ozgrid.com



'Pulls in all data from sheet1 of a closed workbook.

''''''''''''''''''''''''''''''''


Dim AreaAddress As String

Application.EnableCancelKey = xlDisabled

'Clear sheet ready for new data

Sheet1.UsedRange.Clear

'Reference the UsedRange Address of Sheet1 in the closed Workbook.

Sheet1.Cells(1, 1) = "= 'C:\Users\MVishwakarma\Desktop\AR\" & "[Current Month Salesboard for Video Wall_Data.xlsm]Sheet2'!RC"

'Pass the area Address to a String

AreaAddress = Sheet1.Cells(1, 1)

With Sheet1.Range(AreaAddress)

'If the cell in Sheet1 of the closed workbook is not empty the pull in it's content, else put in an Error.

.FormulaR1C1 = "=IF('C:\Users\MVishwakarma\Desktop\AR\" & "[Current Month Salesboard for Video Wall_Data.xlsm]Sheet1'!RC="""",NA(),'C:\Users\MVishwakarma\Desktop\" & "AR\[Current Month Salesboard for Video Wall_Data.xlsm]Sheet1'!RC)"

'Delete all Error cells

On Error Resume Next

.SpecialCells(xlCellTypeFormulas, xlErrors).Clear

On Error GoTo 0

'Change all formulas to Values only

.Value = .Value

End With
If Application.Ready = True Then
ThisWorkbook.Close SaveChanges:=True
Application.Quit
Else
End If

End Sub




The code is working fine, but it gives me an error "method 'ontime' of object '_application' failed" for the first time it is executed. And sometime it go into break mode. Please help :(

Thanks...

vzachin
07-28-2011, 05:59 AM
hi vishwakarma,

this is how i have a script set up to run daily at 7:30 am...

Control Panel>Scheduled Tasks>Add a Scheduled Task, and follow the wizard.
you don't need Application.Ontime.

all you need is this:

Private Sub Workbook_Open()
Run "PullInSheet1"
End Sub

Sub PullInSheet1()
'your code here
end sub

when your scheduled task runs, it will open your workbook and run PullInSheet1. after it's finished, your workbook will close.

you can schedule it to run as many times as you want.

i would like to see how a VBScript would be written. so if Bob is not too busy, maybe he can give an example...:think:

zach

Bob Phillips
07-28-2011, 08:02 AM
Here is a simple example



Dim xlApp
Dim xlWB

Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
Set xlWB = xlApp.Workbooks.Open("C:\test\test.xlsm")
'do some stuff with xlWB

MsgBox "All done"

xlWB.Close False
Set xlWB = Nothing
xlApp.Quit
Set xlApp = Nothing

Just save as a file called file.vbs, double-click to run it.

vzachin
07-28-2011, 08:41 AM
thanks bob....looks easy

Bob Phillips
07-28-2011, 09:55 AM
Yeah, it is just a slightly different form of VBA. There are no data types, it is all variants.