+ Reply to Thread
Results 1 to 7 of 7

Manually insert Word Document Path via a InputBox in a Macro

Hybrid View

Devianey26 Manually insert Word Document... 11-15-2013, 09:54 PM
patel45 Re: Manually insert Word... 11-16-2013, 03:24 AM
Devianey26 Re: Manually insert Word... 11-16-2013, 03:06 PM
mikerickson Re: Manually insert Word... 11-16-2013, 03:21 PM
Devianey26 Re: Manually insert Word... 11-17-2013, 02:53 PM
Fotis1991 Re: Manually insert Word... 11-17-2013, 02:57 PM
Devianey26 Re: Manually insert Word... 11-16-2013, 03:07 PM
  1. #1
    Registered User
    Join Date
    11-15-2013
    Location
    U.S.A
    MS-Off Ver
    Excel 2010
    Posts
    24

    Manually insert Word Document Path via a InputBox in a Macro

    Hi!
    I'm new to the forum but have seen some great replies and hope I can get some help. Here is my problem. I have created a Macro that creates a Word document. The path of where the document is saved to is hard coded into the Macro. What I need is code that will allow the user to enter the path they want to store the document in once it is created. This will help when the Excel document is used by different users. I'm trying to avoid using a constant. Here is a copy of the code I am currently using:

    Sub CreateNewWordDoc()
    
    Const strPath As String = "C:\Users\Tiny\Desktop\CaseFolder\" 'The last characters has to be a "\"
    
    myMsg1 = "Do you want to create a Word Document?"
    myTitle = "Create Word Document"
    
    Select Case MsgBox(myMsg1, vbQuestion + vbYesNo, myTitle)
        Case vbYes
        
        strFileName = InputBox("Please enter file name", "Create new file")
            
        Set wrdApp = CreateObject("Word.Application")
        
        wrdApp.Visible = True
        
        Set wrdDoc = wrdApp.Documents.Add
         
        With wrdDoc
            
            extension = ".docx" '".doc"
    
            If Dir(strPath & strFileName & extension) <> "" Then
                Kill "strPath & strFileName & extension"
                
            End If
            
            On Error Resume Next
            
            .SaveAs (strPath & strFileName & extension)
            
            .Close ' close the document
            
            
        End With
        
        
    Dim Word As Object: Set Word = CreateObject("Word.Application")
     
        Word.Visible = True
        
        Set docWD = Word.Documents.Open(strPath & strFileName & extension)
    
        wrdApp.Quit ' close the Word application
        Word.Quit ' close Word sheet
        Set wrdDoc = Nothing
        Set wrdApp = Nothing
        
        Case vbNo
        
    End Select
    
    
    End Sub
    Last edited by Devianey26; 11-16-2013 at 03:00 PM.

  2. #2
    Forum Expert
    Join Date
    07-15-2012
    Location
    Leghorn, Italy
    MS-Off Ver
    Excel 2010
    Posts
    3,431

    Re: Manually insert Word Document Path via a InputBox in a Macro

    Sub CreateNewWordDoc()
    myMsg1 = "Do you want to create a Word Document?"
    myTitle = "Create Word Document"
    Select Case MsgBox(myMsg1, vbQuestion + vbYesNo, myTitle)
        Case vbYes
        strFileName = InputBox("Please enter full file name (with path and extension)", "Create new file")
        Set wrdApp = CreateObject("Word.Application")
        wrdApp.Visible = True
        Set wrdDoc = wrdApp.Documents.Add
        With wrdDoc
            If Dir(strFileName) <> "" Then Kill strFileName
            .SaveAs (strFileName)
            .Close ' close the document
        End With
        wrdApp.Quit ' close the Word application
        Set wrdDoc = Nothing
        Set wrdApp = Nothing
        Case vbNo
        
    End Select
    End Sub
    Last edited by patel45; 11-16-2013 at 03:26 AM.
    If solved remember to mark Thread as solved

  3. #3
    Registered User
    Join Date
    11-15-2013
    Location
    U.S.A
    MS-Off Ver
    Excel 2010
    Posts
    24

    Re: Manually insert Word Document Path via a InputBox in a Macro

    Hi Patel45,

    Thank you so much for your reply, it was exactly what I needed. Can you help me with my next problem? Now I need to open the newly created word document. As before, I need to allow the user to manually input the location of the Word document. Here is my current code, it works great but the Path is hard coded into the code.

    Sub Open_Word_Document()
    
    Const strPath As String = "C:\Users\inina\Desktop\CaseFolder\" 'The last characters has to be a "\"
    Dim strFileName As String
    Dim extension As String
    
    myMsg1 = "Do you want to open a Word Document?"
    myTitle = "Open Word Document"
    
    Select Case MsgBox(myMsg1, vbQuestion + vbYesNo, myTitle)
        Case vbYes
    
    strFileName = InputBox("Please enter file name", "Open Word Document")
    If strFileName = vbNullString Then Exit Sub
    
        extension = ".docx" '".doc"
    
    Dim Word As Object: Set Word = CreateObject("Word.Application")
    
        Word.Visible = True
    
        Set docWD = Word.Documents.Open(strPath & strFileName & extension)
    
    
    docWD.SaveAs ThisWorkbook.Path & "\" & strFileName, FileFormat:=wdFormatDocument
    'ThisWorkbook.Sheets("REPORT").Range("C7:J56").Copy
    'Word.Selection.Paste
    
        
        Case vbNo
           
    End Select
    
    End Sub

  4. #4
    Forum Expert mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,229

    Re: Manually insert Word Document Path via a InputBox in a Macro

    A different user interface would be

    ' ...
    strFileName = Application.GetSaveAsFilename
    If strFileName = "False" Then Exit Sub: Rem cancel pressed
    '...
    docWD.SaveAs strFileName, FileFormat:=wdFormatDocument
    '...
    _
    ...How to Cross-post politely...
    ..Wrap code by selecting the code and clicking the # or read this. Thank you.

  5. #5
    Registered User
    Join Date
    11-15-2013
    Location
    U.S.A
    MS-Off Ver
    Excel 2010
    Posts
    24

    Re: Manually insert Word Document Path via a InputBox in a Macro

    Hi Mikerickson,

    Your solution also worked great!!! I also used your code to open the document once created, which answered the second part of my question. Thanks!

  6. #6
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Manually insert Word Document Path via a InputBox in a Macro

    As that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED,as per Forum Rule #9. Thank you.

    Also, as a new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  7. #7
    Registered User
    Join Date
    11-15-2013
    Location
    U.S.A
    MS-Off Ver
    Excel 2010
    Posts
    24

    Re: Manually insert Word Document Path via a InputBox in a Macro

    Hi Fotis1991,

    Thank you for your reply, the matter has been resolved.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Get directory path where macro was opened manually.
    By welchs101 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-14-2012, 09:54 PM
  2. Macro to insert 2 graphs from seperate excel tabs into a single word document
    By rpt21 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-06-2010, 04:59 PM
  3. Macro to insert Image into Word document
    By ChrisMattock in forum Excel General
    Replies: 3
    Last Post: 09-13-2007, 03:45 AM
  4. [SOLVED] Can you automatically insert a file path in an Excel document?
    By Mimi in forum Excel General
    Replies: 2
    Last Post: 03-21-2006, 08:15 PM
  5. Printing Word Document from Hyperlink -- Can't get the full path!!! Please Help
    By JadPlane in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-17-2005, 07:06 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1