Multiple Convert of files from HTML to xlsx within folder - VBA

[Krist]

Hi,

I have racked my (limited) excel brain and cannot find a suitable way to do the following:

Convert all files within a folder from HTML (downloaded from browser) to xlsx format.

I wish the following considerations to be adhered to:

1. Files will always be within the same folder on my computer (Macintosh HD:Users:Krist:Documents:R7M:Planning:Locations
2. Files should be saved individually as per the name of each HTML file (C09 in html format should be renamed C09 in .xlsx)
3. The number of files to be converted will change on a daily basis so will have to be dynamic

I think this below is on the right track however I don't know how to make it convert all the files... Also, this is for windows, the mac file path will be as I mentioned above.


<code style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, serif; white-space: inherit;">Sub Open_HTML_Save_XLSX() Workbooks.Open Filename:="C:\Temp\Example.html" ActiveWorkbook.SaveAs Filename:= _ "C:\Temp\Example.xlsx", FileFormat:= _ xlOpenXMLWorkbookEnd Sub</code></pre>
Any help or ideas would be more than welcome, trying to teach yourself vba isn't the easiest without help from more experienced people.

I am using excel 2011 - mac OSX (compatible with windows)

Look forward to hearing from you!

Krist

 

[Andrew Poulsom]

Welcome to MrExcel.

Maybe you'll find what you need here:

Working with Files and Folders (Mac)

 

[Krist]

I had a look at the sites and nothing seems to quite fit. If i'm truly honest when I go into the developer and look at the code, it's a little like looking at the matrix screens to me!

I did come across an interesting page on this forum regarding the conversion of .txt files to .xlsx files as below, and apparently it works fine.

[COLOR=#333333]Sub CommandButton1_Click()[/COLOR]
 Dim MyFolder As String Dim myfile As String Dim folderName As String With Application.FileDialog(msoFileDialogFolderPicker) .AllowMultiSelect = False If .Show = -1 Then folderName = .SelectedItems(1) End If End With myfile = Dir(folderName & "\*.txt") Do While myfile <> "" Workbooks.OpenText Filename:=folderName & "\" & myfile'save as excel file ActiveWorkbook.SaveAs Filename:=folderName & "\" & Replace(myfile, ".txt", ".xls")'use below 3 lines if you want to close the workbook right after saving, so you dont have a lots of workbooks opened Application.DisplayAlerts = False ActiveWorkbook.Close Application.DisplayAlerts = True myfile = Dir Loop </pre>[COLOR=#333333]End Sub[/COLOR]

I would have to change the '\' to ':' to fit in with the mac file names. However when I did this and tried to run it, I got the error with the line "With Application.FileDialog(msoFileDialogFolderPicker).AllowMultiSelect = False" it returned 0.

Would there be a simple way to convert the code to open .html files instead of .txt files and save it as excel files.

The link to the code I found is here:
http://www.mrexcel.com/forum/excel-questions/743829-visual-basic-applications-open-all-text-files-excel-folder-save-them-excel-file.html

Thanks,

Krist

 

[Andrew Poulsom]

This shows you how to loop through files in a folder in Mac Excel:

Loop through Files in Folder on a Mac (Dir for Mac Excel)