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

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

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.

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.

..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

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.

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.

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.

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.

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