I want to be able to launch code in an add-in to perform a check when other workbooks are opened by the user. Code in the Open event for the add-in launches when Excel opens, not when each workbook is opened
View 2 RepliesWhy 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
How can i manage that my code in the Workbook_Open() event is run when i open that workbook with some code from an other workbook?
When i manually open that workbook, excel runs all events, including my Workbook_Open() event, but i need to open this one out of an event of an other workbook.
Is it possible to have excel 2007 open a workbook, activate the first sheet "output" and activate the first empty cell in column A - ready for user input? i.e.
I want my user to open workbook "EHB Stock" and automatically the first empty cell in column A of worksheet "output" is activated and ready for input.
The rest of my code is:
I cannot figure out how to get my error handler to work, or actually, not work. It seems to work fine when there is an error, but the code still gets read even when there was not an error. Basically, I am trying to open a file, which may or may not be there. When it is not there I want a message to pop up informing the user. However, when the file is there and it opens, the error handler still gives the message box. Any ideas what I am doing wrong?
Private Sub btnOK_Click()
Application. ScreenUpdating = False
Dim LCSfile As String
LCSfile = frmSelectFile.Listbox1.Value
On Error Goto ErrHandler
Workbooks.Open Filename:=sPath & sDate & "" & LCSfile & "QUANT.CSV"
ErrHandler:
MsgBox ("File is not quantitated. Please select another file.")
Application.ScreenUpdating = True
End Sub
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.
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 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 with a button that will run a macro to open files and import sheets based on if the user checked the box. Everything works perfectly EXCEPT for the importing of the sheets from newly opened workbook.
Here is what I want the code to do:
1. if the box is checked then open the file
2. Copy (import) the first sheet in the newly opened workbook and put it before the 1st sheet.
3. Close the workbook that the sheet was copied from and do not save changes
4. active the workbook that has the newly imported sheet.
And here is what i have thus far:
Code:
Sub OpenFiles()
Dim Folderpath As String
Dim cell As Range
Dim r, LRow As Single
Dim Dbook As Workbook
Set Dbook = ActiveWorkbook
[code]....
After stepping through it, the problem is here:
Code:
Workbooks.Open Filename:=Folderpath & Range("B" & r).Value
Source = Range("B" & r)
Sheets(1).Copy Before:=Workbooks("CompletionWorksheet.xlsm").Sheets(1)
'How the hell do I close this ^ workbook
ActiveWorkbook.Close savechanges:=False 'This closes the workbook where the sheet was copied to
Windows("CompletionWorksheet.xlsm").Activate
Not sure what to do.
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 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".
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
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.
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?
I have a workbook which has a macro to loop round refreshing its data every half an hour and each time the refresh is performed the file gets saved. This file is opened by one user in the morning, and left open with the macro running to carry out the refreshing throughout the day. This workbook is then also opened by a number of users as read only who interrogate (but do not update) the data in the spreadsheet as part of their work. Periodically, they close and re-open their copy of the workbook to ensure they have the most up-to- date version open.
To let them know when a new version is available (i.e. when the refresh has been performed and the file re-saved), I have some code which compares the last saved date/time of the file they are using with the date and time of the file they loaded the file from, and displays a message when they values are different. I have, however, been unable to develop a macro which properly 're-loads' the workbook. I have been able to get the file closed and the latest version opened (through a 'ThisWorkbook.Open' statement). However, when the new version susbsequently opens, none of the open events are triggered, nor is the Auto_Open macro executed. This is a major problem, as the spreadsheet relies on this processing being performed for the file to properly work. (I know that macros are enabled (I get the 'enable macros' prompt in the course of the updated version being opened, and can run macros once the file has been opened).)
I would like to something arranged where a macro I currently have assigned to a button is automatically run once the workbook is opened. The reason being because I would it to be current with todays date. I have done the following and it results in nothing I still need to click the button to get the macro to work.
Private Sub Workbook_Open()
LightUpRows
End Sub
I am having problems to read a number inside a .txt file.
I use this code to open a .txt
Workbooks.OpenText filename:=data_DMC_filename _
, Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:= _
False, Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(Array _
(1, 1), Array(2, 1)), DecimalSeparator:=",", ThousandsSeparator:=".", _
TrailingMinusNumbers:=True
At original file the number is like this format: 003791689000011403700010, but after opening as workbook it is like this 3,7917E+21
I tried changing the forma to text with this code bellow, but it looses information
Selection.NumberFormat = "0"
and the result is: 3791689000011400000000
I already wrote a lot of my code for other values, using this Workbooks.OpenText command, opening as workbook so I would not like to open the file in text format or so...
I am trying to open a workbook, assign it a variable and use that variable later. here is what i have
[Code] ......
When I try to activate it later on in another procedure using:
[Code] copy to clipboard
I receive an "Automation Error"
I have tried declaring it as a public variable, even a global variable and cannot get this to work.
All I'm really wanting to do is open a workbook (where the name will change depending on the book being opened), assign that workbook a variable name, and use it later in the code.
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 Replies View RelatedI am trying to write code which copies the activesheet (called "Data"), opens a new workbook, pastes the data into it and closes the new sheet as "sales 2008" followed by the date. What I have at the moment is:
View 3 Replies View Related