Use Of Variable In Path Code

Feb 4, 2007

I have been using this code to auto print a series of workbooks whose file date contains the day i want. Each if statement looks to a different folder location to find the spreadsheet with the desired date and auto prints that sheet before moving on to the next if and spreadsheet, and then finally closing. I use an input box to get the date/day variable.

The spreadsheets i auto print are in folders labeled for different months of the year, i.e. 0107, 0207 etc. I want to be able to enter the MONTH i want the spath to look for, in other words, i want to be able to input first the month, which tells the rest of the routine what folder to look for the next input, which is the date of the spreadsheet. I am flummoxed by the variable itself.

The code is below.

Public Sub Auto_Open()
Dim sCurFile As String
Dim sPath As String

fpath = InputBox("shift and day (BXX) of the month to print?", "print")

'Get the path
sPath = "k:2007207"
If sPath <> "" Then
On Error Resume Next
Application. ScreenUpdating = False
If Right(sPath, 1) <> "" Then
sPath = sPath & ""
End If

View 4 Replies


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

How To Incorporate Variable Into Path

Feb 8, 2012

I am using a script within excel as part of this script I want create a new folder to store information


ChDir "C:Documents and SettingsAdminDesktopMIKEPAYROLL2011-12"

However I want to use a variable called "NEWYEAR" which holds the 2011-12 part

For example this part stays the same

ChDir "C:Documents and SettingsAdminDesktopMIKEPAYROLL

folder 2011-12 held in variable NEWYEAR may or maynot exist.

if the folder exists I want to be able to use the folder, however if it doesn't I need to create it.

The problem is I am not sure how to incorporate the variable into the path.

View 5 Replies View Related

Get File Path And Store A Variable

Nov 21, 2008

If I have a file open called test file, How I can find the file path of the file that is open and how can I store that's as a variable?

What I am doing is trying to open a file that excel closes in a save as process!

View 9 Replies View Related

Define Path & Filename As Variable

Feb 16, 2007

Private Sub cmdImport_Click()
Dim exportFile As String
exportFile = "c:jpmimportTrinity_ImpImport_" & Format( Date, "dd-mm-yy") + "_" + Format(Time, "hh:mm:ss") & ".csv"
Open exportFile For Output As #1

I put a watch on exportFile and it's correctly set to: "c:jpmimportTrinity_ImpImport_15-02-07_17:55:01.csv"

However if I try to run this I get "bad file name or numer" when I open for output - the path exists and I can write to it, so must have a fundamentally wrong approach.

View 2 Replies View Related

Excel 2013 :: Set Photo Path As Variable?

Mar 31, 2014

I have a macro that inserts a photo via a photo path that has been set as an object variable. This macro has always worked for me in Excel 2003 but after updating to Excel 2013, the macro gives me a "runtime error 1004 - unable to get the insert property of the picture class". When I debug, Excel crashes.

The macro is designed to loop through up to nine fields (in 9 adjacent columns) and find a photo path for a picture, insert the picture, and print that page, then go to the next column, get the photo path and insert that picture and repeat. The initial part of the macro is as follows. The PhotoID as Object seems to be what no longer works...

I do not care if the inserted photos are maintained in the file when it is not connected to the3 drive hosting the photos, only that I can print them when the drive is available.

Sub PrintPDFImpSums()[INDENT][INDENT]Dim PageNo
Dim RangePrint As Range
Dim Photo As Picture


View 1 Replies View Related

Open Workbook Macro With Variable Path

Sep 15, 2006

I am having trouble getting a macro to open a specific file from an open workbook in the same folder based on the current path of the open file. The application is this... I have two files in the same folder. The first is for data entry and it contains a macro button that will open the second which is a template to which the data in the first will be copied. I can easily do this with a fixed path to the second file, however, I would like to be able to move or copy the folder containing these files to other locations or computers without having to change the path each time. I would like to base the path to the second file minus the file name on the first (already open file) path. Assuming that the first file containing the desired macro button is already open, here is one of many of my attempts...

Workbooks.Open " ActiveWorkbook.PathJob Tracker -.xls"

Assuming the second file is the active workbook, I achieved a similar path assignment when saving the file...

ActiveWorkbook.SaveAs "Application.Path/Jobs Active/" & "Job Tracker -" & Range(" '[Job Tracker -.xls]Specifications'!$A$6").Value

View 2 Replies View Related

Pass Chosen File & Path To Variable

Dec 1, 2006

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

View 3 Replies View Related

Open File With Variable Path & Part Name

Feb 27, 2008

1 I need to open a csv file in order to use data in it at location "C:BarkingEMCIN" to input into an xls spreadsheet. I then need to use the info from the xls spreadsheet along with other info input by users to produce another .csv and it be deposited at "C:BarkingEMCOUT"

The name of the file at "C:BarkingEMCIN" will change everyday or even multiple times a day and be in the format "AVA_DA_260208_BPL_EDF_001.CSV" "AVA DA " will remain the same "260208" will be todays date the next 2 will be interchangeable depending on requirements and use BPL EDF and SSE will be interchangeable depending on requirements and 001 will be the version whcih will be changable. _ underscores will be used as seperators

They will then be save in the format "NOM_DA_260208_BPL_EDF_001.CSV" using the same prinipals as before but automatically saving as the next available when needed with a prompt to tell the user what it will be

View 5 Replies View Related

Prompt To Select File/path And Store As Variable

Jan 29, 2009

I am looking for macro that when run, will open a file explorer window and prompt the user to select a folder and file where they have data stored. Then I need it to be stored as a variable and used as a part of a "Workbook.Open Filename" command.

The reason for this is that, I have a huge formatting marco stored within a workbook. When a user extracts a report from SAP, I want the workbook to grab the file that is extracted, open it and import all of the data in order to be formatted.

View 6 Replies View Related

Open Textfile From Path Defined By Environment Variable

Feb 14, 2010

I found this code and maybe I'm almost there. I need to get the value of the Environment variable called HOME if it exists.

These macros collect all the environemnt variabes into an array and then show them in a messabe box.

View 3 Replies View Related

VBA Generic Code For The File Path

Jan 1, 2009

how can i write the year and month in generic code for the following file path

.SaveAs Filename:= _

"S:RecsReportingRec StatsSummary Stats2009JanStat Summary_" & Format(Date, "dd-mm-yy") & ".xls"

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

Find Method Code: Object Variable Or With Block Variable Not Set

Sep 8, 2006

I need my program to:
- find the cell containing the string "Datum/Tid"
- record the column and the row of the found cell in two variables lCol and lRow

Here is my

Sub test()

Dim rFoundCell As Range
Dim lRow As Long
Dim lCol As Long

'Find method of VBA
Set rFoundCell = Range("A1")
Set rFoundCell = Worksheets("Sheet1").Range("A1:Z50").Find(What:="Datum/Tid", After:=rFoundCell, _
LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)

'for anyof the two lines down I get the message "object variable OR block variable not set"

lRow = rFoundCell.Row
lCol = rFoundCell.Column

End Sub

View 5 Replies View Related

Save File Using Cells For Path & Name Macro Code

Mar 19, 2008

I want to create a macro that saves my file this way:

in a folder named after B2
as a file named after B4n.

View 4 Replies View Related

Change File Path Name In VBA Code Based On Files Location

Mar 11, 2014

I have the following code written but I'm wondering if it's possible to modify this to change the red line to update to the path that the workbook is saved in? Meaning that User1Folder1 would change but [Workbook1.xlsm]Sheet1'E1 would always be the same.

[Code] .......

View 2 Replies View Related

Open All Excel Files In Directory - Code Doesn't Work For One Specific Path

Feb 8, 2014

I have my code here:

Sub openfiles()Dim Path As String
Dim ExcelFile As String
' Path = GetFolder("C:UsersKinteshDesktop")
Path = "C:UsersKinteshDesktopVBA programmingMaps"
ExcelFile = Dir(Path & "*.xls")

[Code] ....

GetFolder = sitem
Set fldr = Nothing
End Function

My problem is that the code all actually works (including the function and when I use the commented part), but pointing to this one specific directory (the one I'm using right now), literally nothing happens.

View 6 Replies View Related

Excel 2010 :: Using Current Month / Date And Time In A Folder Path In VBA Code

Feb 4, 2014

I’m working on a project using Microsoft Excel 2010 and I want to add some features to facilitate saving and retrieving files process.

1.How can I save the daily created workbooks (Assume 15 files a day) in order to contain the current date (and time if possible) linked with certain cell(s) I have at my workbook forming the file name? (XYZ 2-4-2014) and/or (ABC 2-4-2014 23:11) and so on …

2.I’ve been through some other posts and I found VB code which saves the active file into specific path, but it is only useful for single workbook because multiple files are getting overwritten automatically. Is there a code which allows multiple/different files saving & creates daily folders?

View 4 Replies View Related

Save Hyperlink Path For File With Absolute Path And Non-Relative?

Mar 4, 2013

is it possible to configure Excel in order to save the Hyperlink path for a file with absolute path and non relative?

I notice that the hyperlink is ....pdf ry.pdf

if I change the position of the file excel there is a problem!

I would like to save es. d:invoichepdf ry.pdf

View 1 Replies View Related

Plan Path And Actual Path Distance Calculation?

Feb 11, 2014

I am using an XY scatter chart that displays the planned path and an actual path as I go. The planned path is set up in 100' increments and goes 12000' and actual path varies along the way.

My data that I use is the Northing (ft), Easting (ft) and Vertical Section (ft) for both the planned path and actual path.

I was wondering if there was an east way to get that information from the chart or if there is a formula that would allow me to calculate distance between the two points by using the data I use for my chart.

View 1 Replies View Related

Macro To Separate File Name And Path From Complete Path

Oct 3, 2012

I want to run a macro to separate File Name and Path from the given complete path

For Example


In Column A : Given Complete Path " C:MainFolderRecordsSubFilesFile1Record.pdf

I need it separeted like


In Column B :File Name = Record.pdf
In Column C :File Path = C:MainFolderRecordsSubFilesFile1

Is there any way to do this through a macro

View 2 Replies View Related

VBA Code That Consider Variable Change

May 25, 2011

Trying to create a VBA code that will enter a formula such as: SUM(Q2:Qt) in a range of cells for instance A2:A10, and if there is information in B11, B12, B13 etc., it will consider that variable change and enter the SUM formula in cells A11, A12, A13.

I read that Dim LR As Long is the solution but not sure how to apply.

View 9 Replies View Related

Put Variable Code In Cell Using VBA

Jul 9, 2012



The row on the top currently matches the column on the left side. This was done by using simple commands like "=A4", "=A5", "=A6" in the top row. Now I am trying to expand this table to a variable amount of columns and rows (there will always be 3 more rows than columns like there currently is above), so I want to write some code in VBA that will put these equations in the cells for me.

Here is the tricky part though. The numbers in the left column are actually calculated using the solver, and the numbers in the middle cells reference both the left column and top row. Because of this, I actually need to put an equation in the top row (like the previously mentioned "=A4") so that it always has the newest result from the solver.

The left column will always be in the same row, so only the number itself needs to change, but I'm not sure how to do this.

View 3 Replies View Related

Determine Variable Value & Run Code Accordingly

Feb 20, 2008

I'm looking for a way to identify the caller procedure and to act according to that.
I tried the CALLER command but it seems not to be the appropriate one. The sample code is as follows:

Sub AAA()
MsgBox "This MsgBox is displayed from within Proc. AAA"
MsgBox "This should NOT to be shown if Proc CCC was called from here"
End Sub ............

View 8 Replies View Related

Passing A Variable Into A Different Piece Of Code

Mar 6, 2009

I have a spreadsheet that has several buttons on that run code using the cells around the code for parts of the data. eg

ColumnA1 ColumnB1 ColumnC1 Button1
ColumnA2 ColumnB2 ColumnC2 Button2

I want the buttons to run a seperate piece of code to start, but then all buttons run a similar piece of code at the end. I am hoping to be able to pass a variable from the first piece of code each button runs into the section of code that all share (which I have done as a seperate macro)

View 9 Replies View Related

Pass A Variable To The Form Code

Jun 8, 2009

I have a spreadsheet with about 30 charts on it that I would like to attach a macro to which opens the chart in a form. I've figured out how to do this last part, but am stuck on how to pass a variable to the form code which tells excel which chart to copy. Essentially what I'm doing creating a macro for each chart which would run when that chart is clicked on. This macro is identical for each chart except for the name of the chart being passed.

Sub Chart1_click()
ShowChart "Chart 1"
End Sub

Sub ShowChart(c As String)
End Sub

In the UserForm_Initialize code located in the userform module, I'd like to call the code which saves and loads the image of the clicked on chart. But here's where I'm stuck. How do I pass c to the code in the form module...e.g, how to pass c to UserForm_Intialize and to ChartZoom? Here's what I have so far...

Private Sub UserForm_Initialize(c As String)
ChartZoom c
End Sub

Private Sub ChartZoom(c As String)


Dim Cht As Chart
Set Cht = ActiveSheet.ChartObjects(c).chart
Dim CName As String
CName = ThisWorkbook.Path & "cht.gif"
Cht.Export Filename:=CName, FilterName:="GIF"
imgCht.PictureSizeMode = fmPictureSizeModeZoom
imgCht.Picture = LoadPicture(CName)

End Sub

View 9 Replies View Related

Variable Code In R1c1 Formula

May 19, 2007

I am trying to figure out how to use a variable as a row number for use in a range name and/or a pivot table range. Right now I have a range of R571C17, but the row number will change with each use of the pivot table formation macro.

View 3 Replies View Related

AutoFill Code With Variable As Row Number

Aug 7, 2007

Just a niggling problem, I've got lstRow as a Long and it contains the value of the last row offset by (1, 0). The problem is i'm trying to add it into a range

Selection.autofill Destination:=Range(C" & lstRow &":K" & lstRow &")Type:=xlFillDefault

Like that.. Except i've tried a million different combinations of " and & in different places to try and get it to compile. It refuses too. It always gives the error "Expected list seperator or )" I know I could just do it individually from C to K but I'd like to learn how to do it this way as well.

View 6 Replies View Related

VBA Code For NPV Function With Variable Interest Rate?

Feb 2, 2014

I would like a function which works like the NPV function except that the interest rate should be variable and a range rather than a single number.

Is it possible to see the code for the excel NPV function so that I can modify it?

Or alternatively has seen such a function?

View 8 Replies View Related

Storing VB Code In A Variable Withing A Macro

Dec 31, 2008

I have piece of code that I'm trying to make dynamic. I want to store parts of an IF statement in a variable and use them when I need them. Here's a little sample of what I'm trying to do

View 9 Replies View Related

Copyrights 2005-15, All rights reserved