Grabbing working directory and passing it as variable in macro

newbieX

New Member
Joined
Jul 24, 2013
Messages
35
I am trying to figure out how to grab the working directory and pass it as a variable via a macro.

I am running a python script from a directory that is captured with user input and will always have a results subfolder. In the python script I am running Macro1 from an Excel template that is located in a different directory. The macro loads a dbf table and then formats the it the way I want and saves the newly formatted workbook to the results folder with an xlsx extension. I am having a hard time capturing the working directory and passing that as a variable. I realize that referencing various directories is confusing but the templates are unchanging and stored in a templates(Source) folder. The directory where I execute the python script from varies depending on the user name. (Just to make things more confusing, the python script is also stored in the templates(Source) folder, not the directory where I execute it from.)

When I open up an excel template does the working directory change?

This is the section of the python script I am running that is related to my issue. It works fine.

Code:
import os.path
import win32com.client

myDir = ("C:\\tests") 'the variable grabbed earlier in python script.

mySourceExcelFile = myDir + "\\Source\\Cats1.xlsm" 'template path is not related to myDir normally

xlApp = win32com.client.DispatchEx('Excel.Application')
xlsPath = os.path.expanduser(mySourceExcelFile)
wb = xlApp.Workbooks.Open(Filename=xlsPath)
wb.Application.DisplayAlerts = False
xlApp.Run('Macro1')

xlApp.Quit()

This is my macro. I can't seem to get the path variable right.

Code:
Sub Macro1()
'
' Macro1 Macro
'

Application.DisplayAlerts = False

'    ChDir "C:\tests\Target\results"
'    Workbooks.Open Filename:="C:\tests\Target\results\Cats1.dbf"

'  The two commented out sentences above work but directory needs to be a variable.
'  Current location will always be the Target directory.
'  I want to grab current directory then open Cats1.dbf from its results subfolder.
'  Bombs out at ChDir and Workbooks.Open

Dim myDir As String
Dim myResultsDir As String
Dim myResultsFile As String
myDir = CurDir()
myResultsDir = myDir & "\results"
ChDir myDir & "\results"
myResultsFile = myDir & "\results\Cats1.dbf"
Workbooks.Open Filename:=myResultsFile

'  All of the below works fine when above commented VBA commands are not commented out.
'  SaveAs will need to be a variable path also.

ActiveSheet.ListObjects.Add(xlSrcRange, Range("A1", ActiveCell.SpecialCells(xlLastCell)), , xlYes).Name = _
    "Table1"
Range("Table1[#All]").Select
ActiveSheet.ListObjects("Table1").TableStyle = "TableStyleLight1"

Columns("A:A").Select
Selection.ColumnWidth = 18.43
Columns("B:B").Select
Selection.ColumnWidth = 18.43
Columns("C:C").Select
Selection.ColumnWidth = 18.43

Columns("A:C").Select
With Selection
    .HorizontalAlignment = xlLeft
    .VerticalAlignment = xlBottom
    .WrapText = False
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
End With
   
'  The below pathname will need to be saved as a variable also

ActiveWorkbook.SaveAs Filename:="C:\tests\Target\results\Cats1.xlsx", FileFormat:= _
    xlOpenXMLWorkbook, CreateBackup:=False
    
Range("Table1[#All]").Copy
Sheets.Add After:=Sheets(Sheets.Count)
Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
ActiveSheet.Paste

'    ActiveWindow.Close
End Sub

Any and all help greatly appreciated. Excel 2007.
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,357
Office Version
  1. 365
Platform
  1. Windows
What do you mean by 'working' directory?

You can get the current directory using CurDir and you can get the path for a workbook using ThisWorkbook.Path.
 

newbieX

New Member
Joined
Jul 24, 2013
Messages
35
What do you mean by 'working' directory?

You can get the current directory using CurDir and you can get the path for a workbook using ThisWorkbook.Path.

I did use CurDir

Code:
myDir = CurDir()
myResultsDir = myDir & "\results
ChDir myDir & "\results"

I can't seem to get the syntax right for using CurDir as a concatenated variable. It fails at ChDir myDir & "\results"

When I say working directory, I mean the directory I was in when I executed my python script. I am uncertain if the current directory changes to the templates directory when I open it and execute its macro.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,357
Office Version
  1. 365
Platform
  1. Windows
The concatenation looks fine, are you getting an error message on the line?

If the 'working' directory is the directory you are in when you execute the python script from can't you use os.getcwd() and pass the result to the Excel macro?

Perhaps something like this?
Code:
xlApp.Run('Macro1', os.getcwd())
with the macro altered to take the directory as an argument.
Code:
Sub Macro1(strWorkingDir As String)

By the way, you don't need to change directory to open/save/whatever workbooks, just make sure you specify the full path and filename.
 

newbieX

New Member
Joined
Jul 24, 2013
Messages
35
Code:
xlApp.Run('Macro1', os.getcwd())
did not work as it grabbed the directory that the python script was located in,not the directory it was called from (within ArcGIS). However, the following did work.

Python script:

Code:
import os.path
import win32com.client

mySourceExcelFile = "C:\\tests\\Source\\Cats.xlsm"
myTargetDir ="C:\\tests\\Target"
xlApp = win32com.client.DispatchEx('Excel.Application')
xlsPath = os.path.expanduser(mySourceExcelFile)
wb = xlApp.Workbooks.Open(Filename=xlsPath)
wb.Application.DisplayAlerts = False
xlApp.Run('Macro1', myTargetDir)
xlApp.Quit()

Excel Macro -Shorted somewhat for clarity.

Code:
Sub Macro1(strWorkingDir As String)
'
' Macro1 Macro

'
    Application.DisplayAlerts = False
    
    Workbooks.Open Filename:=strWorkingDir & "\results\Cats1.dbf"
    ActiveSheet.ListObjects.Add(xlSrcRange, Range("A1", ActiveCell.SpecialCells(xlLastCell)), , xlYes).Name = _
        "Table1"
    Range("Table1[#All]").Select
    ActiveSheet.ListObjects("Table1").TableStyle = "TableStyleLight1"

    ActiveWorkbook.SaveAs Filename:=strWorkingDir & "\results\Cats1.xlsx", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
    ActiveWorkbook.Close
    Application.Quit

End Sub

It seems passing it made all the difference. Thanks for the help.

The concatenation looks fine, are you getting an error message on the line?

If the 'working' directory is the directory you are in when you execute the python script from can't you use os.getcwd() and pass the result to the Excel macro?

Perhaps something like this?
Code:
xlApp.Run('Macro1', os.getcwd())
with the macro altered to take the directory as an argument.
Code:
Sub Macro1(strWorkingDir As String)

By the way, you don't need to change directory to open/save/whatever workbooks, just make sure you specify the full path and filename.
 

Forum statistics

Threads
1,182,184
Messages
5,934,130
Members
436,930
Latest member
jvinces

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top