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!
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.
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] .....
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.
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
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
I have a variable ("DestFile") that defines a path to a file (used in saving the file)...
I'm in the process of getting a Sub to hyperlink to this file, but in some circumstances, I may only want to hyperlink to the folder, not the actual file...
How would I go about trimming the "DestFile" address to get a "DestFldr" address?...
An example of "DestFile" might be;
S:BryanFor KenGulf ConstructionST0609014-t.xls
(the file name length may vary)
What code can I use to consistently trim it back to;
S:BryanFor KenGulf Construction
as the "DestFldr" variable?
I want to run a macro to separate File Name and Path from the given complete path
For Example
Code:
In Column A : Given Complete Path " C:MainFolderRecordsSubFilesFile1Record.pdf
I need it separeted like
Code:
In Column B :File Name = Record.pdf
In Column C :File Path = C:MainFolderRecordsSubFilesFile1
Is there any way to do this through a macro
I have some code (listed below) that will open 2 groups of files(for testing purposes, I have been using for only 2 file prefixes, but will need for upwards of 10, and more may be added in the future), depending on what files have been opened in the past (it will skip those) and then import the new ones.
I am now trying to clean up my code, and having alot of it abled to be maintained be editing a spreadsheet (administator controlled)
What I would like to do is something similar to:
defvar= cells(1,1) 'where cells(1,1) has all of the info for that file to import
Selection.TextToColumns defvar
I realize this will probably be a little more complicated than this, and may even be its own sub or funtion.
Here is my starting code, and it works fine: ...
I have several words in sheet2.one word per line. every line in sheet1 should be checked and deleted if the line (colum 3 and 4) contains any of the words in sheet2. i decided to go with two for-loops, my Problem is a error in line 7 and 9 (indicated by arrows). it seems to me that ...Cells(...).Value is not allowed for strings. i already tried .Text, checked several VBA Forums but could not find a solution.
View 13 Replies View RelatedI need a bit of help with the below macro which I am trying to create. I recorded the below vlookup, which works perfectly. It checks a list on sheet “Map” and returns a value depending on whether the reference is one of the 6 or not. These 6 are likely to change over time so I would prefer to declare them as variables rather than build them directly into the macro
View 4 Replies View RelatedIs it possible to store format of a sheet in a variable?
I have one sheet. i want to select all cell. then copy the formatting of this sheet(template) in a variable. is that possible? does variable need to be a clipboard? if it is in clipboard how to reference it? i.e if i have two or three formats stored in a clipboard how to select and paste the second one?
once i store this in a variable, i will paste in new sheets(about 50 of them) so that all these new sheets have same format as the template.
what is the easiest to do that?
I have a workbook that is composed of forty (or so) worksheets containing data and a single summary worksheet that has command buttons that take the user to the appropriate data worksheet for their specific project. Each data worksheet is exactly the same in terms of where the header row starts, and the specific headings.
On each data worksheet there is a command button that when clicked, builds a pivot table of the data for the current project. I have been able to create VBA code that hides the columns containing the data and then creates the pivot table in the empty (unhidden) columns n the same worksheet. This works fine, but is not a good solution from a useability standpoint.
What I have been trying to do is when the command button is clicked I want to capture the name of the current worksheet as a variable in VBA, go to a separate worksheet to build the pivot table, and when the user clicks a ‘Review Data’ command button on the pivot table worksheet they are taken back to their original worksheet containing their data. Is this possible?
way to store a cell's location to variables.
Something like:
int a, b
Cell(a, b) = ActiveCell
I'm currently working with a fairly large worksheet, and I'm using Cells. Find to look for a specific cell. Then I want to Filter that column, but I can't figure out what column Selection.AutoFilter Field:=? should be.
I would like to record the address of the last set of cells that data was input into to a variable so that a user can choose to delete the last entry. An 'Undo' button really.
What I have is a user form that writes different materials to thier respective sheets in the database. (Material1, Material2, etc.) Some materials have a different number of variables (some have a width and some don't, but all have a quantity.)
This is the code I have for adding the material to the database (each material has it's own button with material specific code.)
Private Sub AddToMaterial1_Click()
Set c = Worksheets("Material1").Range("a65536").End(xlUp).Offset(1, 0)
Application.ScreenUpdating = False
c.Value = Me.Material1Quantity.Value
c.Offset(0, 1).Value = Me.Material1Description.Value
c.Offset(0, 2).Value = Me.Material1Length.Value
Dim lastenty1
lastentry1 = c.Address
Dim lastentry2
lastentry2 = c.Offset(0, 1).Address
Dim lastentry3
lastentry3 = c.Offset(0, 2).Address
Dim lastentry4
lastentry4 = c.Offset(0, 3).Address
Dim lastentry5
lastentry5 = vbNullString
Dim lastentry6
lastentry6 = vbNullString
Application.ScreenUpdating = True
End Sub
The following code is what I am trying to do for a single button to clear the last entry to the database.
Private Sub RemoveLastEntry_Click()
Range(lastentry1).ClearContents
Range(lastentry2).ClearContents
Range(lastentry3).ClearContents '(There is always at least 3 cells to clear)
If lastentry4 = nullstring Then Exit Sub
Range(lastentry4).ClearContents
If lastentry5 = nullstring Then Exit Sub
Range(lastentry5).ClearContents
If lastentry6 = nullstring Then Exit Sub
Range(lastentry6).ClearContents
End Sub
I am having trouble using the find function. I need to store a date as a variable and then find this date on another worksheet. The date is in the following format:
dd-mmm-yy
This is what I currently have which gives me a run time error 91:
Code:
Dim DateSearch As Date
DateSearch = Range("C3").Value
Cells.Find(What:=DateSearch, After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
I have a table in which I have a "patient" column and a "page" column. The macro searches for a patient's number, then looks if this patient has the page "900.--". A patient may have more than one page, which will result in many rows with the same patient number. So far, my macro uses the search function to find a patient number, then compares the cell next to the active cell to see if it contains the page "900.--". If not, then my macro searches for the next patient and so on until the page is found and noted into another workbook or none is found.
In order to stop the loop, I am trying to store the address of the first cell found into a variable "rFirstCell" so that it can later on be compared to another variable, "rSecondCell", which represent the active cell. When both are the same, it means all the available search results have been tested and the loop should stop.
Sub testing()
Dim rRng As Range, rFirstCell As Range, rSecondCell As Range
Set rRng = Worksheets("Overview").[a1]
Dim sDeath As String
sDeath = "death"
ActiveSheet.AutoFilterMode = False
If LCase(rRng(2, 15).Value) = "x" Then
If LCase(rRng(2, 9).Value) = sDeath Then
Workbooks("DM Endpoint pages_test.xls").Activate
Range("A1").Select...........................
I have a list of names from cell A1:A10 in sheet "Input." Each of these names has its own corresponding sheet in the workbook. I want to be able to run the same exact VBA code for each sheet. In other words, I am trying to get my name variable to automatically change to the next value on sheet "Input." I'm sure this is pretty simple to do, but I can't seem to find anything that works!
View 5 Replies View RelatedHow to store a value in variable after concatenation of two values and putting it into the same cell.
Let assume, in cell A1, we have value 1 (numeric). And in code i have a variable with stored value as "%".
Now i want to concatenate 1 and % and put it back into cell A1 as 1%.
I have a written a code, but seems to be wrong one.
Sub Percentage()
Per = "%"
lr = Sheets("Process Overview").Cells(Rows.Count, 3).End(xlUp).Row
For i = 10 To Sheets("Process Overview").Cells(Rows.Count, 3).End(xlUp).Row
If Cells(i, 4).Value = "p" Then
[Code] ........
I have a spreadsheet with near 300 tabs, each with a picture in the tab. The main tab has a list of all other tabs, the goal is to allow the user to click on a cell next to an entry, and have Excel flash the referenced tab to allow the user to see what the entry is referencing. I have written a simple macro that activates a desired tab, unhides it, displays a message box to pause the macro, rehides the tab, then returns the user to the main tab.
Rather than creating a macro for all 300 tabs and creating buttons I would love to use the Private Sub Worksheet_SelectionChange(ByBal Target As Range) or some variation thereof, to make my life much easier. The name of the tab is in cell A2, so I would want to have the user click on cell A1, activate the macro, then take A1 to A2 with something like A1 = A(x+1)->A2, then display the tab listed in A2. So rather than have 300 macros with Sheets("XYZ").Visible = True, I would love it to read Sheets(contents of referenced cell).Visible = True. with the contents of referenced cell coming from some manipulation of the cell I clicked on...
I need to store a known index, unknown value in a variable so I can increase the value and use it in a database. It is used as a version number for a part and the versions go like: "00" -> "AA" -> "AB" -> .... -> "AZ" -> "BA"... etc.
VB:
If tool.Worksheets("TRB Database").Cells(A, "R").Value <>
ThisWorkbook.Worksheets("Design Calculator, Q").Cells(7, "C").Value Or
tool.Worksheets("TRB Database").Cells(A, "AA").Value <>
ThisWorkbook.Worksheets("Design Calculator, Q").Cells(5, "K").Value Or tool.Worksheets("TRB Database")
[Code] ....
That is a part of the increase and when I try to increase "AA" by 1 it goes to "B" and not "AB".
VB: tool.Worksheets("TRB Database").Cells(row1, "D").Value = Chr(Asc(tool.Worksheets("TRB Database").Cells(A, "D").Value) + 1)
This is where I increase the value.
I want to open a specific sheet and refresh only said sheet when i open another sheet for example x.xls
So opening x.xls will automatically open y.xls
I've tried this in the workbook code area but it doesn't do anything.
[Code] .....
1. Store the value in Cell A1 to a text file located in C: with name TEXTFILE.TXT (Replace the existing value)
2. Store the value to the text file as additional line item (append records)
I am trying to import some csv files so I can combine them, but am having probs with the filename and location.
Sub test()
Dim wsName As String
wsName = ActiveCell
Sheets("Data").Select
With ActiveSheet.UsedRange
LastRow = .SpecialCells(11).Row
End With
With ActiveSheet.QueryTables.Add(Connection:="TEXT; &thisWorkbook.Path &" " & wsName &", Destination:= Range("A" & LastRow))
.Name = wsName
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells.....................
How to save a file, with file- name. but the directory is to be read in worksheet "towns" in Cell1 (brussels) and filename in worksheet "names" in cell B2 (i.e. winter), so it saves to c:russelswinter.txt as a wordpad or kladblok txt file, that keeps a number, so each time we push a button "go back from worksheet names to worksheet towns" the "number" that is saved in the txt document goes up by value +1. In Flemisch, the "old" code goes as follows, and saves the number in the txt file Factuurnummer7.txt. But I want that the file name (here: FactuurNummer7) can be a variable text issue, which has to be read - as already noticed - in cell B2 (with the word WINTER). So the are 2 worksheets: towns, ans names
pad$ = Application.DefaultFilePath
'controle = Dir(pad$ + "FactuurNummer7.txt")
'If controle = "" Then GoTo EerstAanmaken
'Open pad$ + "Factuurnummer7.txt" For Input As #10
'Input #10, Nummer1
'Close #10......................
Below is the current code I have for File Copy before the workbook closes. This file will be distributed all over and obviously will not have the same old path and new path locations as I have in my code also will not have the same file name. Is there anyway to still perform the file copy without knowing the old path and file name and possibly have message box pop up to ask the copy to location and use that in the new path string?
Sub Macro1()
Dim fs As Object
Dim oldPath As String, newPath As String
oldPath = "I:EXLDATAMC Daily" '<---Where the file is currently located
newPath = "H:South RegionOrlando Mail Services2008DI" 'Since the super shared drive is super slow we will just copy and replace this file each time before we close and of course after we save
Set fs = CreateObject("Scripting.FileSystemObject")
fs.CopyFile oldPath & "" & "OrlandoMail.xls", newPath & "" & "OrlandoMail.xls"
Set fs = Nothing
End Sub
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.
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
Have a file of 7 mb having 100 columns and unlimited rows (user defined- approx 500). At all these cells in that area, I refresh (copy/paste) the same formula for all cells, read as:
[=IF($DF47=0,"",IF(ISERROR(MATCH(HE$45,$Q$22:$Q$26,0)),INDEX($DF$12:$HE$31,
MATCH($DF47,$DF$12:$DF$31,0),1+HE$11-$DF$11),INDEX($Q$22:$S$26,
MATCH(HE$45,$Q$22:$Q$26,0),IF($DA47=GROUPSHIP,2,3)))) ].
Refresh is necessary bcs I insert/delete/sort rows.
In order to reduce file size and instead of saving all these formulas into the approx 5000 cells, I would prefer if I could have a function in VBA and call it (with copy/paste) with a smaller formula at all these 5000 cells as: [= myfunction], so as to have this simple formula in each of 5000 cells, hoping that this will reduce size.
How can I write the long formula above to a function in VBA?
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.