Solved

Defining a relative path in VB in a Macro created in VBA

Posted on 2008-10-09
18
1,905 Views
Last Modified: 2013-11-25
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)
Import-CodeReview-Comments.doc
KN1CHK1-CodeReview-button.xls
KN1CHK2-CodeReview-dircomp.xls
KN1CHK3-CodeReview-dirstat.xls
0
Comment
Question by:nedbacan
  • 9
  • 9
18 Comments
 
LVL 9

Expert Comment

by:dmang
ID: 22680003
HI nedbacan

Relative to what?
The path of the word macro project can be extracted, or you can include a dialog for folder selection, and then it doesn't matter where the files are.

Here's some code that you can use for folder navigation and selection:

Private Type BROWSEINFO ' used by the function GetFolderName
    hOwner As Long
    pidlRoot As Long
    pszDisplayName As String
    lpszTitle As String
    ulFlags As Long
    lpfn As Long
    lParam As Long
    iImage As Long
End Type

Private Declare Function SHGetPathFromIDList Lib "shell32.dll" Alias "SHGetPathFromIDListA" (ByVal pidl As Long, ByVal pszPath As String) As Long
Private Declare Function SHBrowseForFolder Lib "shell32.dll" Alias "SHBrowseForFolderA" (lpBrowseInfo As BROWSEINFO) As Long

Sub GetTheFolder()
    strFolder = GetFolderName("Select the folder containing your files")
    If strFolder = "" Then
        MsgBox "Folder selection was not performed", vbOKOnly + vbExclamation, "Select Folder"
    End If
End Sub
Function GetFolderName(Msg As String) As String

    ' returns the name of the folder selected by the user
    Dim bInfo As BROWSEINFO, path As String, r As Long, X As Long, pos As Integer
    bInfo.pidlRoot = 0& ' Root folder = Desktop
    If IsMissing(Msg) Then
        bInfo.lpszTitle = "Select a folder." ' the dialog title
    Else
        bInfo.lpszTitle = Msg ' the dialog title
    End If
    bInfo.ulFlags = &H1 ' Type of directory to return
    X = SHBrowseForFolder(bInfo) ' display the dialog
    ' Parse the result
    path = Space$(512)
    r = SHGetPathFromIDList(ByVal X, ByVal path)
    If r Then
        pos = InStr(path, Chr$(0))
        GetFolderName = Left(path, pos - 1)
    Else
        GetFolderName = ""
    End If
End Function

dmang
0
 

Author Comment

by:nedbacan
ID: 22680691
Relative paths ( "..\code inspection" ) changes depending upon where the root folder will be saved in. For instance, if the root folder is saved on the c drive "c:\code inspection"  and I would like to move the Word document (macro) to the "D:\code inspection"  or "C:\ned\code inspection\"  ...the macro will find the spreadsheets (xls files) ...I would not have any problem in doing so as long as the word document (macro) is within the root folder "..\code inspection.  

In regards to your dialog, where will the code be saved in the Macro?  can you use the previous attachment (CodeReview-Comments.doc) to show me how to go about implementing your code into my the macro . Please excuse me for asking you this ..is that I am somewhat new to all this dialog and macro stuff, and the best way to learn is to see it run.  

Thank you.
0
 
LVL 9

Expert Comment

by:dmang
ID: 22680907
Hi
I have added the code to your document, where you set up the folder holding the Excel files.
dmang

Import-CodeReview-Comments---Upd.doc
0
 

Author Comment

by:nedbacan
ID: 22680970
I am sorry but it does not find the excel files.

I opened the Imort-CodeReview-Comments--Upd.doc
The Browse for Folder dialog appears.
I select the folder (i.e "c:\code  review")  and hit "OK"
and nothing runs.
Did you preformed a test with the excel files I send you?
What can I be doing wrong?
0
 
LVL 9

Expert Comment

by:dmang
ID: 22681647
HI there
The attached is looking better.
dm
Import-CodeReview-Comments---Upd.doc
0
 

Author Comment

by:nedbacan
ID: 22681749
I am sorry but when it runs, it ends by opening one of the excel files.

Can the dialog be eliminated and the be able to run the maco where ever the Word document is moved to along with the "..\code inspection" folder.  As long as the word document exist in the code inspection folder no matter what Drive is on, it will run the macro and extract the data from the excel files.

If you think, the dialog can be worked in smoothly, it will be great but how about using my statement above.
0
 
LVL 9

Expert Comment

by:dmang
ID: 22681938
HI there
It should be properly integrated right now. It should go thru all files in the selected forlder and work on Excel files only.
I'll attach another variation as you have indicated. This picks up the path name from the location of the word doc, but for me, doe not pick up any excel files.

dm
Import-CodeReview-Comments---2.doc
0
 

Author Comment

by:nedbacan
ID: 22682525
I get an error "Run Time error '6068'  ... Programatic access to Visual Basic Project is not trusted.
In the Debug mode ..I get the 2nd "strExcelFolder ..." highlighted.
'Excel source file folder
Dim strExcelFolder As String
strExcelFolder = "..\\Code Review\"
strExcelFolder = GetPathName(Application.VBE.ActiveVBProject.FileName)

Any reason why it failed.
0
 
LVL 9

Expert Comment

by:dmang
ID: 22687222
Ooops...
VBA has the unique ability to look wtihin itself and actually modify macro code while running.  The function GetPathname needs a string - Application.VBE.ActiveVBProject.FileName in this case - which causes the error.
Close your file.
Go to the Word main menu and select Tools | Macro | Security.  The form will have two pages - Security on one, and Trusted Sources (Office xp) or Trusted Publishers (OFfice 2003).  On the Trusted .. .page there is a checkbox (bottom) indicating Trust Access to visual basic project.  Check this, and then reopend your file.
dmang
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:nedbacan
ID: 22688933
Hhhmm ..I followed your steps, re-open the document, and did not see the macro run or got an output.

On my initial document with the macro I send you, it worked fine, the excel files were picked up. But the problem is that is cannot be moved without having to change the path in the code.  I am confuse what has been accomplish or what state is the code in.

 
0
 
LVL 9

Expert Comment

by:dmang
ID: 22689344
Please reattach the doc
I'll have a look today if I can, and on Tuesday if I don't get to it.
thanks
dm
0
 

Author Comment

by:nedbacan
ID: 22689429
Attached is the original document.  On your c: drive, create a folder call Code Review.

Copy the excel file and document within this folder.
Open the Word document, and the Macro will generate a table with the data that was extracted from the excel sheets.
Thank you

Import-CodeReview-Comments--orig.doc
KN1CHK1-CodeReview-button.xls
KN1CHK2-CodeReview-dircomp.xls
0
 
LVL 9

Expert Comment

by:dmang
ID: 22710769
OK .. missed something in the path.  This should work regardless of where the word doc is located.
Try the attached.
dm
Import-CodeReview-Comments-Updat.doc
0
 

Author Comment

by:nedbacan
ID: 22711541
Yes it does work !!  .. But when the User cancels the file browser ..a message box appears "You didn't select a folder" ...then when clicking "OK" you get a Run Time error '1004'. Can you prevent the run time error from happening.
User tend to make wrong selections so it will be nice to get rid of this error.
Also, when the document gets opened, it will be nice for the Macro to clear the old table and extract the new data, (Not append).  The data should only be saved by using the "SAVED AS" to prevent the document keeping the extracted data....the next open it will remove the data (or no data) and add the new extraction.
0
 
LVL 9

Expert Comment

by:dmang
ID: 22711724
OK
Try the attached. It handles the cancel  as well as incorrec folder selection.
dm
Import-CodeReview-Comments-Updat.doc
0
 

Author Comment

by:nedbacan
ID: 22712069
Great ...the errors are handled.  The solution is solved.  

Can I increase the point value (100 more) after you accept the following questions,
Can your method do subdirectories.
For instance, I would like to extract the comments from c:\Comment1 and c:\Comment1\Comment2
So by just selecting Comment1, the macro will also read whats in Comment2, and extract the comments into the table along with what was found in Comment1.

Can the Word document be prevented frorm being saved, so when the next time the Macro is runned it will not append the new comments with the previous comments. If User wants to save the document he will have to do a SAVE AS.
 
0
 
LVL 9

Accepted Solution

by:
dmang earned 350 total points
ID: 22713346
OK ... this should wrap it!!

dm
Import-CodeReview-Comments-Updat.doc
0
 

Author Comment

by:nedbacan
ID: 22714478
Worked as I requested ....Thanks for your help.
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now