Auto open file at fixed length of time

[vishwakarma]

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]

Why not just create a VBScript file to do what you want, and schedule it from Windows?

[vishwakarma]

How can we do that?

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



Thanks,

[Bob Phillips]

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]

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]

below is the code, if you guys want to refer and provide me some 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]

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...

zach

[Bob Phillips]

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]

thanks bob....looks easy

[Bob Phillips]

Yeah, it is just a slightly different form of VBA. There are no data types, it is all variants.