Grabbing working directory and passing it as variable in macro

[newbieX]

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.

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.

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.

 

[Norie]

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]

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

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]

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?

xlApp.Run('Macro1', os.getcwd())

with the macro altered to take the directory as an argument.

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]

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:

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.

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?

xlApp.Run('Macro1', os.getcwd())

with the macro altered to take the directory as an argument.

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.