Excel VBA -Application.run for word routine

[JAWHARRAH]

Hi there, I am self taught and a little confident (not too much) in excel vba. I have an application that I am have just about finished writing . I am stuck on something that I would like to do.
My excel code calls a routine in word, which is working great (a bit messy but it works), while in word the user selects a directory to process some converting of word files to text files.
I want to return the directory the user chose while the word code was running.

Bottom line when the word routine has processed the excel part of the application begins and currently the user is required to select the same directory
I am really, really stuck on this one. I have been able to fumble through and do what I need to have done but this is one little itch I can't scratch.

I would appreciate any help! TIA

 

[Yongle]

while in word the user selects a directory to process some converting of word files to text file
- is the diectory stored in a string variable ?
- what is the name of the variable ?
If not, how is the directory selected by the user used in the (Word bit of the) code - provide that ONE line of code

when the word routine has processed the excel part of the application begins and currently the user is required to select the same directory
- how is the directory that has been selected by the user then used in the Excel code? (provide the line of code which uses that directory)

 

[JAWHARRAH]

while in word the user selects a directory to process some converting of word files to text file
- is the diectory stored in a string variable ? No its a variant
- what is the name of the variable ? SelectedFolderPath
SelectedFolderPath = fd.SelectedItems(1) 'THIS IS SET AS VARIANT
If not, how is the directory selected by the user used in the (Word bit of the) code - provide that ONE line of code
Set fd = Application.FileDialog(msoFileDialogFolderPicker)
when the word routine has processed the excel part of the application begins and currently the user is required to select the same directory
- how is the directory that has been selected by the user then used in the Excel code? (provide the line of code which uses that directory)

filetoopen = Application.GetOpenFilename _
(Title:="Browse to select your txt file for importing", FileFilter:=("Text Files (*.txt), *.txt"))

Hi there, I jumped out of my chair with excitement that you got back to me and so quick!! thank you

I neglected to put excel 2016?

Please let me know what else you need.

Thanks

 

[Yongle]

You may be able to simply set the active drive and path to match what is contained in SelectedFolderPath

Try inserting these 2 lines ...

    ChDrive Split(SelectedFolderPath, ":")(0)
    ChDir SelectedFolderPath

above

    FileToOpen = Application.GetOpenFilename(Title:="Browse to select your txt file for importing", FileFilter:=("Text Files (*.txt), *.txt"))

 

[JAWHARRAH]

Thank you so much for you time. Thanks for that code however putting that into excel has not worked.

Excel calls the word sub routine (ConvertDocsToTxtGo). This opens a userform (it's just a progress bar I created). In that form the dialogue is used to select the directory where the docs are.
Once processed the sub simply tells user all is done and the sub ends.
There is no value or variable passed in or back out.
The variable SelectedFolderPath is only in the word routine and not defined in excel. Is there something I need to pass in, in order to get the directory passed back?

 

[Yongle]

There are various ways to do this and the correct method is dependant on how your current macros are written and where they are located etc

Is procedure ConvertDocsToTxtGo located in ThisDocument module in a WORD document ?
If above is not the case, provide additional information

How is ConvertDocsToTxtGo called from Excel ?

(From a practical user standpoint) could the Excel procedure (the one containing GetOpenFilename) be called to run automatically immediately after ConvertDocsToTxtGo has finished ?

 

[JAWHARRAH]

There are various ways to do this and the correct method is dependant on how your current macros are written and where they are located etc

Is procedure ConvertDocsToTxtGo located in ThisDocument module in a WORD document ?
No I don't know what ThisDocument module is (just checked it out but have not read up on it)

If above is not the case, provide additional information
The sub is in a document called DocConversionToText.docm this is currently in a fixed location.
Theoretically I am thinking all of the files that I am processing would be best in one fixed location
the app is currently converting word files to text, then reading into excel, then manipulating the data, user selects a value and a sheet is generated, once all of the sheets are generated it then exports all of the sheets to csv files, in preparation for uploading into other system.

How is ConvertDocsToTxtGo called from Excel ?
Sub RunWordConversionToText()
Dim wdFile As Variant
wdFile = "ConvertDocsToTxtGo.ConvertDocsToTxtGo"
wdApp.Application.Run wdFile

End Sub
Like I said this part all works fine executing the routine, the routine does what it is supposed to do as well no issue there.
(From a practical user standpoint) could the Excel procedure (the one containing GetOpenFilename) be called to run automatically immediately after ConvertDocsToTxtGo has finished ?
In most cases it could easily run straight after the conversion, however the nature of the business is such that the user may not have time to run the whole process (we're talking 5 minutes tops). I could put criteria for if they want to stop at this point. Obviously they would need to locate the files to import which is what happens now.

 

[Yongle]

Thanks
I will update the thread tomorrow

 

[Yongle]

I have tested suggested method below and believe it to be the simplest way to achieve what you want
- of course you may be doing something I have not anticipated, so let me know if there is something that I have failed to take account of

METHOD
(instead of selecting the folder in Word) Get the user to select the folder in Excel BEFORE the word macro is called, hold that string in a Public variable in Excel and pass that value to Word when the macro is called
SelectedFolderPath is available in Word and also available to Excel (AFTER the word macro ends)

Something like this ....

In WORD

Remove declaration of SelectedFolderPath from ConvertDocsToTxtGo
Remove code for Application.FileDialog from ConvertDocsToTxtGo (now being done in Excel)
Amend ConvertDocsToTxtGo to a bring in the path variable as below

Sub ConvertDocsToTxtGo(SelectedFolderPath As String)
  the variable can be referred to inside the macro
End Sub

In EXCEL
Declare Public variable
Add procedure to utilise Application.FileDialog before calling your other macros

Option Explicit
Public SelectedFolderPath As Variant        'must be placed above all procedures in the module

Sub SelectFolder()
    With Application.FileDialog(msoFileDialogFolderPicker)
        If .Show = -1 Then
            SelectedFolderPath = .SelectedItems(1)
        End If
    End With
    If SelectedFolderPath <> "" Then
        Call MacroToOpenTheWordFile          'replace with the correct name
        Call RunWordConversionToText    
    End If
End Sub

Amend RunWordConversionToText to pass the variable when calling word macro

Sub RunWordConversionToText()
    Dim wdFile As Variant
    wdFile = "ConvertDocsToTxtGo.ConvertDocsToTxtGo"
    wdApp.Application.Run wdFile
'Pass the variable to word when calling the macro
    Call wdApp.Run(wdFile, SelectedFolderPath)
End Sub

Note
Use the code you already have working for Application.FileDialog and simply move it to Excel - above is for illustration only

 

[JAWHARRAH]

I have tested suggested method below and believe it to be the simplest way to achieve what you want
- of course you may be doing something I have not anticipated, so let me know if there is something that I have failed to take account of

METHOD
(instead of selecting the folder in Word) Get the user to select the folder in Excel BEFORE the word macro is called, hold that string in a Public variable in Excel and pass that value to Word when the macro is called
SelectedFolderPath is available in Word and also available to Excel (AFTER the word macro ends)

Something like this ....

In WORD

Remove declaration of SelectedFolderPath from ConvertDocsToTxtGo
Remove code for Application.FileDialog from ConvertDocsToTxtGo (now being done in Excel)
Amend ConvertDocsToTxtGo to a bring in the path variable as below

Sub ConvertDocsToTxtGo(SelectedFolderPath As String)
  the variable can be referred to inside the macro
End Sub

In EXCEL
Declare Public variable
Add procedure to utilise Application.FileDialog before calling your other macros

Option Explicit
Public SelectedFolderPath As Variant        'must be placed above all procedures in the module

Sub SelectFolder()
    With Application.FileDialog(msoFileDialogFolderPicker)
        If .Show = -1 Then
            SelectedFolderPath = .SelectedItems(1)
        End If
    End With
    If SelectedFolderPath <> "" Then
        Call MacroToOpenTheWordFile          'replace with the correct name
        Call RunWordConversionToText  
    End If
End Sub

Amend RunWordConversionToText to pass the variable when calling word macro

Sub RunWordConversionToText()
    Dim wdFile As Variant
    wdFile = "ConvertDocsToTxtGo.ConvertDocsToTxtGo"
    wdApp.Application.Run wdFile
'Pass the variable to word when calling the macro
    Call wdApp.Run(wdFile, SelectedFolderPath)
End Sub

Note
Use the code you already have working for Application.FileDialog and simply move it to Excel - above is for illustration only

Thank you for your response. I did not see this until yesterday.
I did try the code
I do not have a macro to open word I used wdApp.Application.Documents.Open strFile.

So the Call wdApp.Run(wdFile, SelectedFolderPath)
works fine and the variable is fed through to word, however
in order to let the user know their files are processing I have a userform that is called.
my routine ConvertDocsToTxtGo opens the userform

Sub ConvertDocsToTxtGo(SelectedFolderPath As String)
Dim PassFolder As String

PassFolder = SelectedFolderPath
GetProgress.Show

End Sub
my issue now is I can't bring PassFolder through to the form