Macros - Changin File Name Results In Bug.
Sep 30, 2008
I have a couple of basic macros that edit formatting in a sheet that I want to send out to various people. The problem is, when I change the file name of the worksheet - the marcos stop working as they refer to the actual original file name.
Could someone please advise if (I am sure it must be somehow) it is possible to emmend the Macros in some way so that they continue to work regardlesss of what the end user decides to name the worksheet.
View 9 Replies
ADVERTISEMENT
Jun 30, 2009
Note: It should also be noted that, when "splitting" the macros below up into two different macros, they work, but then I have to play 2 macros when I only want to play one.
Just when I thought I was getting this macro stuff, I've come back to reality. I tried to combine these 2 macros together (taking out 1 "sub" and 1 "end sub") and when doing so, I got the error message
"duplicate declaration in current scope"
I thought about removing some "Dim" lines, but realized that the Dims say different things. For example in macro 1, it says "Dim Rng As Range" and in macro 2, it says "Dim Cust As Range".
Don't I need both? Do I put in an "exit sub"?
View 12 Replies
View Related
Jan 5, 2010
When I copy/paste an Excel 2003 file containing macros into an email and send and open into another computer, the macros are gone. How can I ensure the macros go with the file?
View 8 Replies
View Related
Aug 7, 2007
I have a couple of spreadsheets that I have to mail out monthly to the same recipients and I have figured out how to do that in the macro. However, an Outlook alert pops up warning me that an outside program is attempting to send a message and I have to approve the sending of it. I have tried using the Application.DisplayAlerts = False statement, but that apparently only suppresses the displaying of warnings and alerts in Excel, not the entire Office suite. Is there any way I can make this message go away and just send the e-mails w/o intervention?
Second, I have another spreadsheet which imports data from our UNIX system that I run twice a week. It analyzes, massages and reformats the data, then saves it to a text file, which then gets sent to the same recipients every time. Is there a way to send the text file as an attachment automatically through a VBA macro in Excel? If so, how? So far, I have been able to find help on how to send the entire workbook or a sheet within the workbook, but not a file other than the workbook.
View 9 Replies
View Related
Jun 22, 2009
I'm try to put together a set of macros that perform certain operations within a file. However I've got a bit stuck on the following, and I can't see where I've gone wrong:
View 3 Replies
View Related
Jan 8, 2007
I still cant figure out how to write macros. If someone could help me out with this I would appreciate it.
Currently I have a worksheet that is 20mb.
This file contains what takes up most of the space. I repeat this line many times throughout the worksheet.
010807.zip
Once again it is a zero eliminating code.
If someone could reduce the number of formulas for me, I think I could figure out how to adapt it to repeat throughout the sheet.
View 11 Replies
View Related
Jun 17, 2009
I would like to email an Excel file without the macros so the end recipients will only see the data and not be confused or excited about the prompt to enable/disable macros.
View 2 Replies
View Related
Oct 15, 2004
Is there anyway in which I can open a spreadsheet and then delete any macros which exist in that file with VBA?
For Example
Workbooks.Open "\sysdevwebwCorpAcctSuiteReportsNetProfit.xls"
''Delete Any Existing Macros Code''
Activeworkbook.Save
Activeworkbook.Close
View 9 Replies
View Related
Jul 9, 2009
1- how can i open an xls file from a macros to get some data from it?
2- how can i set a wait function, till i press a special button before macros will be able to continue.
e.g.:
[macros.xla]
View 9 Replies
View Related
Jul 20, 2009
I am running the macro below which changes details in my spreadsheet using details from an external table.
Range("C7:E36").Select
Selection.ClearContents
ActiveWindow.SmallScroll Down:=18
Range("C42:E51").Select
Selection.ClearContents
ActiveWindow.SmallScroll Down:=18
Range("C58:E69").Select
Selection.ClearContents
ActiveWindow.SmallScroll Down:=15
Range("C75:E86").Select
If I want to perform this process without having to change the directory to the t drive etc, how do I change the macro? Is there a way, that as long as each of the relevant sheets are in the same directory, i can point the macro there? Ie, as long as all saved together in My Documents or Desktop, the macro automatically looks there?
View 9 Replies
View Related
Jul 24, 2009
I've created a spreadsheet that I need to share. I've created a custom menu with macros. When I copy the spreadsheet, the macros refer to the original file with an absolute path. Is it possible to copy the spreadsheet to another location and have the macros use that path?
View 9 Replies
View Related
Jan 20, 2010
The form is completed by the project leader, then they hit a Command Button and it's sent to the Senior Manager. After the Senior Manager looks at it and approves it, he then sends it on to Admin (me!) so the project can get set up on all of our systems.
We recently upgraded to Office 2007 (yes, we are a bit late!) and now the Macro that sends the form to Admin no longer works. An example of the error is as follows:
Cannot run the Macro "FY06-10 PROJECTS 19-Jan-10 08-32'EmailtoAdmin.EmailtoAdmin'. The macro may not be available in this workbook or all Macros may be disabled.
Here is the code that I use to send it to the Senior Manager:
Sub MailtoSM()
Dim FileExtStr As String
Dim FileFormatNum As Long
Dim Sourcewb As Workbook
Dim Destwb As Workbook
Dim TempFilePath As String
Dim TempFileName As String
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
Set Sourcewb = ActiveWorkbook
ActiveSheet.Copy
Set Destwb = ActiveWorkbook
With Destwb
If Val(Application.Version) < 12 Then
FileExtStr = ".xls": FileFormatNum = -4143
Else.........
View 9 Replies
View Related
May 5, 2007
I am trying to find a way to dynamicly change CSV files .What I want is to add another column to the table with the values that based on the pre programmed parameters. For example if i have A,B,C,D columns and rows 1,2,3,4,5,6,7 I need macros to add values in column E based on the values of column B(for example)
I want to be able to pre-set those algorithms.For example for value 1033 in column B I need macros to insert value 1.25 in column E in the same row,for value 951 in column B I need 0.3 in column E in the same row. Is it doable just in Excel?
View 3 Replies
View Related
Jun 12, 2009
I'm trying to do something very simple, but I haven't had any luck searching for a function in excel that will let me do it.
Column A is a list of directories on a server, i.e. 01april, 02april, etc.
Column B is the path to those main directories, i.e. /raid0/data/documents/april/
Column C is where users can can type "1" if they want the directory or leave it blank if they do not want it selected.
Column D, if the corresponding row in column C=1, will show the complete source path/filename based on Column A and Column B.
Column E, if the corresponding row in column C=1, will show the complete destination path/filename based on Column A and Column B.
This is all working fine.
What I want to be able to do now though, is somehow, when someone sends the document back to me, create an FTP queue. I can do this with BulletProof FTP, because it allows really simple text queues.
So, what I need excel to do is, when someone has finished selecting their files, is write the results to a text file like exactly like this (without the Line1:, etc.):
Line1: <text from D2>
Line2: 0
Line3: 0
Line4: 0
Line5: ?
Line6: <text from E2>
(then repeated for each row in the excel document until column A is returning blank)
Seems simple enough.. Any ideas?
Also, an extra question--> is there any way I can make Excel list a directory structure automatically? Could it display the file size for the entire directory? I don't necessarily want it to list every file, just the directory paths and size of each directory.
View 6 Replies
View Related
Dec 20, 2013
I am facing an issue where my macro is unable to open multiple files and run the macros in it. Below is the code that i am using and it stops at boss1.
VB:
Sub allboss()
Call boss1
Call boss2
End Sub
[Code] .....
View 2 Replies
View Related
May 2, 2014
I have a file here that already has macros in it. The file is basically a excel document generator.
When you click create sku, the document will generate multiple documents based on the user inputs.
Two things in this file
1) In the generated documents the original "generator" file creates, I need generated files to have column B and D formatted to TEXT, currently all the generated files are formatted to general. (This code is in module 1, line 84 col 34.)
2) The other problem is I need to append the value in Cell N1 in the "sku data entry" sheet to be appended to the generated file names. (this part of the code is is in module 1, line 150 col 28.)
View 1 Replies
View Related
Apr 2, 2009
would like to save a spreadsheet with the contents of a cell as the name. My problem is that I have several macros in the workbook and if I change the name of the file when I save, it won't run the macros when I reopen because the filename has changed. It presents me with an error saying it can't find the macro.
View 9 Replies
View Related
Jun 28, 2009
When I have a macro open another file, it always activates the macros in it. Is there some way to disable the macros?
View 11 Replies
View Related
Feb 23, 2003
I have a file that contains Macros that I want to share with others. The only problem I have is that users have the right to enable or disable macros. If they disable the macro - some of the security functions could be lost.
Is there a way to create a macro that will not allow the file to be opened if they choose to Disable the Macros.
View 9 Replies
View Related
Jan 15, 2014
Is there a way I can share the workbooks with macros built in the excel file?
View 1 Replies
View Related
Mar 3, 2007
I just loaded Excel 2007, and then opened a workbook I wrote that made pretty extensive use of macros and code. I knew enough to first set up Excel via the "Trust Center" to allow ALL macros to run, against MS's, or more likely against MS's lawyer's, recommendations. btw, does anyone have a clue as to what "Trust access to the VBA project module" is all about? Apparently MS doesn't, for I have searched Help and the KB for it in vain. Anyway, I brought the old xls workbook in, then saved it as a macro-enabled spreadsheet, .xlsm. Then I open it, and uhg, a most distasteful surprise. Even though I specifically went in and set Excel to run all freaking macros, someone at MS has decided I am too stoooopid to make this decision, and all macros have been automatically disabled. OK, so let us click where it says "Options" on this warning bar.
A window pops up telling me that this file contains encrypted macros that have been disabled...". Do a search on that, again, MS, MS kb, even Google, and you are not going to find it (In quotes). There is ONE freaking option with a radio button now, telling me "Help protect me from unknown content" - again, I try a Google search, an MS search and an MS kb search... nothing directly addressing it; but I do the logical intuitive thing - there is a selected radio button there, so click it to unselect it. Click the idiot thing until the cows come home, darn it, it won't do a thing! I even tried an Alt-p as they have the "p" underlined - all that does is select the statement, it remains selected, tap the space bar, hit the space bar, stand on the space bar... First they disable all the macros. OK, I can live with going in and fixing that, but then deciding that I am too dumb to make that choice so don't allow me to turn them on in my own freaking code? Yes, it has been several hours and I am still pissed off beyond belief.
View 9 Replies
View Related
Jun 21, 2009
What I want to be able to do now though, is somehow, when someone sends the document back to me, create an FTP queue. I can do this with BulletProof FTP, because it allows really simple text queues. So, what I need excel to do is, when someone has finished selecting their files, is write the results to a text file like exactly like this (without the Line1:, etc.):
Line1: <text from D2>
Line2: 0
Line3: 0
Line4: 0
Line5: ?
Line6: <text from E2>
(then repeated for each row in the excel document until column A is returning blank)
View 5 Replies
View Related
May 15, 2007
I want Pivot Table Drill-down result in New excel file. when I have a pivot table, When I double click any data cell, the result comes in the new sheet(Drill-Down) of the same file BUT I want the same result to come in a New Book (a new excel sheet) and not in the same file.
View 3 Replies
View Related
Mar 3, 2009
I'm using Excel 2003 and recently when I open a file, it prompts out a Security Warning message about Marcros which at the end contains 3 choices: Disable Macros, Enable Macros, and More Info. I then click Enable Macros to open the file. Once open, I go to Tools > Marco > Run Marco (Alt+F8). A Macro box prompt out but the Macro list is empty. I try different choice inside the pull down menu "Macros in" but the Macro list is still empty.
Where can I find out the macro which triggers the warning message of Security Warning when opening the file? And how can I delete the macro if found useless.
View 7 Replies
View Related
Oct 12, 2009
I have recorded (i.e. manually as opposed to writing VBA code) a number of macros to perform a routine, however they do not work when I change the filename.
Could someone please advise on how to edit these macros (which reference a specific filename) so that they work when the file name is changed. The macros copy and paste values from different worksheets and then run another set of macros. However all macros and worksheets are located within one excel file
View 13 Replies
View Related
Oct 8, 2012
We have multiple macros that have been developed by various users in the company. Many of these macros have file paths hard coded into the macro to reference file locations with networked drives.
We are planning on making some changes to the network file structure which will break these macros. I realize we will have to go in and manually change all these. I want to ensure that the end users have the flexibility they want, but not have to touch each macro if/when folder structures change, or files migrate to SharePoint etc.
ini files, master reference Excel file...,
View 3 Replies
View Related
Feb 18, 2014
Use filter for 2 columns of a CSV file, column A and Column B for certain words. Say I apply a text filter: contains "word1" or contains "word2" to Column A. Then I get the results. After that, I remove the filter to Column A then apply the same filter to Column B. The thing is, I don't get the results in Column B, instead, I get the cells in Column A wherein Column B is filtered PHOTOS BELOW:
The data that I get for this filter are:
And Word1 you
Word2 is blank
Apply filter to Column B, get the cells filtered in Column A:
The data that get for this filter are from Column A (which are cells filtered from Column B filter)
And Word1 you
Is the blah
Word is all
The results should in a single Column for both filter:
I have more than 100 files that needs to filtered this way, is there any way I can do this automatically?
Here is what the file looks like:
The results I hope would be possible:
Automatically without having to open all the files and have the results of all of it in 1 single Excel file?
----or----
If the above is not possible, open each file and automatically apply the filter into a column in just 1 click and without having to type the same filter for each column.
I found a code to combine all CSV files into 1 Excel file, but half of my files has more than 500k rows and the Excel's limit for rows is around 1million+.
View 9 Replies
View Related
Aug 26, 2009
I have an Excel 2003 program that contains macros. One of the macros hides certain command bars and disables the worksheet menu bar. On close the opposite is true. The problem is, if a user uses the disable macros when opening then the worksheet menu bar and other command bars are still available. I would like to hide all of the data sheets and display another sheet that would normally be hidden displaying a message that the macros have to be enabled for the program to work correctly if disable macros is chosen. When the enable macros are used I would like the Error page to be hidden.
View 6 Replies
View Related
Jul 28, 2008
I have a user that keeps a maintenance log in an Excel worksheet and sends an updated copy once a week to a board member. Two weeks ago, the board member started complaining that he was prompted to enable/disable macros on opening and became worried when my user stated that no macros were used in the book. He is now concerned that we have sent him a virus.
I know the file is clean because I've scanned it, and when I look at the file in VB, there are no modules or classes present just the Sheets 1-3 and the ThisWorkbook file. None of these objects have any code in them. My user does have some macros in PERSONAL.XLS but they are not used in the workbook in question.
No one else gets the prompt for enabling/disabling macros. Even if I set my security to prompt for any macros, I get no message. I'm convinced that there must be some setting in his Excel that is causing this individual to get this message. Is there anything else other than a macro that would cause this?
View 9 Replies
View Related
Jul 27, 2006
I am trying to create formula that will show overtime worked in a given day. The code I am using is a simple one ([ cell - 8], for hours worked). The problem is when the time cells are blank/not used it shows a -8 in the cell. what I need to do to create a code that will eliminate the -8 from showing. The cell its self is taking the result from another cell with a formula and then subtracting 8 from the result of the formula in the other cell.
View 2 Replies
View Related
