+ Reply to Thread
Results 1 to 11 of 11

need a VBA code to open, and save a file in specific folder using cell file paths

  1. #1
    Registered User
    Join Date
    06-23-2022
    Location
    Cheshire
    MS-Off Ver
    365 apps for enterprise
    Posts
    34

    need a VBA code to open, and save a file in specific folder using cell file paths

    Hi I have looked everywhere for VBA code to open a Word Document based on a file path location and filename in a cell, then save and close it into another location, also entered into a cell.

    I have come across this code, but unsure as to how to alter it to make it work the way i need it to :

    Sub automateword()
    Set wordapp = CreateObject("word.Application")
    wordapp.documents.Open "C:\Users\DT168\Desktop\KTW\test.docx"
    wordapp.Visible = True
    End Sub

    i want it to be able to reference the cell to get the file location.

    And I have no idea how to make it then save it to a specific location, also from a cell file path reference.

    For reference the opening file path would be in A1 and the saving file path would be in A2

    Many thanks in advance

  2. #2
    Registered User
    Join Date
    08-15-2021
    Location
    Denmark
    MS-Off Ver
    365
    Posts
    6

    Re: need a VBA code to open, and save a file in specific folder using cell file paths

    Hi,

    Im not shure if this can help you.
    Hope that you can use some of it and get it to work as you want.


    Sub Gem_som_PDF_dansk()
    '
    ' Overfør_til_PDF Makro

    Dim thisPath As String, docName As String

    thisPath = Application.ActiveWorkbook.Path

    docName = thisPath & "\TO " & Range("C4") & " - " & " DK - " & Range("C6") & " - " & Format(Range("C5"), "dd-mmm-yyyy") ' Makes the filename.

    Sheets("TO dansk").Activate
    'Range("C4").Select
    With Sheets("TO dansk")
    .ExportAsFixedFormat _
    Type:=xlTypePDF, _ ' Save it as PDF
    Filename:=docName, _
    Quality:=xlQualityStandard, _
    IncludeDocProperties:=True, _
    IgnorePrintAreas:=False, _
    OpenAfterPublish:=True

    End With

    End Sub

  3. #3
    Registered User
    Join Date
    06-23-2022
    Location
    Cheshire
    MS-Off Ver
    365 apps for enterprise
    Posts
    34

    Re: need a VBA code to open, and save a file in specific folder using cell file paths

    Cant seem to get that working, i just keeps coming up with highlighting of text within the VBA that it cant get past, plus not sure how to tailor it to get it 2 look at 2 cells instead of the 3, cheers anyway.

  4. #4
    Forum Expert ByteMarks's Avatar
    Join Date
    07-23-2018
    Location
    UK
    MS-Off Ver
    O365 32bit (Windows)
    Posts
    3,175

    Re: need a VBA code to open, and save a file in specific folder using cell file paths

    To open the file and save it as something else, try this

    Please Login or Register  to view this content.

    However, if you're not needing to make any changes, you could just copy the file with a new name.

    Please Login or Register  to view this content.

  5. #5
    Valued Forum Contributor
    Join Date
    08-03-2012
    Location
    Newcastle
    MS-Off Ver
    Excel 2007, 2010, 2013, 2016, Office 365
    Posts
    492

    Re: need a VBA code to open, and save a file in specific folder using cell file paths

    ..and if you wanted to move that file from one folder to another, you could use..

    Please Login or Register  to view this content.
    Of course, this assumes your cell values in A1 and A2 refer to folders that already exist etc etc etc

    zeddy

  6. #6
    Registered User
    Join Date
    06-23-2022
    Location
    Cheshire
    MS-Off Ver
    365 apps for enterprise
    Posts
    34

    Re: need a VBA code to open, and save a file in specific folder using cell file paths

    Hi this works to open the file, however it then asks me if I want to saves as instead of automatically saving the document, it seems to have a problem with wordDoc.SaveAs DestFile$, is there any way to reference a cell as the name of the saved document, say a name in A3 entered by the user , hope that makes sense.

  7. #7
    Registered User
    Join Date
    06-23-2022
    Location
    Cheshire
    MS-Off Ver
    365 apps for enterprise
    Posts
    34

    Re: need a VBA code to open, and save a file in specific folder using cell file paths

    Hi, managed to get it all working, however the document has been changed to include a mail merge from the spreadsheet which I have manged to do, however the code is bypassing the SQL as a no instead of a yes and just saving the original document without updating the mail merge, is there something I could add in (and where) to enable the mail-merge to update the document and then save it in its new form.

    Also is there any way to save it as a PDF, thank you for your patience in this situation.

  8. #8
    Forum Expert ByteMarks's Avatar
    Join Date
    07-23-2018
    Location
    UK
    MS-Off Ver
    O365 32bit (Windows)
    Posts
    3,175

    Re: need a VBA code to open, and save a file in specific folder using cell file paths

    Something like this to save as a pdf.
    It's difficult to say regarding the SQL without seeing more.

    Please Login or Register  to view this content.

  9. #9
    Registered User
    Join Date
    06-23-2022
    Location
    Cheshire
    MS-Off Ver
    365 apps for enterprise
    Posts
    34

    Re: need a VBA code to open, and save a file in specific folder using cell file paths

    Cheers I will attach a screenshot of the dialog box that comes up if you open up the document without using the spreadsheet, this dialog box seems to be bypassed as "no" when the VBA opens and then saves the document which means that the SQL Mail-merge doesnt update the document.
    Attached Images Attached Images

  10. #10
    Forum Expert ByteMarks's Avatar
    Join Date
    07-23-2018
    Location
    UK
    MS-Off Ver
    O365 32bit (Windows)
    Posts
    3,175

    Re: need a VBA code to open, and save a file in specific folder using cell file paths

    As far as I can tell you need to either include the SQL as part of the VBA, or disable this prompt via the registry.

    https://learn.microsoft.com/en-us/of...opendatasource

    https://support.microsoft.com/en-us/...2-42ecdccbaced

  11. #11
    Registered User
    Join Date
    06-23-2022
    Location
    Cheshire
    MS-Off Ver
    365 apps for enterprise
    Posts
    34

    Re: need a VBA code to open, and save a file in specific folder using cell file paths

    I've found this but not sure how to integrate it into the VBA you have provided (the PDf bit works brilliantly) : http://www.vbaexpress.com/forum/show...e-a-mail-merge

    The registry edit cannot be done as this is being run on several computers of varying security degrees.

    any advice would be most appreciated
    Last edited by JSD100; 10-28-2022 at 03:58 AM.

+ 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] List all file paths in a folder plus the file
    By myobreportguru in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 07-29-2022, 04:35 AM
  2. [SOLVED] Using Cell References To Set File Open/Save Paths
    By jayfin in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-23-2021, 10:47 AM
  3. Replies: 8
    Last Post: 12-02-2019, 03:42 PM
  4. Read File.Paths in selected Folder + open relevant files
    By PatrickMiller in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-17-2017, 08:51 AM
  5. Need help with code to save file in a folder location based on a cell value
    By msantucci in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-10-2015, 05:56 PM
  6. VBA Go down list of file paths, open, and copy specific cells from each
    By b.frost in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-05-2015, 07:25 AM
  7. [SOLVED] macro to save file to specific folder with file name from cell reference
    By mezza89 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-06-2013, 11:32 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