Auto Run Macro When File Is Opened But Macro Stored In Personal XLSB
Mar 4, 2014
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.
View 1 Replies
ADVERTISEMENT
Apr 2, 2012
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] ........
View 3 Replies
View Related
Jun 12, 2007
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.
View 2 Replies
View Related
Nov 15, 2007
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
View 9 Replies
View Related
Dec 8, 2011
I have a simple macro that's in my default Excel startup file (Personal.xlsb - located in the folder C:Documents and SettingsUsernameApplication DataMicrosoftExcelXLSTART).
The problem is when I try to open two instances of Excel (not create a new workbook). I get the error message "Personal.XLSB is locked for editing."
I've look at other threads that talk about adding the macro as an "Add-In" (Personal.xlsb and multiple Excel's), BUT my problem is that I want to assign the macro a keyboard shoutcut (currently set to ctrl + shift + v).
I don't know of a way to assign an add-in macro a keyboard shortcut.
View 7 Replies
View Related
Oct 16, 2008
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 Related
Sep 22, 2006
I have a special use PC which will power a projector 24/7. When it reboots I want it to load Excel, open a spreadsheet, and then connect to the web to get data using web queries.
My problem is that I get a dialogue box each time the spreadsheet is opened: "Enable Automatic Refresh." I need to remotely reboot the PC from time-to-time so I can't click the button.
I have set my security levels to low and that does not help.
View 5 Replies
View Related
Jun 16, 2009
I am trying to write a macro to transfer patient personal data onto a database.
On sheet 1 the user inputs the patient surname, forename, date of birth, date of test and the test results.
On sheet 2 is the database, where patient surname, forename and date of birth is stored in columns A, B and C respectively. The remainder of the columns on each row is available for scoring test results.
What I would like the macro to do is as follows:
•Read the patient surname as input on sheet 1
•Check on sheet 2 if that surname already exists
•If it does, check if the forename and date of birth also match to sheet 1 for that particular entry.
•If a match is found then copy the test results into a column on that particular row. E.g. could be copied into column D.
•If no match is found, then all the data is entered as a new row on the database (I’ve written the code for this bit but thought I should add it in for clarity)
This will then allow all test results for a particular patient to be read off the same row in the database.
View 13 Replies
View Related
Aug 13, 2014
This is the code I use to call a macro when the macro Im calling is in the same workbook.
[Code].....
However, I would like to call this same macro when using another workbook. I copied the macro "Clearformating" and pasted into a personal macro workbook module. However when I add this code to the sheet tab it will not run the macro.
I also tried this code.
[Code] .....
View 2 Replies
View Related
Apr 11, 2008
Is It Possible To Auto Save To A File Via A Macro?
What I Want To Do Is To Auto Save Directly To A Folder On Our System Via A Macro.
The Problem Is That Firstly It Will Need To Look Up A Cell ( D8 ) To Find Out The Job Number
Then It Will Have To Find The Folder On Our System ( Its On Desktop Under Job File Folder ) Then Comes The Hard Bit It Will Need To Locate That Job Number ( Cell D8 ) To Look Up The Relevant Folder To Put It In. All The Job Files Are Labelled Up Eg.( J2663 - Parry ) Then In That Folder Is A Sub Folder Called Docs Which It Needs To Be Saved In
View 14 Replies
View Related
Oct 10, 2007
I have several macros set up as modules in my personal.xls. There is one particluar macro (for purposes here I will call it "SourceMacro") which I use repeatedly in other macros (also stored in personal.xls). What I have been doing is copying the code from SourceMacro and pasting it into other macros.
I understand that I can actually run the SourceMacro without pasting the code by using a run command. I have inserted the following code into one of my other macros, but get an error message saying that the SourceMacro can not be found. (I have checked spelling carefully.)
Run "Personal.xls!SourceMacro"
Excel 2003 / Windows XP
View 9 Replies
View Related
Aug 12, 2006
I have a macro to insert rows and add formatting in personal.xls, which will run from another worksheet when using alt+ f8 with no problems.
However, I am trying to run it from a macro in the second worksheet and have tried :
Call InsertRows
and get a Compile Error: Sub or Function not defined.
Application.Run "insertrows"
and get a Runtime Error '1004': the macro '...' can not be found.
Application.Run "Personal.xls!insertrows"
and get no error message, but the rows are not inserted.
If I copy the Sub code into the new worksheet, and then use Call, everything acts as it should.
Is the third method the correct way of calling the macro, and if so, what would cause it not to do what it is supposed to do?
View 8 Replies
View Related
Dec 29, 2011
I want a macro that will transfer the data of billing report in the data sheet of the excel file billing dec 2011.
View 1 Replies
View Related
Nov 21, 2008
I have (and will be) creating a number of macro in my personal workbook, a number of which will be run from within other macro also in my personal workbook. I am creating these to be distributed to members of staff within my work. This will be done by creating a template workbook for the job to be done, and e-mailing them all a copy. Now, I can not work out how to move the macro from my personal workbook into the new template so they can be run from there? Also, the macro that access other macro have a path to my personal workbook, will this be updated automatically when they are moved, or will I need to manually change them
View 3 Replies
View Related
Jan 2, 2009
I have quite a few macro's saved on my personal xls file. Anyway for me to save them with the current date to a specific folder?
e.g. MacroNameDate.bas
View 5 Replies
View Related
Mar 31, 2007
I am just learning a bit of VBA and developed a macro that runs fine if I used it on any workbook but when I imported it to personal.xls and tried to run it, it runs on the personal.xls itself. I tried inserting a line that activates worksheet(2) but that didn't work.
View 9 Replies
View Related
May 24, 2007
I have found several examples of excellent macro code on this site.
Is there a procedure to import a macro code ( saved as a TXT file) into the Personal.xls file?
For example, the following code removes blank spaces and converts text to proper case.
Is there an easy way to save this as a macro in my Personal.xls file?
'Macro Code to apply TRIM and PROPER functions on the selected range ..
View 8 Replies
View Related
Jul 6, 2009
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.
View 9 Replies
View Related
Aug 26, 2009
In Excel I've always had a Personal file come up when Excel was opened. It may have accidently got closed??? The personal file does not open with Excel and I am unable to find it. It is required to run macros correct?
View 13 Replies
View Related
Sep 9, 2009
I am wondering where does the Personal Macro Workbook resides. I am using Microsoft Office Excel 2007 I checked in XLStart it is not there.
View 5 Replies
View Related
Nov 2, 2011
I'm trying to run a macro defined in my personal workbook, through a VB script, however I keep getting run time error code: 800A03EC. Here is the vbs I have written:
Code:
Dim xlApp
Dim xlBook
Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Open("C:Test.xlsx")
[Code]...
If I replace the macro PERSONAL.XLSB!FormatCurr with one defined within the worksheet, the script runs fine, but I can't have the macro in the sheet.
View 1 Replies
View Related
Oct 7, 2007
There are two macros that i'd liek to add to my personal macro workbook upon startup of excel.
i tried to do them without coding but it appears that some actual coding is necessary.
1) i'd like to be able to select any # of cells anywhere on the page and hit ctrl+shift + n and have all of those selected cells read #N/A (the forumula is =na() )
2) i'd like to be able to select any number of columns (i do finance/econ research so all data is in chronological order from 1960-present along the columns. i.e. each variable is a column vector.) and be able to hit ctrl+shift+b and have VBA make a chart in a new sheet with my default settings
i've tried to do these but recording the macro hard codes the select cells for 1) and always runs into weird problems for 2).
View 9 Replies
View Related
Jun 3, 2009
The following macro does what I need when run from the active workbook however I need macro to exist in 'personal.xls' so it can be easily applied to raw reports received in daily e-mails.
Sub Add_Sundays_Data()
Dim bk As Workbook
Dim bSave As Boolean
Dim lRow As Long
On Error Resume Next
Set bk = Workbooks("C:Template.xls")
On Error GoTo 0
If bk Is Nothing Then
bSave = True
Set bk = Workbooks.Open("C:Template.xls")
End If
View 9 Replies
View Related
Mar 12, 2009
I am having trouble renaming an active sheet from a Macro I stored in the personal.xls file. I want the active sheet to be renamed to "Data_Source" and then the rest of the code can kick in. Instead of renaming the current worksheet it creates a new one.
View 4 Replies
View Related
Mar 21, 2007
I want to run a program from my PERSONAL.XLS workbook, however, after a piece of code
where I close all the open workbooks different from PERSONAL.XLS, the code stops running.
How can I contour this?
Down is the piece of code used to close the other workbooks and the msgbox I want to show, along with a DialogBox:
For Each W In Workbooks
If W.Name ThisWorkbook.Name Then
W.Close SaveChanges:=False
End If
Next W
MsgBox "Abrir apenas a Origem a actualizar, que deverá ser obrigatoriamente do mês corrente ou do anterior", _
vbOKOnly + vbInformation, "Atenção"
Application.Dialogs(xlDialogOpen).Show
View 9 Replies
View Related
May 8, 2008
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..
View 9 Replies
View Related
Aug 1, 2014
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].....
View 7 Replies
View Related
Dec 20, 2007
I have a macro that is designed to paste a row of formulas into each Subtotal line on a 30,000+ line spreadsheet. I have specified the formulas that I want to paste in the named range "formulas". The macro below worked fine for about 3000 lines, then threw the error Run-Time Error 1004: Paste Method of Worksheet Class Failed on teh line ActiveSheet.Paste. I thought it had just bogged down my computer, so I added teh ScreenUpdating and Calculation lines, but they made no difference. I also tried reducing the range to just a couple thousand lines, which also made no difference. I also have found some suggestions on this forum and others for this error.
In addition to it working fine for 3000 lines, then failing, there are a couple other weird things. One is that on the line in the spreadsheet that threw the error, the formulas were pasted in up to column AX, leaving columns AY through CR blank. The second odd thing is that this exact same macro still works on my sample data spreadsheet (which only has around 50 records).
Sub FormatTotalRows()
Dim rCell As Range
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Workbooks("latest.xls").Activate
Range("formulas").Select
Selection.Copy
For Each rCell In Range("A3040:A5000")
If Len(rCell) > 0 Then
rCell.Activate
ActiveCell.Offset(0, 2).Select
ActiveSheet.Paste
End If
Next
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub
View 2 Replies
View Related
Oct 2, 2012
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.
View 9 Replies
View Related
Oct 31, 2006
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 Related
