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] .....
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?
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...,
I have to run several different types of macros depending on the folders. each folder contains several excel files. so what i want to do is if a folder has a certain name run a certain macro, Here is the code:
VB:
Sub Macro1() '//Change the path to the main folder, accordingly
Call RecursiveFolders("C:Path")
End Sub
[Code]....
then call macro 2 end what syntax code should go there?
I need creating a macro that i can use in a file that will open all workbooks in a specified folder. Each .xls file has a macro that is slightly different and i want to go to one book and run one macro that will make all macros run.
View 4 Replies View Relatedis it possible to use references and concatenate them together that they are still references?
i have in:
file1.xls
sheetX:
A1: 'C:Infonic[file2.xls]sheet2'
file2.xls
sheetY:
A1: test
what i need is in sheet2 of file1
the value of file2.xls!sheetY!A1 = test
the problem is that i need to use the path to file2!sheet2 of the cell A1 in sheetX
is it possible to use sheetXA1 together with sheetYA1 as reference in sheet 2 so that i get back test as value.
I am using formulas that use ranges in outside file locations. Due to my company's file management "nuances" the file paths are exceedingly long and make it difficult to read formulas. Can I use the Define Name feature to enter the external file path and file name? My attempts so fare don't seem to be working.
View 1 Replies View RelatedI have a folder in Dropbox (important that it is stored there, it is shared with 15 people who are on a bunch of different networks) that contains 15 spreadsheets. One of them is the master, containing performance data for 14 people. The other 14 are for each individual person pulling their performance data from the master. Monthly, the master spreadsheet gets updated, and I want the user spreadsheets to update, so I can email the individual spreadsheets to each person so they can track their performance. Of course, they cannot see each others numbers, so they have no access to the folder. There are a handful of people that have access to the master. All the spreadsheets will live in this folder all the time.
The issue is that on my computer the path to the master is different then it is for other users because of where Dropbox stores local data by default, and for a lot of reasons, I cannot change it.
Is there any way to tell excel that the master (or the other 14) live in the same folder so there is no need for the first part of the path?
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.
I have a multisheet workbook where I am trying to modify all hyperlinks.
The links are all to jpgs and are currently on a web server, but I want to change them all to reference a local folder.
Eg: [url]
Obviously, I'll leave "file.jpg" alone as I don't want the specific image to change, just the place where it is trying to find the image.
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?
How to write the VBA which can multi-select the files and store the selected filepaths in the string.
View 4 Replies View RelatedI'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?
Am creating a large medical teaching database on Excel that lists JPEG images (a few hundred) and the file paths for each image into separate Excel columns using a VBA macro.
The JPEG image filenames are numbered in ascending numerical format (i.e 1.jpeg).
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?
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 RelatedSplit 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.
Is there a way that formula links in spreadsheets can use environment variables to reference formulas in other spreadsheets/addins?
For example, I have a spreadsheet that imports templates which call functions in an addin. The spreadsheet, templates and the addin could be installed into either the 'Program Files' or the 'Program Files (x86)' folders.
I am experiencing issues with the formula references when moved between 32 and 64 bit computers since Excel seems to store the full path of the addin in the Excel formula (even though it hides it when the reference resolves its path) in my templates. I would like to update the references in my templates so that they use an environment variable (set by my software installer) to always know the correct path of the addin.
In Excel 2003, spreadsheet links are edited via: 'Edit'->'Links'
In Excel 2007 and higher,'Data'->'Edit Links'
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"..................
I have alot of project folders on my harddrive.
All in format: I:/12345-costumer-projectname/
The five digits are unique for each project.
I make calculations for these projects using an excel file. In this excel I also type the projectnumber (cell J2)
Now i would like to make a button. When pressed, it checks the projectnumber cell J2, looksup the corresponding folder and saves the excelfile in PDF format in this folder.
I have found macro to find files in folders, but none which do the above.
I need a macro in a workbook to look at all the files in the same folder that have "*att*.xls" in the name and determine and copy from the range A15:W515 only the rows that have data in at least columns A, C and D. Each file will vary as to how many rows there will be and there are more than the files with "*att*.xls" in the folder. The data will be on the only worksheet in each file and the worksheet is named "G2WAttendee_xls" the data from all the files need to be copied to the file called "Consolidated webinar reports.xls" (I am using Excel 2003) and to a sheet called "Attendance Data" and added to the end of the last paste.
At the start of the macro the current file "Consolidated webinar report.xls" should be saved to a sub folder of the current directory and have the date saved added to the name. The sub folder is called "Completed reports". The data in the original file on worksheet "Attendance Data" should be deleted.
At the end of the process all the files that have had data copied from them should be moved to the sub folder "Attendance reports consolidated" (This could be done as each file is closed if that is easier).
I have headings in row 1 of the "Attendance Data" worksheet that match the headings in the various files in the folder (which will always be in row 14 of the individual "*att*.xls" files).
The folder with all the files and the "Consolidated webinar report.xls" file is at path "Z:P and S MEvaluationsWebinar series 2012-13TB".
I have up to 50 workbooks in one folder with data in a specific range. I also have one workbook which includes additional data, including conditional formatting and dropdowns. I need to copy the desired range from the first workbook in the source folder to the second workbook, then save the latter to my destination folder, using the same name as the first. I need to repeat this process for all workbooks in the source folder.
View 2 Replies View RelatedI have an Excel VBA Macro for creating/logging drawing numbers. Each drawing belongs to a job number. Each job number has a folder name containing the job number followed by a description (ie 999999 - bracket assembly) for storing drawing pdf's. The job number is only known as a 6 figure number in the drawing creation process BUT for the PDF saving process it is a string value...as my example above
I have to change my process by pre-creating the Job Number folder, then have the macro look for the appropriate folder by searching the parent folder for a sub folder containing the job number (always the first 6 figures).
I want to insert in my macro some code that searches...
The parent folder for a folder containing the job number. The macro value for the parent folder is P:engineeringdrawings (this never changes). the macro value for the job number is iOpenair (it's an entry that is entered as start the macro). Imagine the value for the job number is "999999"...so the search would be for "999999*.*" The code needs to search the parent folder, find the folder name that begins with a six digit number. Capture the complete name of the folder and store that name as a Dim value that I can call up elsewhere in my macro.
I am creating a document for work that automatically generates hyperlinks to pdf files in a given sub folder. Currently I have a section of code that opens a window to choose the folder location.
Code:
'Prompt user to select a directory
Do
Problem = False
Set ShellApp = CreateObject("Shell.Application"). _
Browseforfolder(0, "Please choose a folder", 0, "c:\")
On Error Resume Next
[Code] ........
What I actually want is to not have the window open, but excel automatically choose the sub folder location.
The directory looks like this
Z:ClientProjectDRAWINGS-2 RECORD COPIESPDF
The xlsm file is stored in the DRAWINGS folder, and I need it to point to the PDF folder.
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 RelatedI have a list of items in an excel sheet... 500 of them... and there are images for them in an other folder... The problem is those images are in 1000's and I only need those 500 images.. Images are saved as "SKU.jpg eg NS2354.jpg and we have an SKU column in excel as well .. SKU and their images have same name...
Is there any solution to pick up only those 500 images from those thousands of images?
Is it possible to save those 500 images in an other folder?
looking for some code to save to a destination
destination address is
C:Documents and SettingsstDesktopOJF
now the problem is OJF has folders named 1 to 500
so if cell d5 = 487 it will need to look in the above desination and then open the folder and save it there.
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
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?
I would like to create a code that will open all workbooks in folder "A" one at a time and then close the work book after another code is executed. So one workbook will open and while the workbook is open, I will call another code to format the active workbook and then the active workbook will be saved in folder "B" and closed and go to the next workbook in folder "A" to start all over. The folders are saved on the desktop.
View 5 Replies View Related