Invoking File Locations Using A Dynamic Variable

Jul 5, 2013

Trying to copy records from one spreadsheet to multiple spreadsheets located in different directorates. Although I can create multiple Macros to address the need, I want to use ONE MACRO that utilizes two variables to represent:

1) The targeted file directory
2) The targeted file name

Items 1) and 2) are stated in two cells within the main spreadsheet and as you can already imagine, these values change IAW the name of the target file.

This is what I am trying to do:

File cell A1 = "C:ORLOAcquisition"
File cell A2 = "target.xlsx"

Basically replacing the code below

ChDir "C:ORLOAcquisition"
Workbooks.Open Filename:= "C:ORLOAcquisition arget.xlsx" , UpdateLinks:=3
with, ChDir ""A1"" Workbooks.Open Filename:= ""A2"" , UpdateLinks:=3

I was able to do it with target-spreadsheets located under the same workbook but my code does not work when trying to activate workbooks located in other directorates in the network.

View 2 Replies


VLookup With Dynamic Column And Row Header Locations

Jan 29, 2014

I'm trying to pull information in one spreadsheet (SS1) from another spreadsheet (SS2). I've found that the following works, except when SS2 is closed. Is there a way to do the following formula so that it will work even if SS2 is closed?

=VLOOKUP(A19,A:H,MATCH("Column Header",OFFSET(1:1,MATCH("Row Header",A:A,0)-1,0),FALSE),FALSE)

Link A19 is the lookup value
"Column Header" is the column header we want to find
"Row Header" is the unique row header for the headers of the table

The Column Header could appear anywhere in Column A to H, and the Row Header can appear on any row in the spreadsheet!

View 3 Replies View Related

Find Amount In Variable Call Locations That Change On Each Spreadsheet

Feb 15, 2012

I am acquiring multiple spreadsheets that do not always match row number, due to additional information on some sheets. By this I mean that the information may be in row 31 on on sheet and row 39 on another, the column location is the same each time. I have been trying Vlookups, indexing and matching plus combination formulas - with no luck. How to get the information I need with a moving cell reference?

I can provide a small copy of a workbook, if needed.

View 1 Replies View Related

Moving File Locations

Mar 5, 2010

I have to select about 200 odd files from a folder, and move them to a different folder. The problem is, I have to manually select the 200 files from a list of approx 10,000 (not in order).

I have a list of the filenames (with extension) in an Excel Spreadsheet, and I'm wondering if there's a way to automatically move the files using an Excel macro or something?


File location = C:EBDumped
and I need to move the 200 files to C:EBSent

So I would need to move ONLY the files where the file name is in the Excel list.
The file type (extension) is always the same, but the name (and length of the name) is different.

Is this possible using Windows XP with Excel XP?

View 9 Replies View Related

File Locations (code To Specify Where A Database)

Oct 27, 2008

I use the below code to specify where a database is:

View 2 Replies View Related

Replace Multiple File Link Locations

Nov 16, 2006

I have a file where the links to external spreadsheets need to be updated on a monthly basis. There are a couple of hundred cells containing links along the lines of: =IF(WEEKDAY(F2)=1,E11,IF(WEEKDAY(F2)=7,E11,'J:DAILYDaily ReportUKSeptember 2006[daily_file_AsAt_01Sep2006.xls] Stock - Consolidated'!$G$5))


=IF(WEEKDAY(F2)=1,E10,IF(WEEKDAY(F2)=7,E10,'J:DailySeptember 2006[Cash Flows -Sep06.xls]1st'!$I$40*-1))

As you can see the month is shown in 3 ways...

September 2006
01Sep2006.xls and

I want to be able to enter a month and year and have all the links update...i.e if I type in October 2007 I want the links to change to:...............

View 4 Replies View Related

Keyword Call When Invoking A Subroutine

Nov 18, 2009

Am I right to say that "Call" is not mandatory and should be used only to invoke subroutines that do not return a value?

If you use the keyword "Call" to invoke a subroutine that does return something, the returned variable will be discarded.

View 10 Replies View Related

Invoking Procedure From Quick Action Toolbar?

May 16, 2013

I've developed a fairly simple procedure which lets me reformat cell backgrounds in a worksheet after various cut/paste operations.I've created a template called BASIC.XLTM which has this code in it. I've added a button to the QAT to enable me to invoke the procedure, but when I click the button to do the reformat, it works for the active sheet, but it also invokes another instance of BASIC.XLTM.

View 2 Replies View Related

Macro To Download Csv File From A Dynamic Date Based Url To A File Location

Feb 23, 2014

I have a requirement to download a lot of historical data files from the archieve of an website. The url goes something like this: [URL]....

This downloads file for 21022014 ie. 21-Feb-2014. I need to be able to have a facility to have a selection criteria on my user form (in excel) where I specify a date range and the macro automatically downloads all the valid excel files available within that date range (files for Saturday, Sunday and some holiday dates will not be available in the website archive database) one after the other (like at single clcik of button) into a specific location (predefined viz.. c:/Users/EOD files/) on my laptop. Best would be if I am able to select the save location run time by using a 'Browse' like feature where I go and choose my local laptop folder...same type when we try uploading a file from our laptop to the web..

View 1 Replies View Related

Dynamic Variable Name

Feb 16, 2007

I am trying to assign a value to a variable based on the value of another variable. I have about 15 items each with about a dozen specifications to them. I'd like to compare the specifications of "MyItem" to those of "OtherItems". Problem is MyItem changes constantly, and I don't want to hardcode the values for all of these items for every possible value of MyItem. Plus, I would have to create new code every time there is a new item. It just seems like REALLY bad coding. Please see below:

Sub MyItemHardcoded
Dim MyItem As String
Dim OtherItems As String

Dim MyItemSpec1 As Long
Dim MyItemSpec2 As Long
Dim MyItemSpec3 As Long
Dim MyItemSpec4 As Long
Dim OtherItemsSpec1 As Long
Dim OtherItemsSpec2 As Long
Dim OtherItemsSpec3 As Long
Dim OtherItemsSpec4 As Long

I was trying to use a dynamic variable name to cut through this.
Sub DynamicVariable
Dim MyItem As String
Dim OtherItems As String

Dim ThisItemSpec1 As Long
Dim ThisItemSpec2 As Long
Dim ThisItemSpec3 As Long
Dim ThisItemSpec4 As Long
Dim OtherItemsSpec1 As Long
Dim OtherItemsSpec2 As Long
Dim OtherItemsSpec3 As Long
Dim OtherItemsSpec4 As Long
Dim ItemNames(1 To ItemCount)

I tried using the evaluate method, but it returns an error when the expression evaluates to text - even if that text is a variable name. I've tried some off-the-wall ideas as well, but nothing seems to work. There has to be an easy way to do this. I'm just totally missing it.

View 9 Replies View Related

VBA Code For Opening File With Variable Version Number In File Name?

Jan 29, 2014

I have a file that I save with a new version number each time I make major changes. The file name currently is: "Telephony Equipment Inventory v26 (Summary).xlsm". The "26" is the variable number. give me the vba code to ensure I open the file with the highest version number?

View 6 Replies View Related

Sorting By Variable File Instead Of Specific Named File?

Jul 18, 2014

I recorded what I wanted but don't know how to generalise it so regardless of sheet name it can be sorted instead of specifically looking for "leanne final test" worksheet and sorting it by precise cells.

I have starred the section out so you don't have to focus on the rest of the code. Initally I ask for the file to be opened using WeeklyFN, would I need to use it again somehow ?

View 4 Replies View Related

Combo Box Dynamic Range Variable

Jul 22, 2008

If I had a combo box that look at 10 item in a range A1:A10 and the output value was in B1

Say a user added a new Item in A11. How could I get the range of the combo box to automatically extend to A11 or A12 if two new items are added.

I am trying to avoid having the input range as A:A and having lots of empty values?

View 9 Replies View Related

Dynamic Array As Public Variable

Mar 6, 2010

How can I make a dynamic array public?
I have to Dim it so it stays valid only inside the sub.

Public MyVar()

Sub test1()

Dim MyVar()
ReDim MyVar(1 To 4)
For x = 1 To 4
MyVar(x) = "ffffff"
Next x
End Sub

Sub test2()

For x = 1 To 4
Range("A" & x) = MyVar(x)
Next x
End Sub
Error I get is 'MyVar(x) = Subscript out of range'

View 9 Replies View Related

Clear Cell In Variable Dynamic Range

Feb 21, 2008

In my macro, NextRow is defined as the first empty row in a set of data (NextRow = Range("A1000").End(xlUp).Row + 1). How would I clear the contents of Columns A:H in NextRow in VBA, keeping in mind that NextRow is always different for each worksheet I'm working with?

View 2 Replies View Related

Calculating Average In For Variable Last Row And Copying Across Another Dynamic Range

Apr 28, 2014

I'm trying to calculate the average for a range that begins with cell B15 and has various end points, depending on the day (since I'm pulling 2 actual years of data that strips weekends and holidays, as opposed to going back a set amount of days/years). Syntax for cell B4 to reflect the average of range B15 to LastRow? I tried several things and it didn't work. Rows 1-12 are being reserved for the summary calculations that will then be pulled into the final Dashboard.

View 1 Replies View Related

CSV Import: Dynamic File Name Substitution

Nov 20, 2006

I'm trying to get a user friendly button to simply select then import a csv file into excel. I recorded a macro which runs on clicking a command button. below is the

Sub csv1()
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:Documents and SettingsBatDesktopCSV_53415_20061120_091752.txt" _
, Destination:= Range("A4"))
.Name = "CSV_53415_20061120_091752"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437..............................

View 3 Replies View Related

Code Dynamic File Path

Jun 14, 2007

I have a question. How to code the filepath name to make sure it change when i move my files from one directory to another. Example:

I need to update 4 files from this directory, but i am moving them to another directory.
c:documents and settingTesting
The file being able to follow which ever folder.

View 6 Replies View Related

Dynamic File Selection And VLookup Function?

Jun 22, 2013

I will receive one master file which contains the table and it's values and I have another working sheet where I will update the require details. Every time I will copy the entire master data into working file in order to extract the values by using vlookup function. note that the master file has different names each time. So, that if I use vlookup by taking master data path but not getting the results.

I am looking one macro code to select file (master data) and paste the values based on vlooup references in workings sheets. Note this master data file name may change but reference range always same.

View 9 Replies View Related

VBA Open File In Dynamic Named Folder

May 16, 2008

Anyone up for getting me started on code to open a file in a "dynamic" directory, specifically:


Main directory is G:REPORTS
The file name is "Pivot_xx-xx-xx.xls"
The directory where the file is located is G:REPORTSxx-xx-xx
xx-xx-xx is yesterday's date

View 9 Replies View Related

Dynamic File Path Depending On Cell Data

May 18, 2007

I think this is possible but cant find anything on it ........

View 9 Replies View Related

Dynamic Hyperlink To File Names Found On Local Drive?

Mar 31, 2014

I want to list file names from a directory and sub directories and hyperlink them. I have been able to list the file names onto an excel sheet, but I cannot get it to hyperlink to the file.

'Force the explicit delcaration of variables
Option Explicit
Sub ListFiles()


View 1 Replies View Related

Variable For File Name Or Path Name?

Jun 25, 2014

How could i get the below code to work with a variable as the file name?

Or could I use something like "thisfile" to determine where to import to.

[Code] .....

View 5 Replies View Related

Pass A File Name As A Variable

Aug 8, 2008

I 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:

View 9 Replies View Related

Open Variable File Name

Jan 20, 2010

I am trying to open a external excel document using this code.

View 9 Replies View Related

Loading A Variable File Name

Dec 5, 2006

I am using the following Macro to load a selected sheet:

Windows("Prototype Worksheet.xls").Activate
Windows("Test Data.xls").Activate
Windows("Prototype Worksheet.xls").Activate
Windows("Test Data.xls").Activate
Application.CutCopyMode = False

This works fine as long as I load the file named Test Data, lolz, but on the line"Application.Dialogs(xlDialogOpen).Show", I actually select the file to be loaded. how can and what should I change on the line "Windows("Test Data.xls").Activate" to achieve this

and also

at the very end of the macro I want to close the loaded file without the save box coming up.

View 9 Replies View Related

Linking To A Variable File

May 1, 2007

I am in a workbook created by the the macro (the variable name is strtempfile)

I then open a new workbook called Missing stocks.xls (this name never changes)

This workbook has vlookup links to the strtempfile.

I cannot work out a way to change these links.

I have tried the following =

Dim R, c, strtempfile
R = 1
c = 1
strtempfile = D_temp_File_5

Do While cells(r, c) <> ""

cells(r, c +3+ = "=Vlookup([rc-3],[strtempfile.xls]sheet1!r1c1:r3000c17,17,false)

I get a false answer, even though the number being looked for does exist.

I have also played with the idea of simply changing the links and having the do while loop simply copying the cells with the updated formulas into any new rows that need them, but I am not sure how do do this either?

View 7 Replies View Related

Link With Variable File Name

May 18, 2007

I have a 'master' worksheet, which contains a list of active workbooks. Each row on the master contains several data elements from each of the active workbooks:

File#, Date, Status, Customer, Etc.

This is accomplished by copying a row of data in the active workbook and pasting as a link on the master. When an active workbook data element is changed (date, or status, for example), the link on the master changes as well. Then the active workbook is closed until needed again. The linked data on the master allows for an overview of active files, and for sorting based on certain criteria, such as the next date to action the file.

What I now wish to do is eliminate the process of copying & pasting; this would be done, in theory, by putting a formula in each cell of the master that references a variable: the file number, located in column A.

Thus, where the pasted link formula reads:
='C:Active Files[4545.xls]Home'!$O$1
The desired theoretical formula would read:
='"'C:Active Files["&$A25&".xls]Home'!$O$1"

View 3 Replies View Related

Blurry Chart When Using Camera Tool / File Becomes Non-dynamic When Saved As Xlsx

Mar 24, 2012

Description:The chart shown in the file is a picture using the Camera Tool. The real chart is in the worksheet "Chts". When using the dropdown & radio button, the chart is supposed to change accordingly.

Problem 1:The picture from the camera tool seem to be blurry. The lines also seem thicker. Any rectifications for this?

Problem 2:If I save this file as "xlsx", the chart will not change dynamically when the dropdown is changed.

2003 version


2007 version


View 6 Replies View Related

Creating String Using Variable File Name?

Dec 18, 2012

I'm trying to check to see if a file exists using VBA but the routines I see use a string that has the path and then the file name. I have a fixed path C:VBtesting and the a variable file name. I'm using a file name that consists of a username & date such as cwilliams201212. I'm having problems getting the variable file name into the string.

View 1 Replies View Related

Copyrights 2005-15, All rights reserved