to open a workbook at a specific time. I want to beable to flip my computer on at 8:50am, go grab some coffee and then at 9:00:00am have my workbook open and performing tasks. Besides turning on my computer, I dont want to have to open or activate anything prior to having my workbook open. However if there is code to turn on my computer from a sedative state that would be great too.
View 9 RepliesI would like the workbook to open to a specific worksheet everytime it is opened regardless of what worksheet it was in when file was saved. Can someone point me in the right direction?
View 2 Replies View Relatedis there any way to open a workbook and select a sheet within the book without having the entire name?
I've got a unique workbook and worksheet identifier but don't know how to use wildcards to get my macro to open the right workbook.
On opening of an excel document is it possible to have a date in a cell that will continuously update. =now() enters the date required but if i was to go into the sheet days or whatever later is there a way that this will update automatically??
View 3 Replies View RelatedNeed to have a particular workbook open at a specific time...
I wrote this and placed it in a Personal.xls module, did not run automatically. I then placed the 2nd SUB() in the "This Workbook" and nothing. Where or how is the proper way to run this...
----------------------------
Sub Open_IndexAnalysis()
Workbooks.Open Filename:="e:Index Analysis.xls"
End Sub
------------------------------
Sub Run_OpenIndexAnalysis()
Application.OnTime TimeValue("09:40:00"), "Open_IndexAnalysis"
End Sub
-----------------------
I can open a file at specific time using TaskSchedule. How do I close it at specific time?
I am trying to write a simple one-line code for that but do not know how to combine two statements:
Application .OnTime TimeValue("17:01:00")
and
ActiveWorkbook.Close
Also, this workbook is updated every 1 minute using the same OnTime method and everything works perfectly. However, when I try to close file there is a message saying that "OnTime method object application failed" and I have to click "Debug" or "End".
Only when I click "End", the file is closed.
I have got a spreadsheet which is being used as a database. I would like the spreadsheet to close automatically if the user has not "touched" it for a specific amount of time (say 1hr).
2 scenarios:
a/ The user is working in Excel but with another workbook.
b/ The user has the excel spreadsheet open but is working with another application, say Word
I experimented with Workbook_WindowDeactivate but I do not know how to stop the process if the Workbook is reactivated (Maybe a DO ...LOOP calling a function returning a True/False statement on the event Workbook_WindowActivate !!). My way of thinking is the following
Workbook is deactivated
Start a timer
If the workbook is not reactivated with 1hr, save and close (no user intervention wanted)
Else stop timer
Repeat process
I am working on a system where the system is having "australia time". Now, I want a worksheet where it should reflect the India Time after pressing a specific key in a specific cell.
View 3 Replies View RelatedI 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.
So I have two dates:
Opening Date: 29/07/2014 13:27
Closing Date: 29/07/2014 14:42
These are formatted in DD/MM/YYYY and HH/MM
I need to subtract the dates and find the time taken from the opening and closing time.
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].....
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].....
Can I search a document without opening it?
If not how do I open a file if not already opened without using "ON ERROR"
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
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 Relatedhow to prevent a workbook from opening if another excel document is open and if a visual basic window is opened.
View 8 Replies View RelatedI'm having a problem to use the macro for opening a specific worksheet in a workbook. My plan to setup the workbook to open the first worksheet no matter which worksheet that was last saved and close.
What i have in mind is like below macros but it is not working.
Private Sub Workbook_Open()
Worksheets("Main").Activate
End Sub
Attached is the sample for the workbook. Test file.xlsm
I have a workbook that contains worksheets whose sheetnames are specific to each day of the month ie: 1st 2nd 3rd 4th 5th, and so on up to 31st. I am trying to write an workbook open procedure to check the day of the month and match it to the relevant worksheet so it will open to that day. for example if opened today being the 25th, i want the code to unhide sheet named 25th and select it, whilst all other sheets bar one is hidden.
View 5 Replies View RelatedI got Excel for Mac 2011 installed. I have a table called 'Raw" that holds data from the beginning until the end of 2013 (in total ~7000 rows). I have columns for Category, Start and End time, and time period in Hours, Minutes, and Seconds:
Code:
A
B
C
D
E
F
Category
Start
End
Hours
Minutes
Seconds
Transportation
2013/01/01 01:00:00
2013/01/01 01:15:00
0:15:00
15:00
900
[Code] ...........
I'm trying to get an overview of how much time I've spent in January on Transportation. I would need to look at the Category entries for Transportation, then only use the ones which have the Start set to 2013/01, and then take the Hours/Minutes/Seconds, and summarise it in a new table called 'Data':
Code:
A
B
C
D
E
F
Month
Category
Occurrence
Hours
Minutes
Seconds
2013/01
Transportation
48
[Code] .......
I have figured out a way to count the occurrences for Transportation for January:
=COUNTIFS(Raw!B:B,">="&"2013/01/01 00:00:00"+0,Raw!B:B,"
I have a file that I want to clear certain cells and all option buttons when first opened. I then want the user to be able to save and close the file with the data they have entered. When they go to reopen the file I want the data they entered to still be there. I want the user to get the blank file off the company's server and then save it to there machine when it has been completed and when they go back to this file the information is still entered. I have attached the file.
Request Form 2012 Draft4.xlsm
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] ....
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)"?
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 RelatedI 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 RelatedI 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?
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".
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
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]....
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 & _
"..." ...................
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