Sub test12()
Set wbABC = Workbooks.Open("C:ABCwithMacro.xls")
Call abcMacro 'this macro is stored in ABCwithMacro.xls
'error - sub or function not defined on 'call abcMacro'
End Sub
I am looking for a macro that i can store in my personal.xlsb. what i need is pretty much is something like this
private sub workbook_open
if workbook.name "inventorysummary.csv"
then application.run "personal.xlsb!capacity"
end sub
I only need it to run just for this file and i cannot place it in the file due to it gets replaced every day. Which if it didn't get replaced. I know how to do auto opens when the file stays the same I am just unsure for this.
I have a macro that launches word or excel files if the file address (e.g. C:Documents and SettingsHenryDesktop est.xls) is typed into cell A1 by looking at the last 3 letters of the file name.
I cannot find a way to enable it to launch pdf files as well (e.g. C:Documents and SettingsHenryDesktop est.pdf)
I hide all Excel standard tabs and thus need a button on custom tab to record a macro. (I did this successfully with custom menu in Excel 2003.) In 2007 ribbon, I've reused the code for a button's OnAction, now going to callback that runs:
Application.CommandBars.ExecuteMso ("MacroRecord")
In Excel 2003, the stop button appeared automatically for me. But in Excel 2007, with hiding standard tabs, etc., I see no way to stop recording, other than running my button to return the Developer's tab, where the Record Macro button has changed to Stop Macro.
I'd like to either add a second button to my ribbon to stop recording the macro (but I can't find an idMso to use in a callback) or have my Record button change to Stop button, like Excel does. But I can't come up with correct code.
I have a macro to sort a very large list of materials by a variety of conditions.
The macro runs automatically when a cell on the master list spread sheet is selected and using If/ElseIf it sorts based on what cell they have selected.
Example
If ActiveCell = "ALUMINIZED" Then
ActiveSheet.Range("$A$14:$K$1945").AutoFilter Field:=2
ActiveSheet.Range("$A$14:$K$1945").AutoFilter Field:=4
ActiveSheet.Range("$A$14:$K$1945").AutoFilter Field:=1, Criteria1:= _"Aluminized"
Which is working perfectly. The problem I am having is that some of the cells are two lines so it looks like this:
BE
BEARINGS
I don't know how to get it to launch based on that cell content because of the line break in there. Can I make it launch based on the first two characters only?
Any way to actually execute a macro stored as a text file?
Without being long winded - I want to create different macro and physically save them as text files. Once saved - I could call them from a list.
I would like a macro which works on the selected cells:
A number of the selected cells contain formulae with an apostrophe in the beginning.
I need the macro to remove the apostrophe and calculate the formula in those cells..
I have created a simple macro that inserts a header onto a sheet on the first row of a workbook. It works fine when the macro is stored in the file that I am wanting to paste the header into, but when I moved the macro to my "personal" folder it gives me a debug error with this code:
Code:
ThisWorkbook.Sheets("Bid Sheet").Rows("1:1").Insert Shift:=xlDown
ThisWorkbook.Sheets("Bid Sheet").Columns("K:K").ColumnWidth = 50
I am assuming this error is being caused by the phrase "ThisWorkbook" but I do not know how else to reference the workbook I want to run the macro on.
Full Code:
Sub Insert_Header()Dim wb As Workbook'Open Workbook
Set wb = Workbooks.Open("C:NewPage_Logo.xlsm", UpdateLinks:=False)Â Â Â Â
'Go out to File and copy logo and header Â
[Code] ........
I have a macro that works just find on my local machine. but it need to run from a network drive as several different people may need to access it.
A brief description of the macro.
The macro looks in an directory called unprocessed for any file named "*.csv" if the files are found it opens the file in excel and then copies data from that workbook to my workbook. after this the .csv file is closed (unchanged) and then it is moved to the processed directory.
Sub files()
Dim directory As String, filename As String, sheet As Worksheet, i As Integer, j As Integer
Dim filetype As String
Application.ScreenUpdating = False
[Code].....
I want my macro on the IR Tracker sheet to run once workbook is opened. Is this possible?
I have attached the workbook for better understanding.
An external program opens and closes Excel Workbook (OLE). What is the VBA code so when the WB is opened, Macro1 will run before OLE closes that Workbook?
View 6 Replies View Relatedbelow you will see my macro. I have a slight problem, everything works perfect, except when I change file names. Basically I keep an electronic backup every day of the file I create. So today's file will be named DOH031907, tomorrow DOH032007, Wednesday DOH 032107, etc. etc. I use the same sheet, including the same Macro every day so it does the same thing. Basically everywhere you see "DOH031907Test.xls" I want it to put the current workbook file name there. I'm new to this kind of stuff but it seems like there is an easy solution.
Sub DOHComplete()
'
' DOHComplete Macro
'
' Keyboard Shortcut: Ctrl+Shift+T
'
ChDir "K:PICDailyMetrics"
Workbooks.Open Filename:="K:PICDailyMetricsGMDEMAND2.XLS"
Windows("DOH031907Test.xls").Activate
Sheets("EDI Demand").Select
ActiveWindow.SelectedSheets.Delete
Windows("GMDEMAND2.XLS").Activate
Sheets("EDI Demand").Select
Sheets("EDI Demand").Copy Before:=Workbooks("DOH031907Test.xls").Sheets(3)
Added Code tags - Moderator
I am using the following code and putting it into a module.
Private Sub Workbook_Open()
MsgBox "Hi, thanks for opening me", vbInformation, "Ozgrid.com"
End Sub
when i open the file and click on allow macros it does not run.
I have a macro which opens two different hyperlinks in excel. Opened workbooks have are named like 1.asp and 2.asp. Both contains only one spreedsheet. I need to copy these spreedsheets to the Workbook, from which they are opened.
View 4 Replies View RelatedI´m using this code to create a messagebox±
Private Sub Workbook_Open()
Dim c As Range
For Each c In Range("E2:E126")
If c > -15 And c < 0 Then MsgBox c.Offset(, -3), vbOKOnly + vbInformation, "LET OP! Einddatum contract nadert!"
Next
End Sub
The messageboxes may only appear when the workbook is opened. The problem is that they appear when I change something in the workbook.
I have a worksheet that is a graphical display of a call floor with its cubicles. I am able to pop up a box with many details about the workstation in the cubicle (see Display pop-up box when cell selected) for more details, and would now like to go further with this application.
Our practice is to use VNC to review the end users' screens when they call us for assistance; given that I know the machine name and IP address, how can I start a VNC browsing session for the target machine?
My company uses database software called Business Objects. At the moment few people are using the program and its reports as they can't find the key ones or workout how to run them correctly. As everyone is comfortable with excel. I am creating a series of forms to guide my users to the key reports. I like this method because it is very user friendly and I can add lots of descriptions. Unfortunately my programming knowledge is limited to excel so I can't write a standalone program as you might suggest I do.
The problem:
I want to launch the non-excel files using VBA. For example, a user will click on a "Customer" button to see a second form containing a list of customer reports. The user will then click on the button containing the name of the report they want to launch. I want the file to open. I tried using hyperlinks in an excel spreadsheet which works fine for opening the non-excel files, but it isn’t nearly as user-friendly or interactive.
I have a non Microsoft program file that I can open in excel. I usually make changes to the file; save it to its native format, then open up the non Microsoft program and open that file that I just edited in excel.
If I had to simplify it, using notepad as an example.
I open the TXT in excel and make changes.
Then Save the TXT in a folder location.
Open Notepad.
Browse to the folder Location.
Open the edited file.
I did not know if there was a quick VBA that I could use to get this to work?
I have a sub procedure that launches a hyperlink, but i have some conditionality so that if a cell (J3) is blank it pops up a message letting the user know they need to populate cell (J3). My problem is that if cell J3 is blank it pops of the message, but it still launches the link.
Sub NEWShyperlink()
Dim s As String
If Range("J3").Value = "" Then
MsgBox "Make sure you have entered the ticker symbol into cell J3!", vbExclamation, "Can not get News/Press Releases"
[Code] .....
I have a list of URLs and I wrote a small VBA code to launch them when the user clicks on a button, however, it launches in Internet Explorer. Is there any way to get it to launch in Firefox instead?
View 2 Replies View Relatedhow to launch windows explorer from a path that resides in a cell using the code below.
Sub LaunchWindowsExplorer()
Dim PID As Double
Dim strRootPath As String
Const strExpExe = "explorer.exe"
Const strArg = " " '" /e,/root, "
'// Change rootpath here
strRootPath = "J:MyDirectoryWilliams,Bob 7264"
PID = Shell(strExpExe & strArg & strRootPath, 1)
End Sub
The problem I have is that once I have done this a number of times (probably somewhere between 80-100 times then I notice windows explorer starts to play up as if running out of memory. A reboot soon fixes that, however, I was wondering if there is anything I can add to the code to keep it from hogging up memory...if that is even possible.
When I say 80 - 100 times, I don't mean opening up 80 - 100 instances of windows explorer. I mean launching windows explorer, moving some files then closing that instance of windows explorer. Then coming back into excel and repeating that process.
Each time I click on an Excel file the worksheet will open up within an existing Excel worksheet that I have open. This is frustrating when you are working with two monitors or screens (laptop screen and additional monitor) and want to view each worksheet separately in separate screens. What I end up doing, which is not efficient, is I'll open the second worksheet which will open up in an existing worksheet that I have open and then I'll close it, and then launch a separate Excel worksheet and pull open the recent file and I'll have two open worksheets (not within same Excel worksheet I already have open). This allows me to grab and pull one worksheet in a separate screen and leave the other as I need both open on two monitors.
Question: is there a way to default Excel when opening or double clicking an Excel file to automatically open in a separate instance of Excel and not in an existing open worksheet?
I am trying to launch windows explorer from within excel vba. The routine below is borrowed from another post on this site and modified for my purposes.
Sub LaunchWindowsExplorer()
Dim PID As Double
Dim strRootPath As String
Const strExpExe = "explorer.exe"
Const strArg = " " '" /e,/root, "
'// Change rootpath here
strRootPath = "J:MyDirectoryWilliams,Bob 7264" ..............
Is it possible to use a "countif" function as part of a macro?
Here's the situation. In workbook "A", I want to look at a range of cells ("O19:O248") and count the number of times "apple" appears within that range. If "apple" appears 20 times, I would like the variable v_applecount to = 20 so that I can call that variable in workbook "B"
Note, I essentially want to do something like this in the Macro:
v_applecount = CountIf(O19:O248, "Apple")
I have a Macro that is stored in a specific workbook,"Projection Modeler.xls" the last line performed in this macro is the close workbooks without saving chages(this needs to be done due to the way this macro was written, and it would take too long to rewrite). If I would then like to reopen this workbook, and then rerun this macro, can I do this from inside the current macro (in a new sub routine, for instance), or do I have to write a macro outside of the workbook and then open the workbook and call the routine?
View 4 Replies View RelatedI have been looking through the forums and found the below code, but I have both text and numbers in the same range. I have attached an example of what I need to have converted.
Book1.xlsx
I'm looking for the VBA command for this function. I tried just recording a macro in where I perform the task but it didn't record anything. Tried it several times even.
View 7 Replies View RelatedI have created a form that pops up using a macro when the form is opened. It is a simple form that uses optionbuttons in a group to select Intl or Dom.
Problems:
1) How do you get the form to close once a button is selected?
2) The user can close the form without selecting a button (the X at the top).
3) How do you get the data selected onto the excel spreadsheet?
Where are Macros stored? Are they stored just in the worksheet, or are they stored locally on the machine?
View 3 Replies View RelatedMS Query can only hold 16 joins and some other restrictive requirements. Has utilized Stored Procedures to execute a query and have the results returned to Excel? Query in Excel seems very limited.
View 2 Replies View Related
