Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 08-05-2019, 09:10 AM
LBruce LBruce is offline Save As new file type Windows 10 Save As new file type Office 2013
Novice
Save As new file type
 
Join Date: Aug 2019
Posts: 9
LBruce is on a distinguished road
Default Save As new file type


I'm a VBA novice. I'm having trouble with SaveAs2. I need to open in Word HTM/HTML documents in a specific folder, then save them as .docx with the same filename in the same folder. I have usable code to open said files in Word, but I can't get the SaveAs feature to work. I've tried various different lines of code found online, but have encountered various errors. Any thoughts on the easiest method?

Thanks.
Reply With Quote
  #2  
Old 08-05-2019, 11:23 AM
d4okeefe d4okeefe is offline Save As new file type Windows 10 Save As new file type Office 2016
Advanced Beginner
 
Join Date: Apr 2013
Posts: 77
d4okeefe is on a distinguished road
Default

You want to save an html as as word document?
I did this conversion with the following:

Code:
Sub saveHtmlAsDocx()
    Dim d As Document
    Set d = Documents.Open("C:\scratch\test.html")
    
    Dim newfile As String
    newfile = Replace(d, ".html", ".docx")
    d.SaveAs2 newfile, FileFormat:=wdFormatDocumentDefault
End Sub
Reply With Quote
  #3  
Old 08-05-2019, 11:53 AM
LBruce LBruce is offline Save As new file type Windows 10 Save As new file type Office 2013
Novice
Save As new file type
 
Join Date: Aug 2019
Posts: 9
LBruce is on a distinguished road
Default

Thanks, but it looks like I may not have been perfectly clear.

I need a macro to open every HTM or HTML file in a specific folder and convert them all, saved in the same folder with the original file name. I have part of the VBA enough to open and close the files throughout the directory (copied from a macro from another process of mine), but am missing the steps to save a copy as a Word doc.

The "Call to SaveAsDocx" part is what I can't figure out.

Code:
Sub ConvertHTMLtoDOC()
Dim file
Dim path As String

' Path to your folder. Make SURE you include the terminating "\"
'YOU MUST EDIT THIS.
path = "\\MYPATH"

'Change this file extension to the file you are opening. .htm is listed below.
'YOU MAY NEED TO EDIT THIS.
file = Dir(path & "*.htm*")
Do While file <> ""
Documents.Open FileName:=path & file

' This is the call to the macro you want to run on each file the folder
Call SaveAsDocx

ActiveDocument.Close

' set file to next in Dir
file = Dir()
Loop
End Sub
Reply With Quote
  #4  
Old 08-05-2019, 12:15 PM
d4okeefe d4okeefe is offline Save As new file type Windows 10 Save As new file type Office 2016
Advanced Beginner
 
Join Date: Apr 2013
Posts: 77
d4okeefe is on a distinguished road
Default

How about something like this? I think you need to pass the document to the SaveAsDoc function.

Code:
Sub SaveAsDoc(d As Document)
    Dim newfile As String
    If InStrRev(d.Name, ".html") > 0 Then
        newfile = Replace(d, ".html", ".doc")
    ElseIf InStrRev(d.Name, ".htm") > 0 Then
        newfile = Replace(d, ".htm", ".doc")
    End If
    d.SaveAs2 newfile, FileFormat:=wdFormatDocument97
End Sub
Sub ConvertHTMLtoDOC()
    Dim file
    Dim path As String
    path = "C:\scratch\"
    
    file = Dir(path & "*.htm*")
    Do While file <> ""
        Dim d As Document
        Set d = Documents.Open(file)
        
        Call SaveAsDoc(d)
        d.Close
        file = Dir()
    Loop
End Sub
Reply With Quote
  #5  
Old 08-05-2019, 12:40 PM
LBruce LBruce is offline Save As new file type Windows 10 Save As new file type Office 2013
Novice
Save As new file type
 
Join Date: Aug 2019
Posts: 9
LBruce is on a distinguished road
Default

I'm getting an error on this.

"Run-time error '5174: Sorry, we couldn't find your file. Is it possible it was moved, renamed, or deleted?" Then it lists the first file in the folder.

Debug highlights the "Set d = Documents.Open (file)" line.
Reply With Quote
  #6  
Old 08-05-2019, 01:15 PM
d4okeefe d4okeefe is offline Save As new file type Windows 10 Save As new file type Office 2016
Advanced Beginner
 
Join Date: Apr 2013
Posts: 77
d4okeefe is on a distinguished road
Default

Maybe pass the entire path to the Open function:
Set d = Documents.Open(path & file)
Reply With Quote
  #7  
Old 08-05-2019, 01:23 PM
LBruce LBruce is offline Save As new file type Windows 10 Save As new file type Office 2013
Novice
Save As new file type
 
Join Date: Aug 2019
Posts: 9
LBruce is on a distinguished road
Default

Word clocks for a while like it's trying to do something, but there are no results.

I appreciate your help so far!
Reply With Quote
  #8  
Old 08-05-2019, 01:29 PM
d4okeefe d4okeefe is offline Save As new file type Windows 10 Save As new file type Office 2016
Advanced Beginner
 
Join Date: Apr 2013
Posts: 77
d4okeefe is on a distinguished road
Default

You may be missing a backslash between the directory name and the filename.
You could try this instead, but I'm not sure:
Set d = Documents.Open(path & "\" & file)

It could be something else though. Do you know how to use the Immediate Window. If you place this line of code...

Code:
Debug.Print path & "\" & file
...on the line before Documents.Open(), you will see the full path logged. Then you could double check the filename.

Btw, how many files are you trying to convert. If it's in the 100s, it could just be taking a long time to save them all.
Reply With Quote
  #9  
Old 08-06-2019, 12:36 AM
gmayor's Avatar
gmayor gmayor is offline Save As new file type Windows 10 Save As new file type Office 2016
Expert
 
Join Date: Aug 2014
Posts: 4,101
gmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud of
Default

The issue is primarily related to the filename. What you need is

Code:
Sub ConvertHTMLtoDOC()
Dim strFile As String
Dim strPath As String
Dim strDocName As String
Dim oDoc As Document
    strPath = "C:\scratch\"
    strFile = Dir(strPath & "*.htm*")
    Do While strFile <> ""
        Set oDoc = Documents.Open(strPath & strFile) 'This line needs to include the path
        strDocName = Left(oDoc.FullName, InStrRev(oDoc.FullName, ".") - 1) & ".docx"
        oDoc.SaveAs2 FileName:=strDocName, FileFormat:=wdFormatDocumentDefault
        oDoc.Close
        strFile = Dir()
    Loop
    Set oDoc = Nothing
End Sub
__________________
Graham Mayor - MS MVP (Word) (2002-2019)
Visit my web site for more programming tips and ready made processes www.gmayor.com
Reply With Quote
  #10  
Old 08-06-2019, 11:38 AM
LBruce LBruce is offline Save As new file type Windows 10 Save As new file type Office 2013
Novice
Save As new file type
 
Join Date: Aug 2019
Posts: 9
LBruce is on a distinguished road
Default

That worked perfectly, gmayor. Thank you both!
Reply With Quote
  #11  
Old 09-27-2019, 01:31 PM
LBruce LBruce is offline Save As new file type Windows 10 Save As new file type Office 2013
Novice
Save As new file type
 
Join Date: Aug 2019
Posts: 9
LBruce is on a distinguished road
Default

Hello!

Sometimes this runs in alphabetical order, sometimes randomly. For certain reasons, I do need the code to pull files from the folder in alphabetical order. Is this possible?
Reply With Quote
  #12  
Old 09-27-2019, 08:36 PM
gmayor's Avatar
gmayor gmayor is offline Save As new file type Windows 10 Save As new file type Office 2016
Expert
 
Join Date: Aug 2014
Posts: 4,101
gmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud of
Default

This relates to the Windows filing system, however it is possible by collecting the filenames, sorting them and then processing the sorted list e.g.

Code:
Sub ConvertHTMLtoDOC()
'Graham Mayor - https://www.gmayor.com - Last updated - 28 Sep 2019
Dim strFile As String
Dim strPath As String
Dim strDocName As String
Dim oDoc As Document
Dim oColl As Collection
Dim arr() As Variant
Dim i As Integer
    Set oColl = New Collection
    strPath = "C:\path\"
    strFile = Dir(strPath & "*.htm*")
    Do While strFile <> ""
        oColl.Add strPath & strFile
        strFile = Dir()
    Loop
    arr = toArray(oColl)
    For i = LBound(arr) To UBound(arr)
        Set oDoc = Documents.Open(arr(i))
        strDocName = Left(oDoc.FullName, InStrRev(oDoc.FullName, ".") - 1) & ".docx"
        oDoc.SaveAs2 FileName:=strDocName, FileFormat:=wdFormatDocumentDefault
        oDoc.Close
    Next i
    Set oDoc = Nothing
End Sub

Private Function toArray(ByVal Coll As Collection) As Variant
Dim arr() As Variant
Dim i As Long
    ReDim arr(1 To Coll.Count) As Variant
    For i = 1 To Coll.Count
        arr(i) = Coll(i)
    Next
    toArray = arr
    QuickSort arr
lbl_Exit:
    Exit Function
End Function

Private Sub QuickSort(vArray As Variant, _
                      Optional lng_Low As Long, _
                      Optional lng_High As Long)
Dim vPivot As Variant
Dim vTmp_Swap As Variant
Dim tmp_Low As Long
Dim tmp_High As Long

    If lng_High = 0 Then
        lng_Low = LBound(vArray)
        lng_High = UBound(vArray)
    End If

    tmp_Low = lng_Low
    tmp_High = lng_High
    vPivot = vArray((lng_Low + lng_High) \ 2)
    While (tmp_Low <= tmp_High)
        While (vArray(tmp_Low) < vPivot And tmp_Low < lng_High)
            tmp_Low = tmp_Low + 1
        Wend
        While (vPivot < vArray(tmp_High) And tmp_High > lng_Low)
            tmp_High = tmp_High - 1
        Wend
        If (tmp_Low <= tmp_High) Then
            vTmp_Swap = vArray(tmp_Low)
            vArray(tmp_Low) = vArray(tmp_High)
            vArray(tmp_High) = vTmp_Swap
            tmp_Low = tmp_Low + 1
            tmp_High = tmp_High - 1
        End If
    Wend
    If (lng_Low < tmp_High) Then QuickSort vArray, lng_Low, tmp_High
    If (tmp_Low < lng_High) Then QuickSort vArray, tmp_Low, lng_High
lbl_Exit:
    Exit Sub
End Sub
__________________
Graham Mayor - MS MVP (Word) (2002-2019)
Visit my web site for more programming tips and ready made processes www.gmayor.com
Reply With Quote
  #13  
Old 09-30-2019, 06:31 AM
LBruce LBruce is offline Save As new file type Windows 10 Save As new file type Office 2013
Novice
Save As new file type
 
Join Date: Aug 2019
Posts: 9
LBruce is on a distinguished road
Default

Thanks, but this still opens the files in a random order.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Save As new file type save dialog box promt doesn't save file brmveen Word VBA 2 11-04-2015 12:51 AM
Save As new file type Word 2007 , when I save a .doc or .docx file the file type is showing "Empty" Tomc29 Word 9 06-10-2015 03:04 AM
Set File Type when clicking Save As Madbags Word VBA 1 10-10-2014 01:07 PM
Save As new file type Word file type in 2007 casaserves Word 4 08-03-2013 01:11 AM
Getting COMException Incompatible file type and file extension sbalerao Mail Merge 0 04-21-2011 10:30 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 06:41 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft