I have a macro that runs within Word that reads a bunch of excel files in a defined path set in the Macro and extract them into the Word document. But the path needs to be define where the files are saved in. Can someone show me how to make the macro run using the relative path.
Sometime these files are saved on the network or D: drive or C: drive and it becomes cumbersome to remember to change the path all the time. So by making the path relative to say "...\Code Inspection\" it will eliminate in having to define the path when the files are moved.
Below is the line of code where the macro looks for the path.
I also attached the document where the Macro is saved in along with the excel files that will be tested.
I set the path to read as strExcelFolder = "..\Code Review\" ...and that did not work.
'Excel source file folder
Dim strExcelFolder As String
strExcelFolder = "C:\Code Review\"
'get the first excel file name
Dim strExcelFile As String
strExcelFile = Dir(strExcelFolder & "*.xls", vbNormal)