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
ADVERTISEMENT
Dec 20, 2007
I'm trying to FIND the filename in a path such as:
F:userdjleeCSCAV Remarks.png
My path is different for each record.
I tried =RIGHT(C1,FIND("",C1)), but it does not recognize the "".
View 9 Replies
View Related
May 9, 2006
I have a problem with paths and filenames with spaces.
If the the excel file containing the macro file is opened from explorer rather than from within excel, the path is incorrect. Therefore I use the following
Dim varPath As String
varPath = ThisWorkbook.Path
ChDir varPath
I can then later call either varPath or the VBA GetCurDir and it will be correctly set.
However, when I call a Shell function as in the example:
Dim varPath As String
Dim fPath As String
varPath = ThisWorkbook.Path
ChDir varPath
fPath = varPath & " emp"
MsgBox fPath & "test.bat"
Shell fPath & "test.bat"
I get the message box: e:local data tables emp est.bat
I get the error: Runtime Error 5, Invalid procedure call or argument.
Without having to rename the path structure so that it doesn't have spaces (which isn't always possible), how can I get this to work?
View 6 Replies
View Related
Mar 10, 2007
I created a series of combo boxes to accept values from the user. These values are intended to be used to build the name of a file they want to see. I tried to set whatever gets selected in each of the combo boxes equal to a distinct variable and inserted each variable into the file path at the appropriate point. I then created a variable for the file path and inserted the file path variable into code that opens that file, copies a particular worksheet, and pastes it into a new sheet in the “master” (the one with the form) workbook and labels it according to what it was named before.
The problem I’m having is that Excel says it doesn’t recognize the file path I’ve given it. Judging from the file path it quotes in the message, it’s not seeing my variables (only the part of the file path I entered in quotes) and then, later when I “debug” it says the variables are not defined. I’m wondering if there is more to defining a variable than what I’ve done. On the form page of code, I declared the following variables: The UserForm Code
Option Explicit
Public strPerformBySubtask As String
Public strPerformBySubelement As String
Public strPeriod As String
Public strMonth As String
I populated each combo box ok and remembered to call the sub procedures below, show the form, hide the form, et al. I think the problem might be in the sub procedures below that I thought would define the variables by setting the respective combo box output equal to a distinct variable I could use in the file path:...........................
View 2 Replies
View Related
Jan 31, 2008
I have path data (subfolder depth varies) that I need to trim to filename only.
Example:
/folder1/folder2/filename1.eps
/folder1/folder2/folder3/filename2.eps
/folder1/folder2/folder3/folder4/filename3.eps
Need to end up with:
filename1.eps
filename2.eps
filename3.eps
View 6 Replies
View Related
Jan 29, 2014
I have a list of files in column A:
/folder/subfolder1/subfolder2/subfolder3/file1.txt
/folder/subfolder1/subfolder2/file2.txt
/folder/subfolder1/file3.txt
/folder/file4.txt
in column B I would like to see just the filenames:
file1.txt
file2.txt
file3.txt
file4.txt
How can I achieve this? On the internet I found this forumule which looks promising but unfortunately finds the first slash (instead of the last) and prints what's to the right of that slash:
=RIGHT(C2; LEN(C2) - SEARCH("/";C2))
View 3 Replies
View Related
Apr 5, 2012
I have the following code and I it works except it gives the full file path rather than just the name of the file.
I have been racking my brain for ages to try and figure how to just get the filename to show but I have been unsuccessful.
Code:
Private Sub CheckBox1_Click()
Range("K43").Select
' Set up list of file filters
[Code].....
View 9 Replies
View Related
Apr 17, 2007
Is there a way to find the full name and path of the workbook the macro is running in?
View 4 Replies
View Related
Feb 16, 2010
I am looking for a solution to define a name by saving as. I think I'm almost there, but can't find the part where i can name it.
In steps:
1) Define the path and the name of the current file as string - to delete extension (=.csv)
2) Save current file as current name with .xls in folder where is current file saved before.
This is a part of my code - the part that is neccesary to see what I am doing: ...
View 8 Replies
View Related
Feb 23, 2013
why this won't work.
Code:
Dim DRange As String
Dim ERange As String
Dim SRange As String
EndRow = Range("A65536").End(xlUp).Row
DRange = Range("D1", "Z" & EndRow)
ERange = Range("E1", "Z" & EndRow)
SRange = DRange
View 6 Replies
View Related
May 19, 2008
how to define the end of a range using a variable determined by a rng.Rows.Count command (or anything else)? Is this even possible? Here is what I currently have:
Set rng = Range("A1:A10000")
For i = rng.Rows.Count To 1 Step -1
If rng.Cells(i).Value = "0" Then rng.Cells(i).EntireRow.Delete
Next
I'd like to define the end of the range with more accuracy than I'm currently doing.
View 9 Replies
View Related
Jun 8, 2006
i wrote the following statement that worked:
'Selection.FormulaArray = "=UserDefault!R[-27]C[-1]:R[-23]C[1]" '
but i need to define the index with a variable
'dim franz as integer
franz = 89
Selection.FormulaArray = "=UserDefault!R[franz]C[-1]:R[-23]C[1]" '
this does not work, is there a possibility to do this? the index from where te values should be read is calculated in the macro, so i can not use fixed values.
View 3 Replies
View Related
Dec 22, 2006
I am searching for data, upwards from cell A1400. Once I find data (cell > 0), I need to count up 27 rows and then select these rows for columns A to E. Then I want to copy this block of data and paste it. I have been having difficulty with the selection of a block of data which will be in a different position depending upon the data used. The code I am using is below. The two lines that I need to replace with a Variables cell range reference are:
Range("A1268:E1242").Select
Range("A1268:E1242").Activate
Sub CalculateATR()
Sheets("Data").Range("a1400").Select
Do
If ActiveCell.Value > 0 Then
ActiveCell.Select
Exit Do
Else
ActiveCell.Offset(-1, 0).Select
End If
Loop Until ActiveCell.Value > 0
Range("A1268:E1242").Select
Range("A1268:E1242").Activate
Selection.Copy
Range("G5:K31").Select
ActiveSheet.Paste
Else
End If
End Sub
View 8 Replies
View Related
Jul 21, 2009
I have a range staring with cell "A5" and going to Column "K8+" that I need to copy. My issue is that the number of rows to copy will change. I have the number stored a variable "a" in my coding. how do I code this to work?
View 5 Replies
View Related
Nov 13, 2006
I'm fairly new to VBA and I need to define a variable as a date range
i.e. Period1 = 1Apr2006 to 29Apr2006, Period2= 30Apr2006 to 27May2006 etc
View 9 Replies
View Related
Nov 25, 2009
1. I need to define a variable range of all the USED cells in a column (starting at row 2 as the row 1 is the header)
2. I need to do this for columns A through to P inclusively (all using the same lastrow reference as that of column A)
3. I need to call the range name by the contents of the header in that column (Row 1)
4. Some of the data in the header row (row 1) contains spaces - can we change this to an underscore for the range name only?
5. Does a range only work for the active worksheet? Because i will have multiple worksheets, all with the exact same information in row 1, but different numbers of rows (and different data as well), but i will need the range defined on each worksheet......
I've found the following code and thought it was it, but it only defines on 1 worksheet, and for some reason only defines ranges for columns 1 and 2. Also it defines the range as A1:A65535 - not the USED range.....
View 9 Replies
View Related
Dec 28, 2007
I am producing graphs that compare last years figures (static) with this years figures ( Dynamic) and this years figures come from a Pivot table and Worksheet that has been imported. The value returned in each cell for the graph's dataset is derived from a specific Pivot table row and the column name (month) that matches the column of the data set - so far so good. The formula currently looks like this.
=IF(ISNUMBER(GETPIVOTDATA("Duration of Spell",'Count PS'!$A$3,"Admission Hospital","Hillside","Discharge Date Month Name",Q3)),(GETPIVOTDATA("Duration of Spell",'Count PS'!$A$3,"Admission Hospital","Hillside","Discharge Date Month Name",Q3)),0)
where 'Count PS' is the name of the worksheet and Q3 is the reference cell that holds the Month Name. The problem is that I will not always be creating the report and other users may use a different name for the imported worksheet. I want to be able to set the name of the worksheet to be referenced in a supporting worksheet so that the user could simply amend the cell value to be the name of the source worksheet, but I can't work out the syntax
View 2 Replies
View Related
Mar 26, 2014
I'm working on a project where I need to populate a column with vlookups taken from different workbooks, I have for the most part got it working. However currently, everytime it populates a cell it prompts the user to select the sheet from the workbook as there are two. The sheet name is always the same so I tried to add it into the code so it would avoid having to repeatedly click okay while it populated the table.
This is the original code where it asks for the user to select the sheet every time.
[Code].....
This is what is produced when it is run and the user selects the worksheet each time (which works perfectly fine):
[Code] ...........
I tried to enter the Full Costs sheet name into the code like so:
[Code] ......
However this produces the following:
[Code] .....
The issue I have found is that the square brackets that are around the filename are generated automatically, they aren’t in the actual filename and I haven’t put them in, so I am struggling to work out how to add the sheet name in where it wont be included within the square brackets, as that is what's breaking the lookup.
View 3 Replies
View Related
Apr 17, 2007
I'm working on a spreadsheet that will access two weeks worth of data at a time. Is it possible to have the filename reference contain a variable or a link to another cell in it?
For example: ...
View 6 Replies
View Related
Mar 16, 2009
I have a filname called ex: "Statusreport xxyyzz.xls"
I'm only interested to put xxyyzz in a variable and reuse it for saving another workbook.
How can I use the different string functions for this. The word "Statusreport" is used for apporx 20 different workbooks.
View 9 Replies
View Related
Aug 3, 2006
Have a file lets say is named
sample.xls
Does anyone know how to retrieve the name of the file in VB?
just the name and put it in a string?
View 3 Replies
View Related
Aug 18, 2006
referencing a variable during a data import. I have searched the forums here and the web for a couple weeks and attempted enclosing the variable in many different symbols.
Nothing I have tried works. I have also read chip pearsons guide but I was still not able to get it to work.
I am trying to provide a way for the user to input the filename and location in an input box and then use that variable to import the data. It is only one file that is needed.
The message box filename is only included to allow me to verify the text input.
Dim Filename As String
Sheets.Add. Name = "All Data"
Filename = InputBox("Enter Filename: ", "Enter Filename Location")
MsgBox Filename
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;Filename", Destination:=Range("A1"))
.Name = "SHOAlarmsJune2-9"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
View 3 Replies
View Related
Jun 25, 2007
I have a VBA form that the user enters information into and once they click submit on the form the code opens a new workbook and then enters the information from the form into that workbook. What I need is for that code to then save the workbook with a filename from one of the fields entered into the form but I'm not too sure how to do this.
View 3 Replies
View Related
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
Feb 8, 2012
I am using a script within excel as part of this script I want create a new folder to store information
Code:
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
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
View Related
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
Mar 10, 2009
I am trying to get a module wide variable to provide the current filename to the various subs.
View 2 Replies
View Related
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
[Code]....
View 1 Replies
View Related
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
