Pass Multiple File Names & Paths
Jul 16, 2007How to write the VBA which can multi-select the files and store the selected filepaths in the string.
View 4 Replies
ADVERTISEMENT
Changing The File Paths In Macros
Jul 20, 2009I 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?
Cell Values As File Paths
Feb 20, 2007I 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.
Opening File Paths Where Filename Will Vary
Feb 4, 2014I'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?
Stop Working Out File Paths For Referenced Workbooks....?
Apr 29, 2009I'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?
Managing Macros With Hard Coded File Paths
Oct 8, 2012We 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...,
VBA Shell Command To A Launch Applications On All Computers (no File Paths)
Apr 14, 2006The 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?
Use Application.GetOpenFilename To Search For A Several (.jpg) Files And List The File Paths On A Worksheet
Feb 13, 2010I 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 RelatedList Workbooks With Specific Sheets From ListBox Populated With File Paths
Nov 26, 2008Split 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.
Fill Multi Select ListBox With File Paths & Open Selected Files
Nov 26, 2008i'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"..................
Change Multiple Hyperlink Paths
Nov 20, 2007I 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.
VBA To Open Multiple Files With Different Unknown File Names?
Feb 28, 2014I'm trying to build a macro to open multiple files at one time that will always be saved in a consistent drive. The problem is that sometimes there will only be one file for a month (ie only at month-end) and other times, there may also be additional files for different dates throughout the month. I wont know ahead of time how many files there will be, but they will always be saved in the same file name type that is "FILE DESCRIPTION MM-DD-YYYY". How can I build a loop that looks for a file on each possible day but doesn't error out if the file doesn't exist?
View 1 Replies View RelatedMacro Save Multiple File With Other Names In Same Folder?
Mar 27, 2014how to save multiple file with other names in the same folder
View 1 Replies View RelatedUsing Cell References In File Paths For Formulas To Create Dynamic Formulas
Dec 3, 2013I 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
File Names :: File Renaming Each With The Names I Have On Another List
Jun 13, 2008I have a task I would like some assistance with…
I have a work book that I have to copy over 70 times for over 70 work locations. As you can see, this will require different file names for each location.
I would like some have help with a code that I can use. If possialbe I like a code that will make copies of the file renaming each with the names I have on another list. Is this feasible?
Table Names Revert To Cell Names When File Is Reopened
Jan 4, 2013I have a worksheet with many tables that I use in formulas.
I like tables for a couple reasons, one being the ability to insert/delete rows without affecting the rest of that worksheet row.
Also, automatic copy of formulas/formatting is great.
But, what I really like about tables is the ability to use the naming conventions in formulas.
Problem is when I save this worksheet, after I close it and open it back up, all table references in my formulas have been converted to cell references.
Example:
Code:
=IF((SUMIF('Quote 1'!$M$28:$M$43,">"&'Quote 1'!$J$57:$J$60)*'Quote 1'!$G$57:$G$60)+(COUNTIF('Quote 1'!$M$28:$M$43,""&'Quote 1'!$J$57:$J$60)*'Quote 1'!$G$57:$G$60)+(COUNTIF('Quote 1'!$M$28:$M$43,""&tblOSSRV[Min Order Cost])*tblOSSRV[Cost / Part])+(COUNTIF(tblFam[[#Data],[Qty by Factor2]],""&tblOSSRV[Min Parts Per Line])*tblOSSRV[Cost / Part])+(COUNTIF(tblFam[[#Data],[Qty by Factor2]],"
Create Array Of File Names/sheet Names
May 1, 2008Two part question:
1) I'm relatively new to arrays, but what I need to do is generate a list of file names and the sheets within each one. I would like to use an array for this, but since I don't have much experience.... well....that's why I'm here. Can someone point me in the right direction?
2) And the second part of this.... I was planning on using the FileSystemObject to determine the files in a selected folder and loop through that list of files, opening each one and harvesting the required info (file name and all sheet names). Should I use the FSO or is there something built into Excel that might be better (and also limit the number of dependencies for this little "project" of mine).
Pass A File Name As A Variable
Aug 8, 2008I am calling a sub that I want to do a SaveCopyAs using a variable as the file name but can't get it to work. The file name displays properly in a message box but when I do a SaveCopyAs it does nothing.
This code works:
Dim A Filename: Pass The Name Of File To A Dimmed Value
Apr 17, 2007I have a file (I'll call it 'Template' where I am extracting data into a new workbook. I refer back to my Template in my code, but I have now come across an issue. I will save the template using different country names, for example, if I am populating it with UK data, I will call it 'Template - UK'. In my code, how do I pass the name of this file to a dimmed value. So If i have the template - Uk open, can I pass the filename onto a dimmed value? So instead of having
Windows("template - UK").Activate
I want to have something along the lines of (although I tried this way but it didn't work):
Dim template As String
template = Application. ActiveWorkbook
Windows(template).Activate
Folder Names Instead Of File Names/macro
Dec 10, 2008I need to make this macro read FOLDER names instead of FILE names. When I posted this question yesterday to get this macro, I wasn't told that each file in its own folder. I need the folder names now.
_____________________________________
Sub test()
With Application.FileSearch
.NewSearch
.LookIn = "C:Ford"
.SearchSubFolders = False
.Filename = "*.*"
.FileType = msoFileTypeAllFiles
If .Execute() > 0 Then
For i = 1 To .FoundFiles.Count
Cells(i, 1) = .FoundFiles(i)
Next i
Else
Cells(i, 1) = "No files Found"
End If
End With
End Sub
Pass Chosen File & Path To Variable
Dec 1, 2006I am running a macro which ends up showing the save as dialog box. The name is correct (data) and the type is correct (XML files) but no matter what I try the file path is not right. Here is the section of
sDataFile = Application.GetSaveAsFilename("data.xml", fileFilter:="XML Files (*.xml), *.xml")
Set fs = CreateObject("Scripting.FileSystemObject")
Set js = fs.CreateTextFile(sDataFile, True, False)
Set f = fs.GetFile(sDataFile)
sFilePath = f.parentfolder & ""
Set f = Nothing
How do I set the file path? I have already seen lots of answers to this but they are based on changing the path permanently or on there being no dialog box already open. I need total automation with the user not being able to see any of the save process.
Pass .txt File Text To Variable After Nth Delimiter
Aug 20, 2007I am having a problem reading from a file. I am working with some old code. I am reading information in from a txt file. The information on the txt file is separated by commas on each line. My question is, how can I read in for example, the third section on the first line. By section I mean...
line 1:
section1, section2, section3, section4, section5
I want to read section3 into a string variable. If anyone can help me please post. I am having the hardest time finding this online.
Pass Data From Userform To Excel Sheet Using File Path?
Jan 18, 2012How to pass data from userform to excel sheet using file path?
View 4 Replies View RelatedOpening Excel File From List Of File Names Located In Single Sheet?
May 31, 2013loop and range function to apply in the below code through which I can avoid writing code for all the rows.
I am trying to open excel files located in single folder from files name (along with the path) in single worksheet (Column B and Row 1 to 500).
I have created follwing code which opens the file and then runs a macro in it.
a Sub Test()
Dim strFName As String
strFName = Sheet1.Range("B2").Value
[Code].....
Extract Data And File Names From File In Folder And Paste Into One Sheet?
Jan 13, 2013The code below looks at file names in column A and then goes to a folder and opens and copies the data in range c2 -lastrow from each file and pastes the data into sheet2.
how I could add to the code so that it also inserts the file name in column c?
It would make it easier to track the data in column B.
VB:
Sub CopyFromFile()
Dim fPath As String
Dim lRow As Long
[Code].....
I have attached a sample workbook. The list of file name is in sheet 1. An example of the output is in sheet 2. The data in column A is dummy data generally spans 100's of rows not just 10 as in the example.
The purpose of the code is to be able to put a list of file names in column a in sheet 1 and extract data from those files in a folder. The data extraction works fine. The reson for adding the file names is so that I know what file the data came from.
Pass Multiple Arrays To Procedure
Sep 10, 2007I have a procedure that I want to pass two arrays to. When I pass a single array it works fine but as soon as I try to pass two arrays I get a syntax error. Both arrays are declared exactly the same way and are used in the same way. Am I limited to passing only one array per procedure?
View 8 Replies View RelatedCopy File Names From A Directory Into An File
Mar 11, 2007Is it possible w/ some vba code to copy the names of files, either from a search or a specific directory to an Excel Worksheet?
View 9 Replies View RelatedPass Multiple Tables Into Custom Interpolate Function
Jan 14, 2010I have 5 worksheets each with tables that are formatted the same on each sheet. I have named each table as a named range (ex:filter5tsd15BSF). I have a userdefined function that interpolates values for a single table in a vba module. I can make this function work for a single table by calling the function as =Linterp(filter5tsd15BSF,C12) so I know the function is OK. However, I really rather program this some way that all the tables get passed to the function and then a select case for the conditions for which filter and which TSD (15 or 25) will then select the correct table.
View 2 Replies View RelatedPass Multiple TextBox Values To Cells In Loop
Feb 27, 2008I've created a variable number (i) of text boxes at run time (i also named them, "txt"&i, at the same time), i'm now trying to get the values entered in these to fit into a column that has been inserted during the same sub routine. I'm having a bit of trouble refering to the text boxes though, this is the patch of code i'm struggling with:
Dim TextBox As String
For i = 0 To 2 * NumVar - 1
TextBox = "txt" & i
ActiveCell.Offset(i, 0).Value = TextBox.Value
Next i
I also tried this:
Dim TextBox As Object
For i = 0 To 2 * NumVar - 1
TextBox.Name = "txt" & i
ActiveCell.Offset(i, 0).Value = TextBox.Value
Next i
Pass Multiple Non-contiguous Ranges Into A User Defined Function
Dec 23, 2009I am trying to call a function that calculates forecast error (wMAPE). This function needs to be able to handle passing in non-contiguous ranges. I can't seem to figure out how to do that.
Function wMAPE(Forecasts As Range, Actuals As Range, Weights As Range) As Variant
Dim Denominator As Double
Dim Numerator As Double
Dim i As Long
Dim Fcst As Variant
Dim Act As Variant
Dim Wt As Variant
If Forecasts.Cells.Count Actuals.Cells.Count Then MsgBox ("Error: Arrays not same size")
If Forecasts.Cells.Count Weights.Cells.Count Then MsgBox ("Error: Arrays not same size")
Denominator = 0............