Cell Values As File Paths
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
ADVERTISEMENT
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
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
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
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
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
Apr 13, 2006
I am trying to create a macro that opens a .pdf with the name of the pdf equal to a cell value. For a simple example: Cell "A1" contains the text "distinct" and I want the file prexisting file "C:distinct.pdf" to open when the macro is ran.
View 3 Replies
View Related
Aug 11, 2009
is 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.
View 10 Replies
View Related
Nov 28, 2013
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 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
Mar 2, 2007
I want to add the columns in the attached sheet...any 4,say A,C,D and F.....as follows,if the number in the cell is less than .8,I want use 1 in the sum,otherwise zero. I would like to do this in 1 column if possible. in the attached sheet,I have filled in manually the answer in column L....Thus row 1 contained no numbers less than .8 in the selected columns,where as row 8 had all four numbers less than .8.
View 6 Replies
View Related
Feb 18, 2012
I 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?
View 6 Replies
View Related
Nov 20, 2007
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.
View 4 Replies
View Related
Mar 31, 2007
I have situation which needs expert advice. I have about 22 Excel files of different branches with similar data. I have to link some calculated cells from each of these files into one file to show the consolidated data. There are about 18 calculated cells in each file, so linking each one is practically impossible.
View 10 Replies
View Related
May 23, 2012
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).
View 1 Replies
View Related
Feb 26, 2014
I'm trying to use Excel to build a text file based on values from certain cells in Excel 2010. This is based partially off of static text that never changes and variables that will need to change. For example, I want Excel to output a text file that has the following text: The red fox jumped over the $X twice. I would want $X to be replaced by the value of A1 of the active worksheet. I will also have several lines like that, so it won't just be a single line, but anywhere from 20 - 120.
View 1 Replies
View Related
May 14, 2014
I'd like my macro (in an Excel 2010 workbook) to copy values from three worksheet cells and append them to an existing Access or SQL database file -- without having to leave my Excel spreadsheet or open any other programs. The database file simply needs to be appended. Keeping a running list. That file isn't being used for anything else, we can set it up however is necessary to do this.
For example, Excel cells A1, A2, and A3. Representing Job Name, Job Number and Job Total.
how make VBA do this(in SIMPLE terms)? Or give me a link of where to look or what to search for? I understand VBA but not Access or SQL.
View 1 Replies
View Related
Sep 30, 2013
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'
View 1 Replies
View Related
Oct 22, 2009
I have attached the file I am working on. I am attempting to create a link to an external file based on the value of cells in column A. Then I would like to simply copy the formula down, lets say in Column B, the rows and as I do the external file reference will change depending on the value within the cell in Column A. I hope that I am making sense.
I am using Windows Vista with Excel 2007. The files will all be within the same file folder, however, there are hundereds of files so I won't be able to open them all for the indirect to work.
View 9 Replies
View Related
Dec 12, 2012
I have numerous spreadsheets that I need to open and unhide a sheet, that has XML data stored in cell A1. What I need to do is copy that data in cell A1 and paste it into a text document and save that as an XML file saved as the XLS workbook name with a date stamp.
I'm running into many issues, the main issue is the saving as current file name, and the formatting of the text/XML file.
Here is my current code, which doesn't reference the current file name and is just very generic. Once I get the saving as file-name correct and the formatting of the xml file correct, I will work on it a bit more.
Code:
Sub Test()
Dim Rng As Range
Dim wb As Workbook
Set Rng = Range("A1:A2")
Set wb = Workbooks.Add
With wb
Rng.Copy
[code]....
View 4 Replies
View Related
Jul 10, 2014
I have a Workbook that already has a macro in it that will generate multiple sheets based on certain criteria.
For each of these newly generated sheets (numbered 1-6 in the attached example), I need to be able save each of them to a PDF file based on a unique name contained in a certain cell (in this case, each named is referenced in cell Q1 of each sheet). As such, I should end up with 6 pdfs based on the attached sample files.
The PDFs would ideally need to be landscaped and should be just 1 page per sheet.
In my attached sample workbook, you'll notice that I have a "PracticePrint" macro that doesn't quite get the job done. One other criteria involves not printing any sheet with the characters "1010" in it (my PracticePrint macro is setup to do that, but it fails to do much else).
View 1 Replies
View Related
Nov 3, 2009
I am trying to import some csv files so I can combine them, but am having probs with the filename and location.
Sub test()
Dim wsName As String
wsName = ActiveCell
Sheets("Data").Select
With ActiveSheet.UsedRange
LastRow = .SpecialCells(11).Row
End With
With ActiveSheet.QueryTables.Add(Connection:="TEXT; &thisWorkbook.Path &" " & wsName &", Destination:= Range("A" & LastRow))
.Name = wsName
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells.....................
View 5 Replies
View Related
Mar 31, 2014
I have one column of data. there are currently ~10k rows, but this will increase over time. each row has either a single value (example: pepsi) or a comma separated value (google,samsung) with up to 6 commas. instead of the rows looking like this:
pepsi
google,samsung
coca-cola
I want them to look like this in one column (preferably via a formula):
pepsi
google
samsung
coca-cola
I would like the above output to be a unique, alphabetized list.
View 8 Replies
View Related
Apr 5, 2013
I need a macro to name a file using values from two cells, one is a supplier name that is in a drop down box, the other is a count. Both cells are named in the worksheet and the macro below works on the count, but not the supplier.
Sub Order_Number()
Dim count As Integer
Dim Supplier As String
Range("F13").Select 'where A1 is your cell you wish to store the count in
count = ActiveCell.Value 'picks up last count value on spreadsheet
[Code] .......
View 2 Replies
View Related
Apr 13, 2009
I am trying to summarise data from 50 different files (updated by 50 different users) into one file in one place. To do this I created a simple sumif function that links to those files. This sumif works correctly when those files are open. But when I open my output file after changes have been made to one or more of 50 input files, sumif function doesn't work when I hit update at the file opening pop up question and get #value error. If I start opening those files this error goes away and it works great again.
View 2 Replies
View Related
