Opening html files in excel with a macro

[bball88555]

I am trying to open html files with a macro from excel. Currently, I am opening the file with word.application and this is causing the word file converter to pop up each time I open a file. I am opening numerouse files so this is not wanted. I am wondering if anyone know how to do this different or if you know how to code in what the file converting is doing.

Thanks for your help

 

[TonyJollans]

Hi bball88555,

What do you want to do with theses files when you have them open. View them? Edit them? Extract content from them?

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[bball88555]

I open the first file, do a saveas and rename it, just to create a copy. Then I open the original file and clear some of the contents then copy and paste a selection from one of the excel worksheets. This sequence is repeated numerous times for different groups. I hope this helps

 

[bball88555]

Just a little clarification. The code executes the open command and opens the html file then it prompts me to select how the file should be opened Plain text, RTF, HTML, etc. then does the SaveAs and closes the file. Then when the code opens the file again it prompts for the same question and needs me to select again. So for each group that I do this for it is prompting me twice, and it is running through about 20 groups now so that is a pain.

Thanks

 

[TonyJollans]

Hi bball88555,

Well, just to take a copy you could use the CopyFile statement - no need to open and then SaveAs.

Not sure what to suggest for the second part. I like to edit HTML in Notepad (I hate what Word does to it), but that may not be the best for what you're doing. Is your editing complex? Does it need Word?

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[bball88555]

It doesn't need word, I just used it because I thought it was the easiest way around. The data that I have is in one column on my excel sheet and the only thing that I am doing is copying that data over and pasting it in the open file. If I can figure a way to use notepad for this I could do that as well. If I use the open method and open a the HTML file that way and not in word how do I edit and paste to it.

 

[TonyJollans]

Hi bball,

To be honest I don't know how easily I can help with this because I don't know what HTML will be generated when you copy from Excel to html in Word.

I may be able to help you get rid of the prompts though. Can you post your code?


Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[bball88555]

Sub CopyWord(ByVal NTGroupName As String)

ThisWorkbook.Sheets("Sheet2").Range("A1:A200").Copy

Set wdApp = New Word.Application

With wdApp

.Documents.Open Filename:="C:\My Documents\" & NTGroupName & "\index.html"

.ActiveDocument.SaveAs Filename:="C:My Documents\" & NTGroupName & "\indexOld.html", FileFormat:=wdFormatText, _
LockComments:=False, Password:="", AddToRecentFiles:=True, WritePassword _
:="", ReadOnlyRecommended:=False, EmbedTrueTypeFonts:=False, _
SaveNativePictureFormat:=True, SaveFormsData:=True, SaveAsAOCELetter:= _
False


.ActiveDocument.Close
' close current file

.Documents.Open Filename:="C:\My Documents\" & NTGroupName & "\index.html" ' open original file

With .Selection
.WholeStory 'select all
.Delete Unit:=wdCharacter, Count:=1 'Delete all
.TypeParagraph
.Paste
End With
.ActiveDocument.Save
'Exit Word
.Quit
End With
'Release object variable
Set wdApp = Nothing
End Sub

 

[bball88555]

Also, the information that I am copying is already in html format. I have all the <tags> already coded in so it is a direct copy and paste and no converion. I just need to save it as html not convert to html. I don't know if that will help you at all.

 

[bball88555]

do you know how to use the sendkeys function with document.open. If there is a way to do this then when I open the file and the prompt comes up it will send the keystrokes to the prompt and continue.

I don't know if this is possible. I have tried putting the code SendKeys "^{HOME}{ENTER}", which is what needs to be done to make the conversion, before, after and in the same line as the document.open statement.

 

[TonyJollans]

Hi bball88555,

Sorry I just disappeared last night. I wasn't very well and I went to bed.

Does this help at all? I have changed the open/saveas to a copy and added a format to the open - I don't know what format you want so I took a random one. I usually prefer to avoid SendKeys if I can.

[blue]Sub CopyWord(ByVal NTGroupName As String)
   
  ThisWorkbook.Sheets("Sheet2").Range("A1:A200").Copy
  
   FileCopy Source:="C:\My Documents\" & NTGroupName & "\index.html", _
            Destination:="C:My Documents\" & NTGroupName & "\indexOld.html"

   Set wdApp = New Word.Application
   
   With wdApp
              
      .Documents.Open Filename:="C:\My Documents\" & NTGroupName & "\index.html", _
                      Format:=wdOpenFormatWebPages ' open original file
      [green]' could be wdOpenFormatRTF, or wdOpenFormatText, or maybe just wdOpenFormatAuto[/green]
      
      With .Selection
         .WholeStory 'select all
         .Delete Unit:=wdCharacter, Count:=1 'Delete all
         .TypeParagraph
         .Paste
      End With
      .ActiveDocument.Save
      'Exit Word
      .Quit
   End With
   'Release object variable
   Set wdApp = Nothing
End Sub[/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[bball88555]

Let me try that. It looks great. Here is what I did in the mean time that added another step but it got rid of the prompt. I first copied the original file to the old file and then opened a regular word doc pasted the info in there and then saved it as an html page. However, I think your way might be better. Thanks


Sub CopyWord(ByVal NTGroupName As String)

' copies the information that is going on the index page
ThisWorkbook.Sheets("Sheet2").Range("A1:A200").Copy

Set wdApp = New Word.Application ' allows access to MS Word

With wdApp

Dim sSourcePath,sTargetPath As String

sSourcePath = "C:\Documents and Settings\cr953c\My Documents\automate\" & NTGroupName & "\index.html"
sTargetPath = "C:\Documents and Settings\cr953c\My Documents\automate\" & NTGroupName & "\indexOld.html"


FileCopy sSourcePath, sTargetPath


.Documents.Open Filename:="C:\Documents and Settings\cr953c\My Documents\automate\" & NTGroupName & "\index.doc"

With .Selection
.WholeStory 'select all
.Delete Unit:=wdCharacter, Count:=1 'Delete all
.TypeParagraph
.Paste 'paste the selection that was copied
End With
.ActiveDocument.Save
.ActiveDocument.SaveAs Filename:="C:\Documents and Settings\cr953c\My Documents\automate\" & NTGroupName & "\index.html", FileFormat:=wdFormatText

'Exit Word
.Quit

End With
'Release object variable
Set wdApp = Nothing
End Sub

 

[bball88555]

That works like a charm. Thanks for your help. That open format was what I needed. I tried using the FileFormat:=wdFormatText instead of the one you supplied for me.

Thanks again

Josh

 

[TonyJollans]

You're welcome.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[