OnTime Method Opening Workbook
Aug 13, 2007
my wonderous spreadsheets have had a number of errors on them. One of them seems that the "autosave" VBA that we have been using seems to be hidden in memory. By this I mean that if I am working on this book and close it, on occassion the workbook tries to open itself again (it prompts for the password). Therefore I think something gets stuck "in memory" but I am not sure why it would act in this way?
The code is as follows:-
Public g_blnDate As Boolean
Public vartimer As Variant
Const TimeOut = 60 'in minutes
Sub Salva()
ActiveWorkbook.Save
Call Tempo
End Sub
View 9 Replies
ADVERTISEMENT
Nov 1, 2009
Is there a way to tell (using VBA in the workbook open event) whether a user has opened a particular file by double clicking on the file icon or by using the Open Command in Excel ?
View 9 Replies
View Related
Oct 3, 2007
with opening a workbook at a specific time and/or running a macro at a specific time.
View 12 Replies
View Related
Dec 23, 2009
I need a macro to run at a certain time on Wednesday's, and then a certain time on the other days.
I would like to use a cell to reference that time, but right now, I am just hard coding it, but I an not get it to run.
Ultimately what I am looking for is something like this: ....
View 12 Replies
View Related
Sep 6, 2007
currently i have this:
Sub StartCycleB()
ActiveWorkbook.UpdateLink Name:="c:/a/b/c/test.xls", Type:=xlExcelLinks
RunWhen = Now + TimeSerial(0, 15, 0)
Application .OnTime RunWhen, "MyMacro", , True
End Sub
Works fine. Its updating and refreshing a file every 15 minutes. When a cell in the test.xls changes to TRUE, my sheet picks it up and changes to TRUE also.
So here is the problem: Once my sheet changes to TRUE, another macro activates in my sheet. This macro will automatically activate every 15 minutes if value is TRUE. BUT the problem is i ONLY WANT TO ACTIVATE THIS MACRO ONCE a day IF the value in my sheet is TRUE. So in summary how do i activate a macro based on a TRUE value on a cell, to happen ONCE a day and then stop?
View 3 Replies
View Related
Apr 29, 2006
I am trying to use the application .ontime procedure to run a class method, but having a little trouble with what to use as my procedure name. Do I have to include the name of the class, or the name of the instantiation of the class, or what? Here is kind of what I have in the "myClass" class module:
Option Explicit
Private WithEvents m_cmdButton As MSForms.CommandButton
Private m_iTimer As Double
Private Sub m_cmdButton_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
' Set the time with the long delay
m_iTimer = Now + TimeSerial(0, 0, I_TIMER_DELAY_LONG)
' Start the timer
Call Application.OnTime(earliesttime:=m_iTimer, procedure:="ClickAgain", schedule:=True)
End Sub
Private Sub ClickAgain()
Call MsgBox("ClickAgain successfully called.", , "Boo yeah!")
End Sub
I have checked and the MouseDown event is being registered: it will work if I use a procedure in a standard module rather than the class method"ClickAgain". (I am only including the code relevant to the "ontime" procedure. My actual class has other methods to deal with setting the control source for the command button & everything.)
View 6 Replies
View Related
Aug 5, 2007
I have 2 macros, each called by the other to keep updates fast as per below,
Sub Macro1()
Cells.Replace What:=",29", Replacement:=",30", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Application .OnTime Now() + TimeValue("00:00:01"), "Macro2"
End Sub
Sub Macro2()
Cells.Replace What:=",30", Replacement:=",29", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Application.OnTime Now() + TimeValue("00:00:01"), "Macro1"
End Sub
I need to create a separate macro I can run to permanently stop these two cycling. And allow me to use Excel. Then, when I wish for these to cycle again, I simply run that macro.
View 4 Replies
View Related
Jul 28, 2009
I didn't receive a response to a previous post where I was trying to use a batch file to close and open a workbook at a scheduled time everyday. I was thinking about this problem over the weekend, and thought that it would probably be easier to just use a macro to close the file at a certain time everyday (if it is open) and then use a batch file to open the file a couple of minutes later.
I'm having problems with what I thought should be pretty simple though. I tried this to close the file at 5:15pm
View 14 Replies
View Related
Feb 9, 2012
How to correct the code below so that OnTime is cancelled when the workbook is closed?
Error on bold line is Run-time error '1004': Method 'On Time' of object'_Application' failed
Code:
Public RollTime As Date, CalculateTime As Date, RefreshTradesTime As Date
Sub CancelOnTimeProcess()
SetTimesForRoll
With Application
.OnTime RollTime, "RollMacro", , False
.OnTime CalculateTime, "AutoCalculation", , False
.OnTime RefreshTradesTime, "UpdateLinks", , False
[Code] .......
CancelOnTimeProcess is being called when the user closes the workbook:
Code:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
CancelOnTimeProcess
End Sub
View 2 Replies
View Related
Jun 18, 2007
i want vba to show a msgbox every 5 sec & it should start as soon as the file is opened. Below is the code & it works. Problem is, that when i close the file it immediately reopens and starts the "msgbox proess". Only if i close excel completely, this problem goes away (& thats not a very proffessional way to do it!!). Can someone pl look at this one & help ?
Private Sub workbook_open()
Call Macro4
End Sub
Sub Macro4()
Dim i As Integer
MsgBox "test!!"
NextTick = Now + TimeValue("00:00:05")
Application .OnTime NextTick, "Macro4"
End Sub
View 6 Replies
View Related
Oct 15, 2008
I was wondering if there is any code using an on-click method. When clicking a button i need excel to exit the file/sheet without saving (no prompts) and then re-open the exact same file/sheet once it has closed.
In a way this is like a refresh button.
View 14 Replies
View Related
Apr 17, 2007
I have a need to open a file from my companies intranet. My current method was to open said file via the method that the recorder gave me. However, I would like ot be able to open a said file without having to start open another workbook.
This is the path:
[url]
So the command is this:
Workbooks.Open [url]
Links are not actual links
So what I need to know. Is how can I open this file without opening a workbook. I haven't been able to use the VB "Open Statement" to open a file and I don't believe that I've been successfull using the Filesystem object either.
View 9 Replies
View Related
May 13, 2014
I am using the below code (i have combined different bits of code which i found on these forums) to open a new workbook, copy all the data in it and paste it into Sheet2 in the active workbook . Also, i want the data to be pasted on the next empty row as there is already data in Sheet2. The data that is being copied and pasted has the same format and it is going to be repeated many times.
The problem i am having is that it is not offsetting the data to the next empty row - i think it is a simple change but i can't seem to get there :s
Also, the code is currently selecting all of the data from the workbook that i open - is there a way i can select all of the data, excluding the first row (the first row contains the headings which i already have in Sheet2 in the active workbook).
[Code].....
View 5 Replies
View Related
May 16, 2014
I have created userform and it works fine. Following code assigned to 'SUBMIT' button in userform - works fine. I am trying to include code where certain data from userform is also copied to workbook2 ( of course without opening it)- as marked in red...below
[Code].....
View 4 Replies
View Related
Apr 6, 2014
Can I search a document without opening it?
If not how do I open a file if not already opened without using "ON ERROR"
View 3 Replies
View Related
Apr 25, 2009
I am trying to make one of my macros open a file then switch back to the orignal file and then running the macro.
here is my
View 11 Replies
View Related
Mar 1, 2013
I have a spreadsheet that data gets exported to from a software program. There is information such as customer name, part number, inquiry number, etc that I'd like to use to develop a log of all my estimates I create for parts. I'd like excel to take the cells I select and insert them into certain cells in the other workbook and automatically save that workbook. This will create a log of all my estimates where I can then link the full estimate to the log. The information will have to go in the next available row. Is there any way I can accomplish this without having to open the other workbook and save the work book each time?
View 1 Replies
View Related
Aug 29, 2006
how to prevent a workbook from opening if another excel document is open and if a visual basic window is opened.
View 8 Replies
View Related
Jun 12, 2013
I have just added some code to make my workbook close after it has been inactive for 5 minutes...this works fine except it opens itself up again after 5 minutes. If I select 'Enable Macros' it closes immediately and if not, it will stay open.
Here is all of the code I put in for the auto shut down:
ThisWorkbook:
[Code]....
Module 1
[Code] ....
View 3 Replies
View Related
Jan 6, 2007
I currently have a workbook that, when opened, automatically kicks off a macro (with a call from the Workbook_Open event in ThisWorkbook). I was to run that same called macro from a custom button that I put on a toolbar in Excel itself...which is pretty straight forward. However, when I do this, the macro kicks off twice...once from the button and once because the called macro's workbook opens and kicks off the macro with the Workbook_Open event.
So, what I was thinking was...is there a way to tell if a workbook is being opened by another workbook (by calling it's macro) or whether is was opened "normally (manually)"?
View 9 Replies
View Related
Nov 9, 2006
My friend emailed me a workbook. When she or I open the workbook, it opens two instances of it. Is there an option in excel that is causing it to do this? I need to send this a client and I do not want this to happen on their side.
View 6 Replies
View Related
Apr 23, 2014
I have a workbook that consists of 3 sheets, it's a template I use. Once I am done filling out sheet 3, I save as a new workbook. When I have to go back to the template workbook it always opens on sheet 3. Is there anyway that once I open the template workbook it will open on sheet 1?
View 1 Replies
View Related
Feb 9, 2009
I have created an Add-in for use across the business, the add in calculates financial impact based on figures that are stored on the shared drive.
The addin currently opens the workbook, and sets the variables to figures contained in the workbook - and then closes the workbook.
To roll this out to the business - I need to get the add-in to open the workbook as read Only, so that it will allow multiple users to access at once. I have set to open as read only in the past - however this prompts me to save the workbook as something else.
Is there any way I can get this to just open as read only, do what it needs to - and then close; without having the option to save as something else?
View 8 Replies
View Related
Sep 29, 2009
As above i have a macro which opens a workbook which is on a network drive. This works fine for my multiple users...however i have discovered that if they do not have the drived mapped with the same drive letter at the start...it will throw up an error advsing that the file does not exist. Is there any way to by pass this.
Ex - "TestFile.xls"
File is stored in "E:NetworkTeam1Testfile.xls"
However, some users have access to this file and it is mapped as E:, but some users have been mapped as F: The advisors who have been mapped as F: cannot access the file. Is the VBA/Macro smart enough to bypass the drive letter and just search the rest of the past...NetworkTeam1Testfile.xls".
View 2 Replies
View Related
Nov 8, 2012
Why can't I get this code to work? The first line seems to work ok but not yet sure as the rest doen't work.
I have tried NEXT_YEAR as a string and as a number but I keep getting an error.
Sub test()
NEXT_YEAR = "2013"
ChDir "J:Maxtor backupFire DeptVacations " & NEXT_YEAR
Workbooks.Open(Filename:= _
"J:Maxtor backupFire DeptVacations " & NEXT_YEAR "VacGrp - 1 - " & NEXT_YEAR & ".xls""", UpdateLinks _
:=3).RunAutoMacros Which:=xlAutoOpen
End sub
Below is the code I am trying to replace and it works fine.
ChDir "J:Maxtor backupFire DeptVacations 2013"
Workbooks.Open(Filename:= _
"J:Maxtor backupFire DeptVacations 2013VacGrp - 1 - 2013.xls", UpdateLinks _
:=3).RunAutoMacros Which:=xlAutoOpen
View 5 Replies
View Related
Jun 2, 2014
I have a code that opens a workbook and then calls a function on the workbook. What I want to do is reference that workbook so that when the function ends I can make that the active workbook again.
This is my code
Code:
Sub BarcLinkedCleanSort()
Dim fNameAndPath As Variant, wb As Workbook
Dim ws As Worksheet
[Code]....
View 1 Replies
View Related
Jul 28, 2006
I hope this is not a violation of any of the security rules. At work, we are wanting to track when users open up a workbook by sending an email (not my idea). I have the email on open part working just fine, I wanted to know if there is an option that it does not save the sent message in the sent folder?
Private Sub Workbook_Open()
Run "EmailMacro"
End Sub
Sub EmailMacro()
Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
strbody = "User xx has opened workbook" & vbNewLine & vbNewLine & _
"..." ...................
View 5 Replies
View Related
Aug 23, 2006
I'm attempting to write a macro (in Excel) that uses a path that is given in one of the fields of the worksheet to open a workbook and get find a field to return to the original worksheet. I have working code to pull the information from a worksheet within the same workbook, but I can't seem to get it to work with an external worksheet. Can someone assist me in getting this to work?
I have attached my original code that works, so all I need to do is translate it to open the external workbook and worksheet instead of using the local worksheet. This is pretty simple right?
Public Function GetRate(strClassification As String, strContractNo As String) As Integer
View 9 Replies
View Related
Sep 12, 2006
I have a Workbook that will freeze while trying to open.
Macro Security is set to Medium (klik "Disable Macro" )
The Hour Glass mouse pointer will stay on "Wait" then a "Not Responding" Alert will appear on the top blue bar of Excel.
The only way to shutdown Excel is by using the Task Manager ( Cnt_Alt+Del)
All other workbooks open OK on their own, but as soon as I trt to open the problem workbook, everything Excel freezes.
View 9 Replies
View Related
Apr 2, 2007
I've created a simple userform with buttons that perform certain macros.
1. how do i make the userform to pop up every time the workbook is open?
2. i got the userform to pop up via macros menu.. but the buttons doesnt seem to work... could you guys give quick lesson on how to add a macro command to a button on userform?
View 2 Replies
View Related
