Excel vba to save as word doc

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
5,832
Office Version
  1. 2007
Platform
  1. Windows
Please can you advise how i save a file as .doc

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

Rich (BB code):
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
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
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
 
Upvote 0
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
 
Upvote 0
Can you advise please the line of code i need to use to save as Word .doc
 
Upvote 0
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!
 
Upvote 0
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!
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,177
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top