Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Advertisements:










Schedule Macros To Run While Workbook Is Closed


I'm trying to look at options for scheduling macros to run at a particular time. Ideally I would like to run one at 3:00 am during every week night. From what I've looked into, I can run the OnTime procedure and it could schedule this to run each night. Does anyone know if this is correct, and if there are any other options avaliable to me?

Also, does anyone know if this can run automatically if Excel is closed, or does Excel have to be open? Does the workbook have to be open?


View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Run Other Workbook Macros
I need to create a macros to open up 4 other workbooks and run each individual workbooks macro. The work books can be called book 1 to 4 and the macros is call run in each sheet.

When I have tried it manages to open the sheet but not run the macro.

View Replies!   View Related
Protecting A Workbook But Still Able To Run Macros
is it possible to protect a workbook but still run various macros, i have tried this and the macros wont run

View Replies!   View Related
Open A Workbook And Run 1 Of 4 Macros Based On 3 Cell Values
I'd like to automatically run 1 of 4 macros depending on some criteria. Every workbook created has, unfortunately, the same worksheet name, so that leaves the only differences between the 4 possible loaded workbooks in the cells area and even those can be similiar in many ways. But....I found some criteria to separate all 4 workbooks...Here they are...

run macro 1 if this

1. Worksheet name says "Screen"
2. Cell H1 has the word "Lead"


run macro 2 if this

1. Worksheet name says "Screen"
2. Cell B1 has the word "records" somewhere in the cell
3. Cell H1 has the word "Lead"


run macro 3 if this

1. Worksheet name says "Screen"
2. Cell B1 has the word "records" somewhere in the cell
3. Cell N2 has the word "Delivered"

run macro 4 if this

1. Worksheet name says "Screen"
2. Cell B1 has the word "records" somewhere in the cell
3. Cell N2 has the word "Bevel"

I don't know if this can be done, but if so, that would be fantastic! I'm thinking that the macro would have to be "global" and in the user's personal workbook?

View Replies!   View Related
Copy Range From Closed File & Paste To Closed Workbook
Is there code that will take certain data from one Excel sheet to another named file in a different place on the network? Example Copy cell aa47 from "Recent Faxes.xls" that sits in "correspondence" folder. Then paste into cell B25 "Current Documentation.xls" in the "Sales Contacts" folder

View Replies!   View Related
Schedule A Macro To Run
I am trying to schedule a macro to run at 11:45 pm every time. I use system scheduler to open the excel file and when I use this command to run the macro nothing happens. The workbook opens fine but the macro does not run. I only wants to run the macro once day even if I open the workbook during the day I do not want it to run.

View Replies!   View Related
Hiding Macros From The Run Macros List
Is there a way to hide a macro from the list where you choose which to run, but not in the VBA editor? The userbox I just created calls upon 2 different macros, and has a macro to bring up the userbox. I need a way to hide the macros in Module3 from selection, but keep the macros in Module4 available to choose to run.

View Replies!   View Related
Schedule Workbook Open Automatically
Is there anyway I can schedule an excel workbook to open up at a certain time; I have some code attached to the Workbook Open function; so when the workbook is opened the code will run; but I need the workbook to automatically open at say 17:00 everyday so the code can run.

View Replies!   View Related
Macros Really Slow The Another Macros Is Run
I have two macros. Macros 1 opens another excel file, counts the number of rows it has, inserts that number of rows into the master file and then copies the data over. Key code as follows (x3 for 3 worksheets)...
For k = 1 To numrows
Selection.EntireRow.Insert
Next k

Macros 2 'cleans' the appended data by systematically going through each row, checking if certain cells have data, and if not deletes the rows. Key code as follows (x3 for 3 worksheets)...

For l = lastrow To 12 Step -1
If WorksheetFunction.CountA(Range("B" & l & ":C" & l)) = 0 Then
Rows(l & ":" & l).Select
Selection.Delete Shift:=xlUp
edelrows = edelrows + 1
End If
Next l

Here's the weirdness. macros 1 runs fairly quick the first time out (few seconds). Macros 2 takes about 2 minutes to run (which I'm fine with). However, when I run macros 1 AFTER running Macros 2, Macros 1 goes from taking a few seconds to a few minutes.

View Replies!   View Related
Transfer Data From Open Workbook To Closed Workbook
im looking for some code to transfer cells a3,d6,f9,i6,k10 and i18 from open workbook named "hello"

to

closed workbook named "goodbye" and input to next available row
a3 to a1
d6 to a2
f9 to a3
i6 to a4
k10 to a5
i18 to a6

View Replies!   View Related
Changing Date/time To Run A 12 Hour Production Schedule, And Not 24 Hour
I have created a daily schedule which has a number of factory variables taken into consideration which determine the date and time a particular product should, barring any mechanical problems, come off the machine. (see attached spreadsheet).

The date at the top will be editable by me only so that when I update the production quantities, the “date/time off” column automatically re-adjusts to the remaining quantities.

The formulas are a little long winded, but I have left them that way whilst I try and develop it. I should be able to figure out how to condense them later.

My problem is that the “date/time off” on the right works excellent, but over a 24 hr period.

Ordinarily, we work a 12 hour day (6am to 6pm) with overlapping shifts to cover breaks, and 20 mins warm up at the start of the day for the machine, thus maximising a 12 hour day.

Of course if demand exceeds the allotted time we put on overtime.

Is it possible to specify that normal days are only 12 hours so that if a product exceeds 6pm, it flows into the next day with the balance starting at 6:20am?

And, if the production for the week exceeds the time could I stipulate particular days which we deem are suitable for overtime? Ie, we decide Wednesday is a 14 hour day and not 12.

I had toyed with the idea of creating a 365 day table/calendar, on another worksheet which would have its individual allocated hours in an adjacent column and somehow link them to the date/time off, perhaps by way of a VLOOKUP, but I have been chasing my tail trying to figure out how to implement it.

View Replies!   View Related
Run A Vba Code When Sheet Is Closed
I want to run vba code when a excel worksheet closes. But I want this code to be used for every worksheet that opens. So the macro or vba code has to be loaded with Excel automaticly. I don't want to reenter the code in every template I use.
In access you can attach a module that runs when access or the application is closed: 'Private sub form_close()' Is it possible to create something like this in Excel.

An explanation what I want to do:
I Open/create a workbook from within access. When the worbook closes I want to look with vba if there is a cell called " Total" and if so I want to open a access databases and submit the total to the database. I don't want access to wait untill the worksheet is closed. If the worksheet stays open for a longer time I want to work on in access with other tasks.

View Replies!   View Related
Run Macro In Closed File At Same Time Each Day
i know there is information on this ontime method, i haev read through it, but since i am working with reuters running live in the background they just dont work.
Anyway, on a normal excel sheet, I have made the automated macros run on a timer, which is great. BUT If someone switches off my pc, its not going to be so great because i dont think the macro would run without excel being open.

QUESTION:
How can i make my macro run every morning at 8am? Assuming the worksheet is closed, even excel is closed. i am sure the answer is starring me in the face, i am just getting boerd of looking at it! in the Thisworkbook:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.OnTime dTime, "thismodule", , False
End Sub
Private Sub Workbook_Open()
Application.OnTime Now + TimeValue("20:00:00"), "thismodule"
End Sub

and in the Module:

Sub thismodule()
dTime = Now + TimeValue("08:00:00")
Application.OnTime dTime, "thismodule"

View Replies!   View Related
Copy From Closed Workbook Then Create New Workbook
I've got a problem with this code, have been wracking my brains about. Here is the process I am trying to do:

1) Copy a range (a2:av1000) but (ideally) find the last populated row from a closed workbook (with a different password)
2) Create a new workbook and paste this data into it at A2
3) Close all the workbooks but only save the new one.

View Replies!   View Related
Copy From Closed Workbook To Open Workbook
I'm trying to write a macro that accomplishes the following:

" Book 1" is already open. The user runs a macro that lists all .xls files in directory "d:measurements" The user selects the desired file from the list or box the macro copies from this "book 2" " sheet 3", " range A6:I107 and pastes (values only) into "book 1", "sheet 5", "range A6" End of macro.

I want to accomplish this without opening the selected file (book 2)

View Replies!   View Related
Pull Value From Closed Workbook Where Workbook Name Is In A Cell
I want to open an excel file whose name is a value in a spreadsheet, and then pull a value from the spreadsheet I queried into my existing workbook. Here is the syntax i have so far:

to note: I'm calling the workbook in which the macro is located testproject.xls, and I'm using [integer].xls as my list of files that the macro will query. Ultimately, this code will go in a loop.

Set currentCell = Worksheets("Sheet1").Range("A1")
varCellvalue = currentCell.value
Workbooks.Open "path_of_file" & varCellvalue & ".xls"
Windows("testproject.xls").Activate
currentCell.Offset.(0,1).Select
ActiveCell.FormulaR1C1 = "=[3.xls]Sheet1!R1C1"

The problem is, I want [3.xls] to carry the sale value as varCellvalue with .xls appended, and not be kept static at 3. I tried inserting & varCellvalue & ".xls" into the brackets, but with no luck. My only difficulty is getting [3.xls] to vary along with varCellvalue.

View Replies!   View Related
Run A Macro Automaticly On A Closed Work Book Every Friday
Is there a way to run a macro automaticly on a closed work book every Friday ? I've found ways of doing this using on-open event but It made me wonder if this was possible.


View Replies!   View Related
How To Delete Another WorkBook Macros Using Macros
What is the commands or script for deleting a macro automatically using
another workbook macro.

View Replies!   View Related
"Run" Making Macros Run Twice And Stop
I used the "Call" command instead of "Run." Everything finally worked perfect!!
Being new to VBA I do not know the reason behind the "Run" command being different and causing my problems. BUT "call" sure is the answer to my recent irritations.

After I got the darn things to finally work, I decided to do the responsible thing and use Option Explicit, which helped me clean up my code tremendously!!

View Replies!   View Related
Combining Two Macros To Run As One
I have two macros that I need to run as one and I am not sure how to call the second macro from within the first. I'm sure it's easy...If you know how... Thank you in advance.
--------------------------------------
macro 1
Sub PrintUsedRange()
'
Dim ws As Worksheet
'
For Each ws In Worksheets
Application.CutCopyMode = False
ws.PageSetup.PrintArea = ws.UsedRange.Address
With ws.PageSetup
.Zoom = False
.CenterHorizontally = True
.Orientation = xlPortrait
.FitToPagesWide = False
.FitToPagesTall = 1
End With
Next ws
ActiveWorkbook.PrintOut............

View Replies!   View Related
New To Macros - How Do I Create And Run One
I have an API that continuously gets stock price. The price appears in one cell at all times - let's call it A1

I want to record the value in a different cell every time it changes, so I can analyze the data.

so A1 = 15
B1 = 15

little later
A1 = 15.2
B2 = 15.2

I did some googling and came to a conclusion that I needed to write a macro.

I see that there is a function or event called Worksheet_Calculate()

Apparently it is supposed to do whatever when a value changes (per calculation)

How do I write a macro, and then execute it?

View Replies!   View Related
One Macro To Run Other Macros..
I have 5 macros that update/gather info from the net, and instead of running each of these individually, i would like to be able to create 1 super-macro that runs them all. How would i go about doing this?.. say they're called M1, M2.. M5..

View Replies!   View Related
Run All Macros Togather
I have Macro1, Macro2 and Macro3. Can I have a Macro so that I can have the option to run all 3 together?

View Replies!   View Related
GetValue From Closed Workbook
when using the Active X Data Objects 2.8 Reference in excel 2007, i run into the problem with the GetValue function. After running the script the output sheet shows #REF! in all specified cells?!

Sub test12()
p = Location
f = file
s = "Sheet1"
For r = 1 To 150
For c = 1 To 12
a = Cells(r, c).Address
Sheets("Cars").Cells(r, c).Value = GetValue(p, f, s, a)
Next c
Next r
End Sub..........


View Replies!   View Related
COUNTIF On A Closed Workbook
I have the a COUNTIF function used on one workbook which refers to another workbook, however I get the result #value! unless the other workbook is open - this is even if I chose to update links when I fist open the file.

Do all workbooks have to be open when using COUNTIF?

If I open the other workbook after my workbook with the COUNTIFs on has been opened then all #value! errors disappear and the correwct info is shown.


View Replies!   View Related
Lookup Value From Closed Workbook.
I have a workbook called Book1 that was created from another macro. I am attempting to do a lookup using columns B2 and C2 and look for the same values in columns G6 and J6 from a workbook called Marine Moves..If a match is found copy the value from Column H insert that value into Book1 Column A, if possible I would like the Marine Moves workbook to remain closed during this process.

View Replies!   View Related
Referring To Closed Workbook
I have the following formula to extract a specific cell from a closed workbook. It works fine. I want to be able to make the file name refer to a another cell so I can create a spinner to change it. For example:

='C:Documents and SettingsTom Desktoplabor[01_032407.xls]Stats'!A4

Cell A1 would be 01_032407, and I would replace [01_032407] with [A1]
but it does not work.

View Replies!   View Related
Prevent Workbook Being Closed
I display a message box in Workbook before Close event.
If user click yes I shouldnt close the workbook if NO i should close the workbook.

In the Yes part, How can I stop excel from closing the workbook.
If i dont write any ocde it automatically closes in the event.

View Replies!   View Related
Getting Data From A Closed Workbook
I am trying to use this code to pull data from a closed workbook. The code will be in workbook Book1 and I will be pulling information from closed workbook Book3. I need to pull data from cells A1:A4 from Book3 and place it into Book1 on Sheet2 in cells A1:A4. I receive an "Subscript out of range error" on this line of code

View Replies!   View Related
Pull From Closed Workbook
I have a folder that contains hundreds of excel workbooks. Each workbook has 20 or more sheets. All workbooks have the same look and design. All workbooks have macros that they use to function and the macro I want cannot interfere with their macros.

What I want to do is copy certain cell values (some numeric and some text) from several sheets from these workbooks and paste them into another workbook (a master workbook) that is essentially collecting these values into a table without having to open all these workbooks. For example, let’s say all of the workbooks are named with a numeric value starting with “one.xls” and that the sheets in these workbooks are named “a”, “b”, and so on. So, in the master workbook in cell “B2”, I want the name of the first workbook searched (“one.xls”); then in cell “C2”, I want the value from cell “J45” on sheet “b” of “one.xls”; in cell “D2”, the value from cell “B32” on sheet “b” of “one.xls”; in cell “E2”, the value from cell “K13” on sheet “e” of “one.xls”; in cell “F2”, the value from cell “R43” on sheet “k” of “one.xls”. The in cell “B3” of the master workbook, I want the same information as before except from “two.xls” instead of “one.xls”, and so on down to “three hundred.xls”.

View Replies!   View Related
Max Value From Closed Workbook
Is there a formula or vba code that would allow me to get the max value from a column in a closed file/workbook?

View Replies!   View Related
Retrieving From A Closed Workbook
I have tried to find how to retrieve a worksheet from a closed workbook and I am almost sure that there was an answer how to do this in FAQ but I cannot seem to find it, does anyone know where this thread is?

View Replies!   View Related
Copy From Closed Workbook
Copy From Closed Workbook

I have been trying this method for a while, I just don't know what to put where, & what names to change.

Trying to pull ranges from database.xls to cost.xls

View Replies!   View Related
Get Values From Closed Workbook
I have a master file with two sheets. I have another input file with only one sheet. Without opening the input file, is it possible to copy the range and paste special values only into a duplicate sheet in the master?

View Replies!   View Related
Values From A Closed Workbook
I wrote here a few days ago about an issue retrieving a closed worksheets values and I got help from RoyUK(see code below and link), but for some reason the values are never entered into the open worksheet.

Retrieving From A Closed Workbook
Sub FillingSheet()
Dim filetoopen As String
Dim wb As Workbook
filetoopen = Application _
. GetOpenFilename("XL Files (*.xls), *.xls")
On Error Resume Next
Set wb = Workbooks.Open(filetoopen, True, True)
With ThisWorkbook.Worksheets(1)
. Cells.Value = wb.Worksheets(1).Cells.Value
End With
wb.Close False
Set wb = Nothing
End Sub

View Replies!   View Related
Make Workbook Code/Macros Unique To Workbook
How is code or functions kept unique to a workbook? for instance i create a few toolbars that are relevant to "work book 1", however when i open another workbook "2" the toolbars do not function properly or are removed by the opening/closing of the work book, similarly other macros seem to struggle with more than one workbbok open at a time.

View Replies!   View Related
Having Macros Run When The Date Is Changed
Is it possible to have a macro run when a date is changed each month instead of clicking on the button. I have a few macros that are ran each month in a spreadsheet (manually) but would I would like to have them run automatically when the date is changed in cell a4 each month (Cell A4 is linked to another spreadsheet tited "Input" here the date is changed each month and all of the other spreadsheets are automatically updated). The date in cell a4 is always the last day of the month. Also the name of the workbook changes each month ( I am not sure if this is important to know).

View Replies!   View Related
Run Multiple Macros From Different Workbooks
I am trying to run multiple macros from multiple workbooks. I've figured out how to open each workbook, but I can't figure out how to run a certain macro in each workbook called "Main".

View Replies!   View Related
Run Macros On Open Statement
I would like to when open a certain spreasheet it would perform a certain task.

View Replies!   View Related
Run Macros In Scheduled Task
I've used Excel 2003 to write a macro to analyse some date from our internal proprietary database. I use some Perl to generate a bunch of csv files from the data, and the run the macro to generate graphs and statistics from that. What I would like to do now is run this entire operation as a scheduled task, so that, every week or so, the data is downloaded, the processing is done on the csv files, and the results are saved to a location to be viewed on our internal site.

I'm assuming that the best way to do this would be somehow run something from the command line (to be embedded in a script) which basically says "1) Open this csv file 2) run this macro 3) save the output to this location", but I can't figure out how this can be done.

View Replies!   View Related
Run Macros That Reside In Different Workbooks
I have a series of 24 files (for non-profit grants), each of which contains a macro to create a consolidation file of 29 departments (for each grant). Instead of opening each file individually I would like to create a macro in a different file that would open each file and run the consolidation macro in each file. So far I have been unsuccessful in compiling a macro to complete this task.

View Replies!   View Related
Run Macros Based Cell Value
I am trying to create a macro based on value in a cell. For example if J2 is greater than 1 then run macro A. The code I have is:

If j2 > 1 then
Call MacroA


The file imported as a text tab deliminated file and I believe that even though J2 might be 10.05, it does show up in the formula bar as just 10.05 not =10.05. I don't if this makes a difference. The J2 cell could either bland or values ranging from 1 to 100. If it is blank I want to run a different macro.

View Replies!   View Related
Run Macros On Change In Another Sheet
I have two macros, both written in "Module 1". I would like these both to run on the sheet named "profile" whenever any one or more cells in the sheet named "data" change in value. I have looked at the relevant FAQ's but unfortunately just don't have the experience with vba.

View Replies!   View Related
Lookup In Closed Workbook Without Opening
I want to lookup data in a closed workbook, that is located on my desktop, without having to open that workbook. I have been trying code as follows, but having problems with the syntax.

View Replies!   View Related
Sum In A Closed Workbook With VBA
I was wondering if there's any way to sum inside a closed workbook by usin' VBA? I think I figured out how to do it just with formulas, but I was hoping to be able to make like a macro or something to do it, 'cause its kind of time consuming having to go back and forth to all these workbooks.



View Replies!   View Related
Copy Worksheet From Closed Workbook
I am trying to use a routine that I copied from another thread on this forum, but I get an error message: Runtime error "1004" Copy method of worksheet failed.


View Replies!   View Related
Copy Cells From A Closed Workbook
I have a folder at this location c:documents and settingscharliedesktoplabor

it contains numerous files that have this format 01_Projections with 01 being the store number and it goes from 01 to 20. the word projections is the same on all 20.

I want to access cell A12 on a sheet titled "summary" in all files and have bring over to another workbook without having to open each one. How can I accomplish this?
What if I want to bring back numerous cells from this same sheet and place them in cells in the open workbook?

View Replies!   View Related
Copy Data From Closed Workbook
I have 7 workbooks I would like to copy data from. The data in question is a sheet called "Accident Book" in each of the 7 workbooks.

I know how to copy the data, but how do I reference these 7 closed workbooks ? They are all in the same directory, but these are other files in there that I dont need, so I dont just want to point to the directory.

I have this code from Nimrod but can't get it working.


Sub GetValuesFromAClosedWorkbook(fPath As String, fName As String, sName, cellRange As String)
With ActiveSheet.Range("A65536").End(xlUp)
.FormulaArray = "='" & "\Bls36dc10WorkH&S" & "[" & Central 2004.xls & "]" _
& "Accident Book" & "'!" & range("A6")
.Value = .Value
End With
End Sub


I actually want to copy data from A6:N6 down to the last row entry, then paste this into the next available row in the activesheet. I need to do this from 7 different workbooks.


View Replies!   View Related
Indirect Reference To Closed Workbook
I am writing a formula in cell "B1" in "December 2007 REVISED BY DAN.xlsm" that will reference cell "X1" in a workbook that has not been created yet.

The way I will tell the formula what the name of the workbook will be is by referencing cell "A1" and adding the extension ".xlsx" to it.

Example: ...

View Replies!   View Related
Display Data In Closed Workbook
I have 1 excel workbook open. It is in a common directory with several other excel workbooks which are closed. In the open workbook, I have created a vba macro that completes various tasks - only within the open workbook. I want to modify the macro in the open workbook such that it will specify data to be displayed in the closed workbooks. Then, when the closed workbooks are opened next, the data will reflect what the macro had specified.

View Replies!   View Related
Copy Data To A Closed Workbook
how do i copy the data from cell C1 in the active workbook "name1.xls"
to cell D10 in the closed workbook "name2.xls" in "sheet1" without opening the
closed workbook?

View Replies!   View Related
Copyright © 2005-08 www.BigResource.com, All rights reserved