Managing Macros With Hard Coded File Paths
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
ADVERTISEMENT
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
Apr 10, 2009
All I need is to use the forecast function =FORECAST(I902,F832:F901,I832:I901) but want to substitute a hard coded from and to value list, such as something like =forecast(71,F832:f901,{1-70})
That is, forecast case 71 given a range of 1 through 70. What is the correct syntax to give a from and to value? Curly brackets don't seem to work, and I don't want to do a list like {1,2,3,4,5,6,7...} which would be a pain.
View 9 Replies
View Related
Jan 31, 2014
I want to copy all rows from my input sheet that have a date less than the date in cell B3 of the output spread sheet and are contained in the stock table (range A7:B17) Sedol in column B Is a unique identifier. I started to do this but am lost.
View 6 Replies
View Related
Feb 6, 2014
I am trying to divide hard coded financial data by a named cell that will allow me to toggle viewing the financial data in dollars, thousands of dollars, and millions of dollars. I have named my toggle cell _000s and I have used Go To Special to highlight all of the hard coded data in my financial spreadsheet. Once I have done this I would like to Copy my _000s toggle and divide the hard coded values by this cell. I am using copy and then paste special divide by but I am having no luck with this tactic.
faster more efficient way to go about this rather than individually changing each hard coded item?
View 3 Replies
View Related
Aug 20, 2014
I am trying to multiply a range of cells by a cell reference. The cells currently have hard coded values in them. I know with past special you can multiply a range of cells by a copied #. I want a similar function to that just instead of a copied cell its a cell reference. No VBA.
View 5 Replies
View Related
May 16, 2014
I pulled a set of financial statements from a database into Excel. All of the values are hard coded but I would like to make them dynamic, while maintaining the original content. For example, If cell C3 is a revenue figure for 2012 ($1,000) I would like to link it to cell C5, which will be a currency conversion rate (2.0). So I would like cell C3 to read =1000*C5, which would equal 2000, and for cell D3 to equal 2013 sales*D5, etc. I would also like this to go across a specified range so that I can also change operating expenses, taxes, etc over a number of columns (dates) that I define, all linking to the conversion rate for the given year. Having another tab where you link and anchor is an option but I would prefer something in VBA where I can qickly include this functionality with new workbooks that I download.
I am using Excel 2010.
View 5 Replies
View Related
Oct 22, 2009
I have a number of statements within the Sheet Event Code (Excel 2007). Three times lately I have added a column and had to go back into the code and find all of the references that needed changing to reflect the new column.
I have been working on this for a couple of days and even tried EE, but to no success.
I have read that Defined Names / Constants should be used as often as possible, but even trying that, the VBA code errors out or "hangs up". Even within Bill Jalen's book (VBA and Macros 2007), there is nothing that addresses this, especially using Intersect.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
On Error GoTo mEnd
Set rng = Sheets("Log").[F14:F10000]
If Not Intersect(rng, Target) Is Nothing Then
If Target = "" Then
With Sheets("Log")
View 9 Replies
View Related
Mar 10, 2014
When I use the folder path on my computer it works great. However, Dropbox is in slightly different locations on other computers that would need to run the macro. Is there a way to get to the files without modifying the code for ever computer? The only difference in the folder path would be everything up to where Dropbox is in the path and would need to include usernames as below.
[Code] .....
View 5 Replies
View Related
Feb 20, 2007
I have a file which I am using at work to compile production shift reports. I have created a sheet called Live Report and created a macro called 'New Day' that copies the entire sheet and renames it as yesterdays date (using a separate sheet called Data with a =today()-1 and text formulas thus the sheet tab for 14/2/07 is called 14 Feb) it then clears certain cells on the Live Report sheet so that information can be entered again.
My problem is that I want to now create a table of values that compiles values from each sheet for the month. What I wanted to do was have the names of all possible sheet tabs listed in column A and then as we get to each date the links will become active for the 20 or so values on each sheet. So each row will have the sheet tab name in column A and then columns B to R will have links to that sheet. As I am lazy and the linked values will always be in the same location I just wanted to drag the first row of links down the table using the value in Column A as part of the filepath/link.
My problem is no matter how much I try I can't use cell values in filepaths. If this is impossible I would like help with a macro that can find the right row in the table based on todays date and copy certain information.
View 9 Replies
View Related
Feb 4, 2014
I'm trying to write code that will open files within a specific folder. However, the end of each filename is going to vary over time to the point that I'll never be able to predict the exact filename.
Is it possible to write something like:
Code:
Set wb = Workbooks.Open "C:\folderfile_filenumberxxxx.xlsx"
Where the "xxxx" signifies the variable element of the file name that could be any four characters? Failing that, is there a similar possible alternative?
View 1 Replies
View Related
Jul 16, 2007
How to write the VBA which can multi-select the files and store the selected filepaths in the string.
View 4 Replies
View Related
Apr 29, 2009
I'm working on a sheet which references a sheet in another workbook. I want to refer to the other workbook simply by it's filename (Forecast.xls) but whenever I save and reopen Excel has replaced the filename with the full path (C:Documents and SettingskoconnorMy DocumentsAuto-sheet project[Forecast.xls])
This is quite inconvinient as I am eventually going to want to move both workbooks together to a different location. Is there a way to make Excel stop working out full file paths and just look for the filename in whichever folder the active sheet is sitting in?
View 3 Replies
View Related
Apr 14, 2006
The VBA shell command is as follows:
programPath = "C:Program FilesInternet Exploreriexplore.exe" ' works
'programPath = "iexplore.exe" ' does not work
Shell programPath + " " + fileToLaunch, vbNormalFocus
but the drawback is that the invoked program (iexplore.exe, at least in my case) needs to have the FULL PATH to where the program exists = the "C:Program FilesInternet Explorer" which may or may not work on someone elses computer. This hardcoding will not work and is not transportable.
Is there a trick to find where the executing program lives? or launching it without the path?
View 9 Replies
View Related
Feb 13, 2010
I am trying to use Application.GetOpenFilename to search for a several (.jpg) files and list the file paths on a worksheet.
View 2 Replies
View Related
Nov 26, 2008
Split off from Fill Multi Select ListBox With File Paths & Open Selected Files
if I can add a other request I just thought of.
If a sheet is also in one of the workbooks list, can I add a link directly to it?
For example, Workbook1 has a sheet named "MARCH".
When i'm working on sheet "MARCH" I want to be able to click on a button to see the Userform I created from the script above and see if the sheet "MARCH" is present from the other workbooks listed, if there is a sheet "MARCH" in the workbooks listed, I want another listbox to show what files contain sheet "MARCH" and link to them.
View 9 Replies
View Related
Nov 26, 2008
i'm having a problem with a script I have (script is below). What i'm doing is running a script that displays files in a certain folder, those files are then displayed on sheet1 column O and are linked so when they are clicked upon, the file will open. I also have a combo box with a input range of $O$O. That puts the values in column O in the combo box. Now here is a problem and request.
Problem first, when I select the file names from the combo box, they don't open the file, all it does is display the file name in combo box and that's it. how to I get it to open up the file? Now for a request, I have another sheet that is almost exactly as the one i described above. The only thing different I want is when someone clicks on a file name from the combo box, instead of it automatically linking to the file, I want them to click on a button to display the file.
Sub HyperlinkXLSFiles()
ActiveSheet.Unprotect
Dim lCount As Long
Application. ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False
On Error Resume Next
With Application.FileSearch
.NewSearch
'Change path to suit
.LookIn = "c:Sec"
.FileType = msoFileTypeExcelWorkbooks
' .Filename = " Book*.xls"..................
View 2 Replies
View Related
Dec 3, 2013
I am using a lot of linked reports that have to be rewritten each month. For example smaller formulas look like this:
=('S:PUBLICProductionJob CardsMOLDING201311 November[440A SIDE SPOILER JOB CARD.xls]Production Parts'!B$228*2)+'S:PUBLICProductionJob CardsMOLDING201311 November[440A SIDE SPOILER JOB CARD.xls]Production Parts'!B$262+'S:PUBLICProductionJob CardsMOLDING201311 November[440A SIDE SPOILER JOB CARD.xls]Production Parts'!B$292
What I want to do is extract the file path from the above formula and make it a composite of several cell references.
So what I need is to have a cell where they can change the month and another where we can change the year. So I set up several named cells that look like this:
_MONTH =11 November
_YEAR =2013
_JOBCARD ='S:PUBLICProductionJob CardsMOLDING
_PATH =_JOBCARD & _YEAR &"" &_MONTH
I tried several versions, I am hoping for something like this:
=('_PATH &"[440A SIDE SPOILER JOB CARD.xls]"Production Parts'!B$228*2)+'_PATH &"[440A SIDE SPOILER JOB CARD.xls]"Production Parts'!B$262+'_PATH &"[440A SIDE SPOILER JOB CARD.xls]"Production Parts'!B$292
View 4 Replies
View Related
Feb 11, 2008
I've seen a preference setting somewhere that allow me to specify what happens with the cursor after entering a new value in a cell. such as moving down or moving right or not moving at all?
How do I code the cursor to NOT MOVE out of the cell after a new value has been entered?
View 9 Replies
View Related
Jun 18, 2014
I have two spreadsheets of "leads"
One lead is a do not call list that has 4 separate columns of phone numbers,
The second list is a batch I purchased.
I need to make sure that the second batch does not contain the info from the first spread sheet.
I guess I could modify the 1st spreadsheet to just be one column of numbers, then I would be comparing one column to one column.
View 5 Replies
View Related
Aug 18, 2009
I have a list of about 700 guys with their names, ages and a coolness factor between 1 and 10. I have different categories that I want them to fit into based on their age and "coolness". The charts at the top of the sheet I've uploaded. Ages are on the top and their coolness rating is on the side. So for instance if somebody is 27 and has a coolness of 6 they would be lumped in the World category. How do I create an IF command that would automatically list what category they're in based on age and coolness?
View 6 Replies
View Related
Jun 27, 2013
I`m making an Costumer Database for my business in excel, I already made a form in witch I can insert, change or exclude a client. In this form I have a button that hides the actual form and show another one to perform the search.
The search is working fine with the listbox, what I need right now is that i can search someone and then I click on the client i want in the listbox and somehow get the reference and open the other form with that reference.
The first form work with an reference number that is defined as a variant, so i have a macro that see this number and bring the client with that code. In the other form I needed a macro that gets the value of the selected reference in the listbox, then hide the form, call the other one and run a private sub.
View 4 Replies
View Related
Mar 1, 2012
I have discovered that my blue highlighted code posted below isn't deleting the visible rows of filtered data as expected.
Code:
With core_data
.Activate
.Range("D5").Select
Dim rngToDelete2 As Range
llastrow = 0
lmergedelete = 0
llastrow = .Range("a65536").End(xlUp).Row 'calculate the last row visible
'filter the visible rows to include only those rows in which dupphase3 = "DUP" (ie. duplicated rows)
[Code] ....
Additionally, and I'm not sure if it's related to this, but I am receiving an error with the red line .... "Application-defined or object defined error".
View 6 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
Aug 18, 2009
In this file i did paste special values so the links are no longer there, but in my original file alot of the values you see in the cells are pulled from other worksheets ( thats just so you know to lookin values of the cells and not the formula ).
I basically need row 18 starting from Column O to self populate.
Its based off of which has 1's in the Column. I have the completed here manually just to show you what i am looking for . I usually have to go to row 3 and Enter the data manually depending on which 1's are filled in for the column.
If i could have a method of having row 18 starting with Column O to self populate based off those 1's
View 8 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
