Common File Location

[NewToVBA1]

Hi,
I have a master file that has macros. These macros opens and copies data from files located in the same folder as the master. So, all my macros in the master file has a file path declared as a string. If someone else uses my macros, they have to go to the individual macros to change the file path. I know there is a function that you can use to select the files using windows manually. But, I was wondering if there is a way to insert the path names in the macros so that if the user copies the other files in the same folder as the master file, he or she does not have to go to the individual macros in the master files and change the file name. No matter where the folder is, the user needs to place the files and the master file in the same folder and start running the macros.
Any help will be very much apprecaited.
Thanks

 

[Joe4]

The VBA command
ActiveWorkbook.Path
will give you the current path that the file you are accessing resides in.
So you can use that to capture the path and build it into your code, i.e.

Dim myFilePath as String
myFilePath=ActiveWorkbook.Path

 

[NewToVBA1]

Thanks. But his does not work, my wild guess : probably because the macros are opening other files.

 

[Joe4]

<!-- / icon and title --><!-- message -->

Thanks. But his does not work, my wild guess : probably because the macros are opening other files.

Sure it does. If you run the code as the first step of the macro before it starts opening any other files, it will capture the path that the macro file resides in. If I understand your original question correctly, that was what you were looking for, right? To capture the path that the macro workbook resides in?

 

[NewToVBA1]

I am pasting a part of one of my macros , the macro below works:

Dim wb As Workbook
Dim ws As Worksheet, ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet, ws4 As Worksheet, ws5 As Worksheet, ws6 As Worksheet, ws7 As Worksheet, wsFiles As Worksheet
Dim strPath As String, strMsg As String, lMsgType As Long
Dim cell As Range
Dim RowNdx As Long


Set wsFiles = ThisWorkbook.Sheets("Look_UpTable") ' Worksheet with file list
Set ws7 = ThisWorkbook.Sheets("Dummy1")

strPath = "C:\MyFolder\AfterFeb2011\New\My-Tool\" ' Folder of the data files'

Application.ScreenUpdating = False




For Each cell In ws7.Range("D1", ws7.Range("D" & Rows.Count).End(xlUp))



On Error Resume Next
' Attempt to open the next file
Set wb = Application.Workbooks.Open(strPath & cell.value)
On Error GoTo 0

If Not wb Is Nothing Then 'if the file was opened

..................................
..................................

The macro below(as per your suggestion) does not work:

Dim wb As Workbook
Dim ws As Worksheet, ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet, ws4 As Worksheet, ws5 As Worksheet, ws6 As Worksheet, ws7 As Worksheet, wsFiles As Worksheet
Dim strPath As String, strMsg As String, lMsgType As Long
Dim cell As Range
Dim RowNdx As Long

strPath = ActiveWorkbook.Path

Set wsFiles = ThisWorkbook.Sheets("Look_UpTable") ' Worksheet with file list
Set ws7 = ThisWorkbook.Sheets("Dummy1")

Application.ScreenUpdating = False


For Each cell In ws7.Range("D1", ws7.Range("D" & Rows.Count).End(xlUp))



On Error Resume Next
' Attempt to open the next file
Set wb = Application.Workbooks.Open(strPath & cell.value)
On Error GoTo 0

If Not wb Is Nothing Then 'if the file was opened

..................................
..................................

 

[Joe4]

I am pasting a part of one of my macros , the macro below works:

Pasting a part of the code may not be good enough, because in order to capture the current path that the macro file resides in, the code to capture the active file path needs to happen before you do anything else with any other files.

Is there anything else happening before this?
Can you confirm that I understand you correctly and you are trying to capture the path your macro workbook resides in?

 

[NewToVBA1]

Thanks Again.

I am trying to assign the same file path as the master file to the different macros(written in my master file). These marcos when run opens many files , copies data and closes them . These other files are all loacted in the same folder as the master file. As you can see in the marco I have posted earlier has a path name. There are other macros below that macro that uses the same path name. If someone else uses my code, he or she has to create the same folders as I have and place the master files and all other files exactly at the same location in his or her computer because my macros will only follow the the file path written in the macros. I wanted to make changes in my code in such a way that if the user places the master file and all other files in his or her folder of choice, the code should work and the users do not have to open the macros and edit the file path.

 

[Joe4]

Open main macro workbook, do not run anything at all, and place this macro in it.

Sub CaptureFilePath()
    Dim myFilePath As String
    myFilePath = ActiveWorkbook.Path
    MsgBox myFilePath
End Sub

Now run it. Does the Message Box return the path that you want?

Now, place the body of the code in your main macro and run it. Does the Message Box return the same path as the stand-alone macro?

 

[NewToVBA1]

Yes. it does gives me the file path in both the cases. But, the main macro does not run...
I think because of the following line in the code:

Set wb = Application.Workbooks.Open(strPath & cell.value)

This line opens files of the path (strPath), with names written in the cells of a column in a worksheet in the master file.

 

[Joe4]

There may be a minor issue preventing it from working - you need to add the last backslash after the file path, i.e.:

strPath = ActiveWorkbook.Path & "\"

Does that solve the problem?