is there a VBA way to determine an unknown path in which a KNOWN WB is located !?
Assume a WB named: 1X1.xls Located at C:TEMP
[In case the user uses more than one partition and/or more than one HD - it might alse be located at: D:TEMP
The known open command for a known file AND Path is:
How can I change this;
Code:
Sub GetData_Example4()
Dim SaveDriveDir As String, MyPath As String
Dim FName As Variant
SaveDriveDir = CurDir
MyPath = Application.DefaultFilePath 'or use "C:Data"
[Code] ....
So that it still prompts the user for the path but limits their selection to only Excel files and only those named 'Roles'?
I am trying to use the same VBA code for different applications using variables.
Basically I want to have a variables sheet that has the different directories based on the macro selection. My variable sourceloc would change based on the variable name on the sheet.
sometimes I would want it to equal what was in a1 and sometime I would want what was in a2
a1 cell contains "f:filezilla files
a2 cell contains "f:dmc files
This is the code I started and it gives compile error when ChDir = sourceloc is reached
Public sourceloc
Sub filezillasettings()
Dim sourceloc As String
sourceloc = ActiveSheet.[a1] ' a1 cell contains the directory name and folder name =
End Sub
I have my VBA Codes set in a workbook (Production) that my supervisors open to calculate production. So when they open the workbook (Production) I have a button that they push that starts the code however I need to put something in that activates the other open workbook, The problem is that the name of the workbook that they run the code on can change.
View 2 Replies View RelatedI'm trying to build a macro to open multiple files at one time that will always be saved in a consistent drive. The problem is that sometimes there will only be one file for a month (ie only at month-end) and other times, there may also be additional files for different dates throughout the month. I wont know ahead of time how many files there will be, but they will always be saved in the same file name type that is "FILE DESCRIPTION MM-DD-YYYY". How can I build a loop that looks for a file on each possible day but doesn't error out if the file doesn't exist?
View 1 Replies View RelatedI've been working on a spreadsheet and these forums have been a great help. I'm now at the very last section and, surprise surprise, it's also the hardest!
I'm creating a stock trade recording sheet. I have a userform ask the user to enter a date, a time, the number of stock purchased, and the price of the stock. These are then entered in a new row.
Now what I want to do is have summary cells which say how many stocks were purchased and the total profit made for each day. Since each time is given its own row, I can't know in advance which rows to sum over. I also don't know on which days a trade was made. So a summary cell should only exist if a trade was made that day.
Could I do something like.... check if the date matches then sum over all the values for that date? So if column A has the dates, can I say "Search which rows in column A have this date" then "for those rows, sum column C"?
Also, how would I create a a row for each traded date's summary cell and enter the date in it? I've attached a spreadsheets which manually demonstrates what I want to do (no macros) and a spreadsheet with what I have so far (basic macros).
Any and all help much appreciated, I just need to get my head around creating and dealing with variable ranges. Is that a really advanced task? I don't think this is a one line solution so please bear with me while I make mistakes!
how can i open a different path of the workbook in the listbox. if my listbox there only show the file name.
View 2 Replies View Relatedhi, i need a macro that will open the below file path, and when i select the file i would like the macro to copy the active sheet from the source file being opened from range A2:L10000 and then paste special values to destination CELL A16 of the activesheet of the workbook.
I would like the date format for the below to be generic so it will apply for all months and years.
S:RECSBANKRECNominees200812-2008
Is it possible to set the starting path for the open dialog - using expression. GetOpenFilename so that the contents of the correct folder are visible immediately?
View 3 Replies View RelatedI have created a button on an excel form in the 2003 version and i want to create a macro that opens up a network path in windows explorer such as 'open W:
ew folder
ew share'
I have an application that requires users to download a file from a location that is then imported at their execution into the application. I've done my best to counsel users to download the file to their desktop (for ease of locating) and to stick with a standard filename so that there are no errors. Some of these people just don't take direction well, will save the file wherever, or save the name inappropratly, or worse, open up multiple copies of the same file corrupting the name: ie: FileName.xls, FileName.xls(1), FileName1.
View 5 Replies View RelatedFile open dialog box, Get the path to textbox? ...
View 7 Replies View RelatedI 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
I'm trying to use vba open a new workbook and then edit it. But i can not select cell in the new workbook. but it can be activated!
it can open the file (fname) and activate the sheets (sName), but will flage an error on range select.
Workbooks.Open fileName:=fPath & "" & fName
Workbooks(fName).Sheets(sName).Activate
Range("A8").Select
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
I would like to have a macro button that will open the file location (path were the excel is stored) in MS explorer.
I found something like:
Sub ExplorePath()
Shell Environ("windir") & "Explorer.exe " & ActiveDocument.Path, vbMaximizedFocus
End Sub
But that give the error that an object is needed. Would that also work for MS Word?
Is there a way to use the open dialog box (like as if you where opening another workbook) and then with the selected item inster the path and file name into a cell.
View 2 Replies View RelatedI 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.
I have my code here:
VB:
Sub openfiles()Dim Path As String
Dim ExcelFile As String
' Path = GetFolder("C:UsersKinteshDesktop")
Path = "C:UsersKinteshDesktopVBA programmingMaps"
ExcelFile = Dir(Path & "*.xls")
[Code] ....
NextCode:
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.
I have an Excel sheet that contains a few thousand folder paths in the first column. The first few cells in the column look similar to below.
C:UsersNameDesktopFolder Copy12010360
C:UsersNameDesktopFolder Copy12010361
C:UsersNameDesktopFolder Copy12010362
C:UsersNameDesktopFolder Copy12010363
C:UsersNameDesktopFolder Copy12010364
C:UsersNameDesktopFolder Copy12010365
I am trying to create a macro that will copy any folders that exist in any of the paths listed in the first column. The folders should be copied to the path of the open workbook containing the macro. Below is the macro I have currently, much of which was taken from information I found in this thread [URL]....
VB:
Sub wrapper3()
x = 1
Set fs = CreateObject("Scripting.FileSystemObject")
While Sheets("Air").Cells(x, 1) <> ""
v = InStrRev(Sheets("Air").Cells(x, 1), "")
dest = ActiveWorkbook.Path & Mid(Sheets("Air").Cells(x, 1), v, 99)
[Code]...
This code seems to work fine if all of the folders exist to be copied. My problem is that some do not and it is creating a "Path Not Found" error for which I need a fix. If the folder doesn't exist at the path nothing should be copied and the next path can be evaluated.
I am successfully opening a .csv file using a variable value stored in a named range in my Main file (the variable includes the directory and path).
I copy data from the .csv file to the Main file then I need to close the .csv file without saving but I want to do that by using the
Windows("xxxx").Activate
command where "xxxx" is the namedrange in my Main file which stores the .csv filename (without the directory and path prefix).
I can use the
ActiveWindow.ActivatePrevious
command but if I have another workbook open, this one closes instead of the .csv file I opened from the macro.
I realise this is probably very basic and I've searched the forums but can't find any identical postings.
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] .....
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 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.
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 want to have C9 populated with a budget type from H1, I1 or J1. How do I create the formula that checks which budget type the value in F9 is?
I am using excel very infrequently.
Screen Shot 2014-05-18 at 12.52.26.jpg
I am new to VBA programming in Excel (currently 2003) so hopefully this isn't a "dumb" question.
I would like to re-arrange a group of data from a table of several rows and columns to a single column where the data from the first row is translated to a column then the second row is appended to the end of that data ....
ie:
( 1 ) ( 4 ) ( 7 )
( 2 ) ( 5 ) ( 8 )
( 3 ) ( 6 ) ( 9 )
to
( 1 )
( 4 )
( 7 )
( 2 )
( 5 )
( 8 )
( 3 )....
I thought a user defined formula would be a good place to start but am having problems getting the address of the cell that the function resides in to use to calculate the offset from a fixed cell (to be entered into the function as a argument). In other words, how do I get the address of the call that a UDF is entered in into the UDF? activecell.address is returning the cell that was last clicked not the cell were the UDF resides.
Secondly is there a better way to achive the results that I am looking for other than through a function? The size of the table will vary from worksheet to worksheet. I would also like to add an argumnet that lets me exclude cells with certain content from the resultant list.
I have created a drop down menu wseveral items for the first time. The drop down is located on several worksheets. What I'm trying to do is find out if there is a way to do 2 things.
A) capture a total (numerical value) of the number of items selected from the list on each worksheet. ex. if on worksheet #3 15 cells contain data from the drop down cell A1 on worksheet #1 would = 15.
&
B) capture a total (numerical value) of the number of times a specific item was selected from several worksheets. ex. if ABC was selected from the drop down menu 8 times on worksheet #2 & 6 times on worksheet #3, cell A2 on worksheet #1 would = 14.
How to pull data with a VLOOKUP to a file located on a Server.
My big problem is not understanding the syntax of the Server location. I've provided it below (obviously with some character changes).
I have a file located on my Hard Drive. I need to perform a VLOOKUP from A2 on this spreadsheet. The File on the Server is called "LookupTest.xls". The Range of Data is from Sheet 1 and is from A2:C4 and I need to pull the data from column C depending on what is selected from A2 from the file on my HD. I understand how to use VLOOKUP, just not when I'm pointing to another file on a Server.
Server mapping from Windows Explorer:
rbbabc$ on 'RP17409 - ABC Database (AHSSRVVN678.tge.com)'
Again, that server mapping is fake, as I changed it, but this is how it looks on Windows Explorer.
I'm trying to keep a master file of Data on a spreadsheet found on a server so that one master file can be updated, and several other tools pull from that Master File on VLOOKUP, Validation Lists, etc.
I have 2 tables where I need to pull data from one table into the other.
Table 1 - pull data here
A
Site
B
Begin Date
C
End Date
Rate
[Code]...
For example: site 77 in table 1, I need to pull whatever rate is shown for site 77 in table 2 for the date range specified on table 1. So the correct answer for the first row site 77 in column E would be .02. Notice there is a problem...mainly the dates do not match exactly. I just need the rate the applies for the majority of the time.
Another example. For table 1, site 174 from 2/11/11 to 2/22/12, the correct answer in column E (pulled from table 2) would be .02, even though Column D shows .00.