I use this code to export my divisions to excelfiles and at the end i show a msgbox where the files are stored but i would like to go straight to the directory where my files are created. This works with WshShell if i set the right path after WshShell.run but i would like to use the variable Path from my script. How can i do that?
sub Export set f = activedocument.Fields("extra")
if IsEmpty(f.GetSelectedValues) then set pv = f.GetSelectedValues 'adds selected values if any else set pv = f.GetPossibleValues(KOS) 'adds possible values if none selected end if
set Array1 = f.GetNoValues 'empty array for i = 0 to pv.count-1 'adds values from the first array Array1.Add Array1(i).Text = pv.item(i).Text Array1(i).IsNumeric = false 'if text in your field next
'Creates a path and a filename of the output xls-file in the same folder as the current document Path = ActiveDocument.Evaluate("left(DocumentPath(), index(DocumentPath(), '', -1))") 'Path = "C:\Team_Audit" 'your static path if you dont want a dynamic path
set obj = ActiveDocument.GetSheetObject("TB11") 'the ID of the object you want to export
'Loop through each selection in your field for i = 0 to Array1.count-1 f.Select Array1(i).Text 'Selects one value in your field at a time
'Sets the FileName to be the same as the selection in your chosen field Dim D1 : D1 = replace(Date,"/","_") FileName = Array1(i).Text & D1 & ".xls"
' Starts Excel set XLApp = CreateObject("Excel.Application") ' Makes it run in background XLApp.Visible = False set XLDoc = XLApp.Workbooks.Add
' Set the cell to start in at A1 Set rngStart = XLDoc.Sheets(1).Range("A1")
' Copies the chosen object contents to clipboard obj.CopyTableToClipboard true ' Paste it into excel XLDoc.Sheets(1).Paste() ' Save the excel-file with the dynamic path and filename ' Save the excel-file with the dynamic path and filename XLDoc.SaveAs Path & FileName
' Closes the active document XLDoc.Close ' Close the instanced object references of excel set rngStart = nothing set XLDoc = nothing ' Exits the current running Excel XLApp.Quit
' Closing final object reference set XLApp = nothing next MsgBox("Data weggeschreven naar: " & Path) test(Path) ' trying to give the value of Path to the function test
end sub
function test(Path) Set WshShell = CreateObject("Wscript.Shell") 'test = WshShell.Popup("export started",10) WSHShell.run "C:/" 'THIS WORKS but i would like to use WSHShell.run Path end function
