Automate word file via excel

[ysherriff]

Hi all, I have created a macro that is supposed to open a word file and save the word document with a different name in a different location. The names are based on data in range. It seems like everything is working until I come to this code:

ActiveDocument.SaveAs folderpath & "\" & reportName, Password:=EMPLID, ReadOnlyRecommended:=False

I get an error message below. What am I possibly doing wrong? I am not familiar with coding for word documents but this code is in excel and not word.




I have attached the workbook. It is in the vba module titled mod_WordSave. Below is the full code

Option Explicit

Sub Open_Word_Document()

 'Opens a Word Document from Excel

  Dim objWord As Object, folderpathstr As String, fileCount As Integer, NAME As String, EMPLID As String
  Dim currMthStr As String, fileNamePrefix As String, rownum As Integer, nameCount As Integer, i As Integer
  Dim reportName As String, folderpath As String, prctProgress As Single, PMAOutFilePath As String
 
  Application.ScreenUpdating = False
  Application.DisplayAlerts = False
 
  Set objWord = CreateObject("Word.Application")
  objWord.Visible = True
 
  nameCount = Range("NAME_COUNT_LEVEL")

  For i = 1 To nameCount
  NAME = Range("DSM_NTID_START_POINT").Offset(i, 1)
  EMPLID = Range("DSM_NTID_START_POINT").Offset(i, 0)
  
  
  Application.StatusBar = "Generating " & NAME & " Report....." & i & " of " & nameCount
  prctProgress = i / nameCount * 100
  ProgressBox.Increment prctProgress, "Creating report for " & NAME & "- " & i & " out of " & nameCount


  reportName = "Q1-2016 Sales Performance" & " - " & NAME & ".docx"
  folderpath = PMAOutputFilePath & "\" & NAME
  
  'Change the directory path and file name to the location
  'of your document
  
  
  objWord.Documents.Open "\\beicorp1\sales_dept\SPandA\Sales Operations\Workstreams\Sales KPI Dashboard\2016\Sales PMA Forms.docx"

  ActiveDocument.SaveAs folderpath & "\" & reportName, Password:=EMPLID, ReadOnlyRecommended:=False
  
  ActiveDocument.Close , False
  
 Next i
  Application.StatusBar = False
  Sheets("Control").Select
  ProgressBox.Hide
  MsgBox "Reports have been generated succussfully!", vbInformation
  
  Application.DisplayAlerts = True
  Application.ScreenUpdating = True
 End Sub

Thank you very much.

 

[Marc L]

Hi !

As here it's an Excel forum …
Check any variable value of the codeline where error occurs !

Another way : test on a local hard disk path.
If code works on a local path but not on a network, meet your IT …
​

 

[ysherriff]

Thank you.