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


Variable File Name For Creating External Links

Apr 10, 2013

I am working on a research project of which details cannot be disclosed. I basically have many worksheets which each contain 3D positional data and I am plotting overlaid graphs using multiple worksheets. I have gotten them to work where I manually enter the names of the worksheets, but I am trying to use the CELL("filename") command to get the current worksheet's name, then using a substitute command to change certain fields, then I would like to use the string name created by those functions to call a global variable in another worksheet which defines a range. I will try to give an example.

Say I have 3 worksheets for arbitrary measurements:
1) filename 1mm
2) filename 2mm
3) filename 3mm

I would like to graph all 3 sets of data in worksheet "filename 1mm", using the fact that everything in the filenames are the same except for the 1/2/3mm part. I have a template of which I will be copy/pasting data from numerous data sets and then each worksheet has defined names "X_vals" and "Y_vals" which give me dynamic ranges for the data I wish to plot from that worksheet.

When I type the names manually--i.e. " 'filename 2mm'!X_vals" everything works fine, but when I try to create the exact same string dynamically using indirect/substitute/left/right etc, it does not let me do it. I am able to create a dynamic string with a range, such as " 'filename 2mm'!C10:C100" but then when I change it to " 'filename 2mm!X_vals" it just gives me #REF!.

I guess my question is just is there any way to use the indirect function to create a string name dynamically which references a defined name in another worksheet? If not possible with just simple excel functions, is there a way to do this with a macro? I am not exactly familiar with VBA in excel, though I know how to run macros.

View 3 Replies View Related

Inputbox Value Can Be Compared To A String Variable Or A Numeric Variable At The Same Time

Dec 7, 2008

I am trying to develope a "goto" page macro where the page value maybe 1,34,7A, 256C etc. I am not clear on how an inputbox value can be compared to a string variable or a numeric variable at the same time. This is what I have done, but when the texboxvalue is "7A" it doesn't work.

View 3 Replies View Related

String Variable To Call Defined Range Variable

Nov 10, 2006

Say you define a public range variable called Inputworksheet and you set it to refer to the worksheet called Inputworksheet. You have a separate string variable with the value Inputworksheet. How do you get this string variable value to call/control the range variable Inputworksheet?

I am getting an excel worksheet value from a lookup function that corresponds to the name of a VBA range variable. Once I have this worksheet value, I would like to use the range variable that has the same name as the worksheet value.

View 5 Replies View Related

Insert String And String Variable Into Cell

Feb 18, 2014

I'm using a userform to create a new sheet. The form already creates the sheet and names it what was typed into the userform. Now I want it to place that variable in a cell along with a string. the following code will place the variable from the form (tbname) into cell b5.

View 2 Replies View Related

Creating A Yearly Database File Using Information In Daily File

Feb 7, 2008

I would like to automatically update a 'yearly' database file with info from a file that is changed on a daily basis.

The daily file that i use has info like date, truck number, delivery stops, weight.

the database file has the similar headings.

at the end of each day this daily file is saved. I would like to have the info that is entered into the daily file automatically plugged into the yearly database file into the next available group of cells with respect to the salesperson.

This is kind of a generalization but i'm hoping to just get pointed in the right direction. If something like this involves vba then it will be beyond my ability and i'll have to do it manually, which is fine

View 9 Replies View Related

Creating A Variable Word Art

Jan 23, 2009

I am wondering if anyone as created a what to do word art, using a input variable.I.e in may example I have a person, Suzie Smith who is an Chief Manager, when I box requires her signature, I would like for it eo be imprinted going diagonal across the box. Is this possible?

View 7 Replies View Related

Creating A Formula With A Variable

Jul 14, 2008

Column A will be completely empty.

When an * is inserted into a single cell in column A, that Row number will become the variable in this equation:


i.e. if i put a * in A14, then the formula should read (=Sheet1!$G$14).

View 10 Replies View Related

Creating Variable Range?

May 18, 2013

How would I create a variable range with:

Sheets("output").Cells(I + r, 1):Sheets("output").Cells(I + r, 9)

View 3 Replies View Related

Creating A Variable Macro

May 24, 2007

I currently have the below macro:
If Cells(1, 3) = 2 Then
For a = 1 To 336
b = Round(a * 2 + 0.99)
Cells(b + 0, 3) = a
Cells(b + 1, 3) = a

Next a
MsgBox " Cell C1 is not equal to 2"
End If

where the intent is that whatever cell A1 is equal to, that is how many times over it will duplicate a number in consecutive cells before moving to the next number, ie:
etc to 336.

How can I tweak the code so that if I change the data in cell A1 to 9, I don't have to go in and change the macro?

View 9 Replies View Related

Creating String From Active Sheet Name

Mar 14, 2013

I'm trying to use the ActiveSheet Name as the folder name for images being imported into excel. For example, on the Sheet "Sample 1" I want to pull from the File Path "C:Documents and SettingsuserDesktopSample 1"

I'm getting an error, I think this code should work in regular VBA not sure whats wrong in excel?

Dim strSName As String
strSName.Text = ActiveSheet.Name
Const strPath As String = "C:Documents and SettingsuserDesktop" & strSName & ""

View 3 Replies View Related

#VALUE! Error Creating String For VLOOKUP

Apr 25, 2006

Needing to do a VLOOKUP on two criteria, I have set up a string using "&" to join together the contents of two cells. This is in a workbook with many identical worksheets. The string works for some of the sheets but in others, for reasons I simply cannot fathom, it is returning a #VALUE! error.

View 9 Replies View Related

Creating External Reference With Variable?

Feb 27, 2014

I'm wondering if there's any way to put a variable for a cell address in an external reference formula. Basically, I'm creating an abridged report of customer data that takes a single customer's info from 2 other workbooks on a networked drive and compiles the chosen cell data into one worksheet summary of their info. The base external reference formula looks like this:


I want to know if there's a way I can make the formula use a variable cell address that I can just type into another cell to tell it which row I need to grab info from. Generally, all of the customer data I need is spanned across multiple columns in one single row. The formula above appears multiple times in the sheet I'm trying to make. The first is for "Name", the next is for "Address", next is "Phone Number", etc. So for instance, if I want customer data from the main spreadsheet and that customer's info is on row 355, my first few formulas are:


Is there a way I can maybe have a designated cell where I just enter the row number I need (e.g. 481) so the formula sees that number and plugs it into the cell reference above resulting in the formulas adjusting themselves to ='F:Projects[ProjectDB.xls]Sheet1'!$A$481?

View 9 Replies View Related

Creating Matrix With Variable Size

Nov 19, 2012

I am trying to create a matrix in VBA with variable matrix size. The matrix I need will be anywhere from 3 to 9 columns and 15 to 30 rows. I want to set the size as variables and then create the Matrix using these variables, similar to:

rows = 4
columns = 25
Dim XMatrix(rows, columns) As Variant

View 1 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

Creating Date Of Manufacture From Lot Code String

Dec 9, 2013

Is there a formula that can be used to convert a manufacturing LOT Number to a Date? For example, the first six digits of our Lot Number are the MMDDYY followed by an item description (120913DKP-01). We are trying to pull the 120913 portion out of the lot number to equal a Date of Manufacture of December 9, 2013.

View 4 Replies View Related

Creating VBA Macro That Changes Specific String To A Number

Jul 7, 2012

I am having some difficulty with a pdf that I converted to an excel document because I wanted to use the data from the pdf tables in a different program I am currently working on. However, the data is in the improper format. For example, in the table it reads 2-1/8 as string and I want it to be the number value 2.125 . Likewise if the value in the table reads 5-1/4 I want it to automatically convert it to something that will be read as the number 5.25

View 4 Replies View Related

Creating Range Object Using Variable Indices

Mar 28, 2008

how do we do something like Range("A1:B2") using an index for the cells. For example Range(R[0]C[0]:R[i]C[j]) which doesn't work. I know about Cells(i,j), but that only returns a range object of a single cell.

View 9 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

Creating String / Lookup / Validation From One Reference Sheet

Apr 18, 2014

I have an endless list of information that I need to turn into a text string, lookup with a validation on the end.

The easiest way is to show you so I've attached the info and what result I want.

Attached File : Test.xlsx‎

View 3 Replies View Related

Creating A User Form With Variable Number Of Controls Buttons

Sep 4, 2008

Is it at all possible to create a User Form where the number of CheckBoxes will be linked to the number of entries in a cell range?

For example I have a 3 records in the range A:A called "Blue", "Green", "Yellow". I want to have a user form with 3 CheckBoxes with the same caption names.

View 9 Replies View Related

Creating Autofilter That Returns Records Based On Date Variable

Mar 13, 2013

I have a strange problem I cannot solve in that I'm trying to create an auto filter that returns records based on a date variable.

If I set the code to equal the date variable, it returns information. If I set the code to return records AFTER the date, it returns nothing.

Code to return records on the date:

Dim startdate As Date
startdate = Format((Now), "dd/mm/yyyy")
ActiveSheet.Range("A:B").AutoFilter Field:=2, Criteria1:="=" & startdate

This returns data, however if I slightly amend it to get records on or after the date:

Dim startdate As Date
startdate = Format((Now), "dd/mm/yyyy")
ActiveSheet.Range("A:B").AutoFilter Field:=2, Criteria1:=">=" & startdate

It returns nothing. Is there a syntax problem in my code.

View 1 Replies View Related

Indicate That A Variable Is String

Oct 30, 2008

I understand that a everything is already considered to be string, but how would I phrase this?

If txtBolow = (a statement which indicates the textbox is full) Then

txtBolow. SetFocus
txtBolow. SelStart = 0
txtBolow. selLenght = Len(txtBolow)

If txLowl = ("") Then
txtLowl. SetFocus
txtLowl. SelStart = 0
txtLowl. selLenght = Len(txtBolow)

What would I use to indicate that the user has completed the data in the textboxes, and the old data is highlighted to make way for the new?

View 9 Replies View Related

Using A String Variable For Sheet Name

Aug 13, 2009

I am trying to use a variable for the name in the Sheets command so that I can put in the names of the CSV file that I have captured in a variable. Here is the macro that I recorded using Excel 2007:

View 4 Replies View Related

Get Set Of Chars From A String To A Variable

Dec 24, 2009

I believe I should use the INSTR function, but I simply dont understand its use very well. I have a string in a cell, "MEAS/SPHERE,F(QUA_1),5", the characters I need start at the "(" and end at the closing ")", ref: (QUA_1). The start and end will always be the same, the length of characters and the spelling of characters be anything. Thus, QUA_1 can be CAL_1, CAL_2, SOMEOTHERTEXT_1 or 187bgo_199, etc, always enclosed in "( )". I wish to place the characters and the closing "( )" into a variable as a string.

View 5 Replies View Related

How To Get Set Of Chars From A String To A Variable ..

Jan 8, 2010


This one has two sets of quotes, with the information from the noted post I can retrieve the string "S5a09b-90", now that I have a double set of brackets "( )" how do I get the value for the second set of brackets, "CAL_15"?

In the quoted string, CALIB/SENS,S, is constant. The first set of brackets are constant (string length is not), the second set of brackets are constant (string length is not).

View 7 Replies View Related

Parsing A String Variable

Dec 8, 2006

It's been a long time, but I use to parse a string in C.
How do you strip off the long directory path of a string to just the filename and extension?

String Example:
S:2006 jobs602f EID Bass Lake Recycle BPSConstructionSubmittal Review16940-Instrumentation602f-16940-01.rev.doc

Wanted result:

View 9 Replies View Related

Use String-Variable In Formula

Sep 16, 2009

if it is possible, to use a string variable in a Formula via VBA.

For example: ActiveCell.Formula = "=SUM(Sheet3!strVariable)"

Of course the code is not working that way. The Variable has the following structer: CellAdress;CellAdress;CellAdress... so it would perfectly fit into the formula-syntax.

Is there any possible way to do that? The reason behind it is, that I have to deal with a flexible range and I want to process this range with a formula. Actually the range is flexible, because of different filters and I only wanna count all numbers in the unhidden rows.

My varibale would then contain all celladresses, for the visible cells.

So in a nutshell: is it possible to somehow use a varible in a formula?

View 9 Replies View Related

Set A Workbook Variable As A String

Oct 20, 2009

I have a string variable contacting a file.

I want to set a workbook variable to this file, WITHOUT actually opening it!

DIM wb as Workbook
DIM filenm as String

filenm = "C: empdata.xls"
set workbook = filenm

So I know this won't work, but is there anyway around it? (bearing in mind I don't want to open the file!

View 9 Replies View Related

Using A String Variable In Formula

Apr 21, 2006

Just a quick question, is it possible to use a string variable in a Excel formula (in VBA)? Im trying to do the following in VBA

mortgageSchedName = acName & " Schedule"
Range("B6").FormulaR1C1 = "=mortgageSchedName!RC[254])"

the above results in an error because there is no such sheet as "mortgageSchedName", but I want it to access the value of that string variable.

View 6 Replies View Related

Copyrights 2005-15, All rights reserved