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.
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?
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:...........................
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:
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: ...
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
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.
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.
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:
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
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?
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.....
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
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.
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?
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")
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.
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
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
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...