Excel vba to save as word doc

[ipbr21054]

Please can you advise how i save a file as .doc

I have this below but debug fails to the line in red

Private Sub Print_Invoice_Click()
 
  Dim sPath As String, strFileName As String
  
  strFileName = "C:\Users\Ian\Desktop\REMOTES ETC\DR\DR COPY INVOICES\" & Range("L4").Value & ".doc"
  If Range("L18") = "" Then
    MsgBox ("PLEASE SELECT A PAYMENT TYPE "), vbCritical, "PAYMENT TYPE WAS NOT SELECTED"
    Range("L18").Select
    
    Exit Sub
  End If
  
  strFileName = "C:\Users\Ian\Desktop\REMOTES ETC\DR\DR COPY INVOICES\" & Range("L4").Value & ".doc"
  If Dir(strFileName) <> vbNullString Then
    MsgBox "INVOICE " & Range("L4").Value & " WAS NOT SAVED AS IT ALLREADY EXISTS", vbCritical + vbOKOnly, "INVOICE NOT SAVED MESSAGE"
  Exit Sub
  
  End If
    With ActiveSheet
    .ExportAsFixedFormat Type:=xlType.doc, fileName:=strFileName, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False
    
  End With
  
  MsgBox "HI"
  MsgBox "ONCE PRINTED CLICKING OK WILL" & vbNewLine & vbNewLine & "SAVE INVOICE " & Range("L4").Value & " CLEAR PAGE INFO & DELETE THE GENERATED PDF ", vbExclamation + vbOKOnly, "PRINT SAVE & CLEAR MESSAGE"
  
  Dim MyFile As String
  MyFile = "C:\Users\Ian\Desktop\REMOTES ETC\DR\DR SCREEN SHOT PDF\" & Range("G13").Value & ".doc"
  If Dir(MyFile) <> "" Then Kill MyFile
  
    Dim i As Long, lRow As Long, ws As Worksheet
    Set ws = Application.Worksheets("DATABASE")
    lRow = ws.Cells(Rows.Count, 1).End(xlUp).Row
    For i = 6 To lRow
        If Trim(Range("G13").Value) = Trim(ws.Cells(i, 1).Value) Then

            If ws.Cells(i, 16).Value = "" Then
                ws.Cells(i, 16).Value = Range("L4").Value  ' adding invoice number to INV sheet "P"
                ActiveSheet.Hyperlinks.Add ws.Cells(i, 16), Address:="C:\Users\Ian\Desktop\REMOTES ETC\DR\DR COPY INVOICES\" & Range("L4").Value & ".doc"
                MsgBox "INVOICE " & ws.Cells(i, 16).Value & " WAS HYPERLINKED SUCCESSFULLY" & vbNewLine & vbNewLine & "GENERATED PDF WAS ALSO DELETED ", vbInformation, "HYPERLINK SUCCESSFULL MESSAGE"
            Else
                If MsgBox("COLUMN CELL P ISNT EMPTY " & ws.Cells(i, 16).Value & " IS ENTERED IN IT." & vbNewLine & "WOULD YOU LIKE TO CORRECT IT ?", vbCritical + vbYesNo, "COLUMN P NOT EMPTY MESSAGE") = vbYes Then
                    ws.Activate
                    ws.Cells(i, 16).Select
                End If
                Exit Sub
            End If
        End If
        
    Next i
    
    Range("G14:G18").ClearContents
    Range("L14:L18").ClearContents
    Range("G27:L36").ClearContents
    Range("G46:G50").ClearContents
    Range("L4").Value = Range("L4").Value + 1
    Range("G13").ClearContents
    Range("G13").Select
    
    Call PasteIfFormulas_Click
    
    ActiveWorkbook.Save

End Sub

 

[rollis13]

Just a little hint: .ExportAsFixedFormat Type:= works only with xlTypePDF and xlTypeXPS so you need to change this part of code to whatever else you really need to do in your project.
Link1, Link2

 

[ipbr21054]

Hi,
Been on it a while & have got nowhere at all.

Just need to save as Word .doc.
A saturday wasted trying top sort this

 

[ipbr21054]

Can you advise please the line of code i need to use to save as Word .doc

 

[Chris_010101]

Can you advise please the line of code i need to use to save as Word .doc

Try

With ActiveSheet.ExportAsFixedFormat Type:=xlType.doc, fileName:=strFileName, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False

I am no VBA expert but it looks like you've got a return after ActiveSheet (splitting the object/method).

A shot in the dark!

 

[Chris_010101]

Also, here's an excerpt of some code which i use with a mailmerge to save letters to docx

'export word
ActiveDocument.SaveAs2 FileName:=docname & ".docx"
ActiveDocument.Saved = True

ActiveDocument.ActiveWindow.Close savechanges:=wdDoNotSaveChanges
Documents(MainDoc).Activate

Hope this helps!

 

[ipbr21054]

Thanks but thats the same.
I think its a bit more to it than that but await another reply.

Simple job & been trying for 5 hours with no joy