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
ADVERTISEMENT
Aug 3, 2014
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.
View 2 Replies
View Related
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
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
Oct 31, 2008
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 Related
May 19, 2009
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.
View 5 Replies
View Related
Dec 10, 2008
hi, 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
View 9 Replies
View Related
Jul 16, 2007
Back to coding in VBA after long months of Matlab, and banging my head around this simple code snippet:
Sub openfile(FilePath)
Dim myWBk As New Workbook
'Now open Form
myWbk=Workbooks.Open(FilePath)
End Sub
This returns a nasty error "Activex Component couldn't create object". It opens the file given by FilePath though. What I would like is to assign the open workbook to the myWbk variable, such that it is easier to work with.
View 2 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
Aug 21, 2008
I have code to create a new workbook, and when I try to rename it I get "Compile error: Can't assign to read-only property."
Dim objXlApp As Object
Dim wkb As Workbook
Dim wks As Worksheet
Set objXlApp = CreateObject("Excel.Application")
' Create a workbook
Set wkb = objXlApp.Workbooks.Add
' Delete all worksheets bar the first one.
For Each wks In wkb.Worksheets
If Not wks.Index = 1 Then
wks.Delete
End If
Next wks
'Create some worksheets and names
With wkb
.Worksheets(1).Name = "myWorksheet1"
.Worksheets.Add.Name = "myWorksheet2"
.Worksheets.Add.Name = "myWorksheet3"
.Worksheets.Add.Name = "myWorksheet4"
End With...................................
View 5 Replies
View Related
Jan 25, 2010
I need a macro to do the following
1.Get list of .xls files from specified folder.
2.Append the files in new workbook in same folder.
Ex:
D:/users/excel/Sample_sheet1.xls
D:/users/excel/Sample_sheet2.xls
D:/users/excel/Sample_sheet3.xls
I want to get the list of Sample_*.xls and
create Sample.xls master file which adds the above three .xls as sheets in it.
Actually my macro as below
View 10 Replies
View Related
Mar 21, 2013
I have got a master workbook and I have written macro to copy and paste data on another workbook. write a macro to save the new workbook to a file path with a file name where both file name and path are stored in master workbook sheet...
View 5 Replies
View Related
Sep 27, 2006
I did a macro on my mac to transfer a sheet from one workbook to another worbook. It works very well when the destination workbook is open. Therefore I wanted to add some piece of code to check if the destination workbook is open. If not then I wanted the macro to open it before tranfering the sheet. Here is the code I´m using for tranfering the sheet
Sub Transfer_Sluttet()
If ActiveSheet.Index <> Sheets.Count Then
Application.DisplayAlerts = False
Set ws = ActiveSheet
Sheets(ws.Index + 1).Delete
ws.Move Before:=Workbooks("Sluttet.xls").Sheets("sheet2")
'Moves active sheet to beginning of named workbook.
'Replace Test.xls with the full name of the target workbook you want.
Application.DisplayAlerts = True
End If
End Sub
This is the type of macro I useually use on my pc to check if a workbook is open and if not then open it
If IsWorkbookOpened("Filename.xls", "C:Documents and ..................
View 3 Replies
View Related
Oct 3, 2012
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
View 2 Replies
View Related
Dec 7, 2012
Here's my macro:
Code:
Sub CopyRow()'
'Copies row to new sheet, highlights it, marks column 'A' as copied.
'
Dim cCell As Range
Set cCell = Selection.Cells(1, 1)
Selection.Copy
Sheets("Sheet2").Select
Rows("2:2").Select
[Code] .....
Is it possible to modify it to paste into a different workbook called c:filesDestination.xlsm, instead of the existing workbook (Source.xlsm)? The destination sheet name is the same (Sheet2). It's OK if both workbooks are open at the same time.
View 2 Replies
View Related
Apr 8, 2013
Is there a way to run a macro in an open workbook when the workbook becomes activated or deactivated? So if I have workbook A open with a macro in it and workbook B open as well, can I have the macro run on workbook A when that becomes the workbook I see on my screen?
View 2 Replies
View Related
Apr 6, 2013
I have created a spreadsheet some time ago and have been asked to improve on it but I'm rusty with VBA.
I have an automated ordering system that saves each sent order as the date e.g "05-04-2013.xls" but the management team want a graph with the data for the last 4 weeks compared. I have created a seperate workbook called "consumables report.xls" which has a column with the products listed followed by columns "Quantity" and "cost" which is repeated for the 4 weeks of the month.
I want to add a button to prompt the user to choose the saved order e.g "05-04-2013.xls" (all orders saved in same directory) to copy and paste the quantity and cost columns (c8,D69) into "consumables report.xls". I got this to work earlier but it would only paste the formulas and not the values. So I need
A prompt to open workbook
Copy range (c8,d69)
Close work sheet
Paste special .value (c8,D69)
I dont care if it has to open the workbook to copy the data as this will only be used once a month so it dosnt matter how slow the code is.
week 1 week 2 week 3 week 4
Product
quantity
cost
quantity
cost
cost
quantity
cost
1
2
3
4
5
6
7
8
View 7 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 17, 2006
I created folder on my desktop to hold various documents. I have master excel document which contains forms and command buttons that open documents from same folder. Is there a way to change code instead of having full file path to specify current directory. I'm worried that if I move this folder to another location then my open file button will not work since location is changed.
Also when I open with command button word document, I have a button on word document to close. I can close document but word application is still active. Can I close word application with the document at the sam time. This is the code I'm using: "ActiveWindow.Close"
View 3 Replies
View Related
Jun 17, 2009
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:
View 14 Replies
View Related
Apr 21, 2006
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 Related
May 30, 2006
I 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'
View 3 Replies
View Related
Apr 29, 2009
I currently have a macro set up to run once a workbook is saved/ closed. This works fine unless tehre is more than one workbook open. When I try it with more than 1 different workbook open I get a run error.
My code begins:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
and then performs the macro. I don't really want to specify the filename as this is liable to change. Is there anyway of adding a "this workbook" command to the text string, and if so where would it go?
View 10 Replies
View Related
Dec 16, 2011
if workbook("Result") is not open then
add new workbook and name it "Result + date and time"
else activate workbook "Result"
View 3 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
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
Oct 27, 2008
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 Related
Mar 25, 2009
File open dialog box, Get the path to textbox? ...
View 7 Replies
View Related
