Get Filename And Path Of Workbook The Macro Is Running In

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


ADVERTISEMENT

Check File Path Of Before Running Macro

Mar 26, 2008

I have a spreadsheet with a macro that saves a backup s/s to a particular folder, but the problem I have need the macro to check the filepath is that of the original s/s before running.

Its so that when people take copies of the main s/s and save them to their desktop I dont want the copies being saved in the backup folder

macro is below

Private Sub Workbook_BeforeClose(Cancel As Boolean)

If ActiveWorkbook.ReadOnly Then Exit Sub

If MsgBox("Do You Want To Save A Backup?", vbYesNo) = vbNo Then Exit Sub

Dim fso As Object
Dim objFiles As Object
Dim myWkBk As String
Dim myFName As String
Dim BkUpDir As String
Dim CountFiles As Integer
Dim mydate As Double

BkUpDir = "k:singstatappealsappeals trackerBackups"

Set fso = CreateObject("Scripting.FileSystemObject")
Set objFiles = fso.GetFolder(BkUpDir).Files

On Error Resume Next
'the actual counting of files takes place here.
If Err.Number 0 Then
CountFiles = 0
Else
CountFiles = objFiles.Count
End If


If CountFiles > 4 Then
MsgBox "You have " & CountFiles & " saved workbooks. Perhaps you should delete some?"
End If

mydate = Format(Now(), "ddmmyy")
myWkBk = ActiveWorkbook.Name
myFName = ActiveWorkbook.FullName

fso.CopyFile myFName, BkUpDir & mydate & myWkBk

End Sub

View 9 Replies View Related

To FIND The Filename In A Path

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

Spaces In Path And Filename

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

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 View Related

Trim Full Path To Filename Only

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

Find Filename In Path Regardless Of Number Of Subfolders

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

Hyperlink To Display Filename Not Full Path

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

Recording Macro To Save Workbook Using Filename In Cell?

Aug 5, 2013

I'm trying to record a macro that calls up the saveas dialogue and uses a filename from a cell (it's the w/c date). I can start recording the macro, open the dialogue box and copy the contents of the cell but i can't get it to paste as the filname.

What I'm trying to achieve is to allow users of a muli-sheet workbook to be able to click on a 'save' button on any sheet and have the workbook save with a filename which includes the current week commencing date. This way all the weeks changes will be in just one file and from the following monday a new file will be started the the old one left alone as an archive.

View 9 Replies View Related

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 View Related

Create & Save Workbook To Same Path As Macro File

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

Macro To Append Multiple Xls File In Single Workbook From Specified Path

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

Macro To Save Workbook By Referencing Cell In Another Workbook For File Name And File Path

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

Macro To Copy Folders To Path Of Open Workbook Based On Cell Values

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

Running A Macro From A Different Workbook

Dec 18, 2008

I have 2 workbooks (Workbook1 and Workbook2).

Workbook 1 contains a button, that calls a macro. This macro calls a macro from Workbook 2.

For this, I am using the Application.Run function.

The Macro in Workbook2 is a simple one line macro that updates cell A2 to a value of 6.

Range("A2").Value = 6

This works fine!

However, I want the the cell A2 in WORKBOOK1 to change to 6, not the cell in Workbook 2.

To add an element of difficulty, Workbook 1 could have any name (I won't know what it is, as the user could change it).

How can I get the Macro in Workbook 2 (Called from Workbook1), to update the cells in Workbook 1.

View 12 Replies View Related

Running Macro In Another Workbook

Sep 17, 2009

I have 2 files in a folder, "Main.xls" and "My Database.xls". "My Database.xls" contains the following macro in 'Module1':

View 2 Replies View Related

Work Around Running Macro On Protected Workbook?

Jun 5, 2014

I cannot run a macro because I am encountering error 1004, intersect of object. i am using a protected workbook, and I dont have the password to unprotect it, but it needs to be fill out using a macro from a different workbook. Is there a way around this?

View 1 Replies View Related

Running A Macro Process On Each Sheet In A Workbook

Jan 27, 2008

I am trying to write a macro that will run another macro (already written) through all the sheets/tabs in a workbook one sheet at a time and will stop once it reaches the last sheet/ tab.

Can you assist me in writing this macro.

View 9 Replies View Related

Macro To Separate File Name And Path From Complete Path

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

Excel 2007 :: Running Macro From Protected Workbook

Mar 13, 2008

I have a macro with "ActiveWorkbook.Protect Password:="password"" . It was working fine in excel 2003. In excel 2007, because of this I am not able to run any macro's in the workbook. I am getting a message "The macro may not be available in this workbook or all macros may be disabled. I can see a security warning in message bar saying macros have been disabled, but I dont have have a option to enable the macros.

View 9 Replies View Related

Show Inactive Workbook Filename

Jun 8, 2014

I open two spreadsheets only. In one of them I would like the filename of the other spreadsheet shown in cell A1.

The reason why is the filename is a 6 number date and I'd like to confirm it showing in full format.

View 3 Replies View Related

Workbook To Check It Filename Once Opened

Dec 29, 2009

I am trying to ensure that m work book keeps the same name even when I email it to others.

I have been told on this forum that it is possible for a workbook to check its own name once opened?

I have two different names that this file can be called 1st is the one that is in use all the time and the 2nd is a archive version where the name changes each month.

The file name I wish to keep the same for the no archived version is Toolbox and a predetermined number I have a workings sheet called workings and in a cell i have a formula =TEXT("Toolbox Application " & Cover!E10,) this will give me the file name that i need to check for example it could be Toolbox Application 00089 or Toolbox Application 00967.

I also have a cmd that archives the workbook and saves it as Toolbox and the Month it was for Toolbox for Jan for example

I also need this to open if it is an archive

if neither of the names match i need the workbook to close and a msgbox to tell user to rename the workbook correctly.

View 9 Replies View Related

How To Replace Workbook Reference Filename On A Formula

Apr 6, 2014

I have to many refrence links I need a macro that replace the filename. How to replace workbook refrence filename on a formula using cell value,

example:

from [13.xlm]2014.Goals'!C8 to ["cell-value".xlm]2014.Goals'!C8

View 1 Replies View Related

Creating New Workbook Then Giving It A Custom Filename?

May 25, 2012

I am building a macro and am just not figuring out how to do this. I am trying to create a new workbook, then give it a name. Simple right? Well, I have the format for the file name to be "mm-dd-yy Transmittal #.xlsx" but I can't figure out how to make the # part of that equation start as 1 but if the file already exists change it to 2, if that exists, change it to 3, and so on. Here's what I have so far:

Code:
Sub Transmittal()
Dim NewBook As Workbook
Dim MyPath As String

[Code]....

I don't think I'm allowed to do "x + 1" so that is probably where my problem is coming from.

View 9 Replies View Related

Link Filename Of Workbook As Text In A Cell....

Jan 13, 2005

I have a workbook that gets saved many times with many different filenames.

All I want to do is have cell A1 display as text the name of the file.

eg similar to the date function, there should be a = filename function .

View 9 Replies View Related

Create New Workbook And Save Filename From Cell Value

Nov 1, 2009

Im trying to create a macro that when i click the button a new workbook is created and saved as the value from C5 cell.

View 9 Replies View Related

Vba Code Which Saves A Workbook With A Variable As The Filename

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

Open A File, Extract & Paste The Filename Into Another Workbook

Aug 1, 2006

Am working on a procedure to open all files in a folder and copy some data from the files into another workbook (master). I have this piece of the code. However, I also need to get the filename (from which data was copied) and paste this into an adjacent cell in the master (so I know which file each data point came from).

View 8 Replies View Related

Workbook.Path Format

Aug 4, 2006

I have a very basic cut copy and paste macro in a workbook (ThisWorkbook) that fails when it reaches the line :

Workbooks.Open Filename:=ThisWorkbook.Path & "" & DataWbk, UpdateLinks:=3

When i hover over this line it states the actual (and correct) path of the workbooks that the macro is relevant to, but the error says the path is unrecognisable.

However, in other similar macros that i have that work ok, when i hover over this line it states : <Object variable or With block variable not set>.

Is there any way that i can set the 'ThisWorkbook.Path' to read this? To my knowledge i have not recorded these two macros differently, yet they are producing these contrasting results.

View 7 Replies View Related

Open Different Workbook In Different Path At The Listbox

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







Copyrights 2005-15 www.BigResource.com, All rights reserved