Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Mar 2004
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    html to .xls (Excel 2000)

    I need help. I can open a specific html file but I need a macro to open all .htm's in a directory and save them as .xls. I need them as excel documents and html's.

    Thanks in advance.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: html to .xls (Excel 2000)

    Here is such a macro:

    Sub HTM2XLS()
    ' Path - adapt as needed and note the trailing backslash
    Const strFolder As String = "C:Excel"

    ' Variables
    Dim strFile As String
    Dim wbk As Workbook

    On Error GoTo ErrHandler

    strFile = Dir(strFolder & "*.htm") ' See note
    ' Loop through folder
    Do While Not strFile = ""
    ' Open HTM file
    Set wbk = Workbooks.Open(Filename:=strFolder & strFile)
    ' Save as XLS
    wbk.SaveAs Filename:=strFolder & Left(strFile, Len(strFile) - 3) & "xls", _
    FileFormat:=xlWorkbookNormal, AddToMRU:=False ' See note
    ' Close without saving anew
    wbk.Close SaveChanges:=False
    ' And on to the next one
    strFile = Dir
    Loop

    ExitHandler:
    ' Release object memory
    Set wbk = Nothing
    Exit Sub

    ErrHandler:
    ' Inform user
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    End Sub

    Note: This code handles files with extension .HTM; if you have .HTML files, replace "*.htm" with ".html" and Len(strFile) - 3 by Len(strFile) - 4.

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: html to .xls (Excel 2000)

    But your code is more user-friendly, and it will work with various extensions (.htm, .html, .mht, ...)

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: html to .xls (Excel 2000)

    You mean my untested tweaks to Andrew's code ... <img src=/S/grin.gif border=0 alt=grin width=15 height=15>.
    -John ... I float in liquid gardens
    UTC -7ąDS

  5. #5
    New Lounger
    Join Date
    Mar 2004
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: html to .xls (Excel 2000)

    Thank you Hans, your code worked like a charm. Sorry it took me longer to look back for an answer than it took you to post it. John, I'll try your code later, thank you also.

    Tom

  6. #6
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: html to .xls (Excel 2000)

    <P ID="edit" class=small>(Edited by JohnBF on 25-Mar-04 18:01. Partly tested.)</P>You are just too darned fast. To anyone else, there may be bugs, and thanks to Andrew whose code is plagiarized.

    Sub SaveHTMasExcel()
    Application.ScreenUpdating = False
    Dim oFSearch As FileSearch
    Dim wbkFile As Workbook
    Dim strPath As String
    Dim lngC As Long

    strPath = GetFolderDlg()
    If Not CBool(Len(Dir(strPath, vbDirectory))) Then Exit Sub
    Set oFSearch = Application.FileSearch
    With oFSearch
    .NewSearch
    .LookIn = strPath
    .FileType = msoFileTypeWebPages
    If .Execute Then
    For lngC = 1 To .FoundFiles.Count
    Application.DisplayAlerts = False
    Set wbkFile = Workbooks.Open(Filename:=.FoundFiles(lngC))
    wbkFile.SaveAs FileFormat:=xlWorkbookNormal
    wbkFile.Close
    Application.DisplayAlerts = True
    Next
    End If
    End With
    Application.ScreenUpdating = True
    End Sub

    Public Function GetFolderDlg(Optional RootFolder As Variant, Optional Title As String = "Select a Folder") As String
    On Error Resume Next
    GetFolderDlg = Replace(CreateObject("Shell.Application").BrowseFo rFolder(0, Title, 0, RootFolder).Items.Item.Path & "", "", "")
    End Function

    Edit. Improved code, but I have found that if the source file has an 'html' extension it is saved with no extension. Also, to my annoyance, some of my test file names were along the lines of "prospect, inc. ~ url.com.htm" and the file is saved as "prospect, inc. ~ url.com", an unwanted result.
    -John ... I float in liquid gardens
    UTC -7ąDS

  7. #7
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: html to .xls (Excel 2000)

    My post was edited with improvements and also some warnings.
    -John ... I float in liquid gardens
    UTC -7ąDS

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •