Copy column data from worksheet to .htm file (notepad) using VBA

[johnnywinto]

Hello, its my first time here.

I am really new to VBA and only know some basics but Im looking for something a bit more complex. I have built an excel database which creates web pages by using formulas to pull in data that changes page per page. The generated HTML is in a worksheet called 'P1 - Output' in a single column from cell range A1:A334. The data is concatenated content results.

If its at all possible I would like a VBA script which copies and pastes the text results in 'P1 - Output' (range A1:A334) into a .htm file. Again if its at all possible it would be brilliant if the file could be saved based on the file name required which is located in a worksheet called 'P Data' in cell B1.

What happens in the database is if I change the cell reference in P Data it creates a new script, so all I need is a mechanism to output the html code into the htm file.

Please view the screenshots which may give a clearer picture:

P1 - Output

P Data

If anyone can help I would be eternally grateful.

Kind regards

John Winterton

 

[johnnywinto]

I think Im doing something wrong. See Script below:

Sub output_htm()
'
' output_htm Macro
'
Application.ScreenUpdating = False
Application.EnableEvents = False

Dim rs As Worksheet
Set rs = Worksheets("P Data")

For f = 1 To 200
Data = ""

For r = 1 To 334
Data = Data & Cells(r, "A") & vbCr
Next r

myFile = "C:\Users\WintertonJ\Desktop\SAM Website 2021\Excel Files\htm-test\" & rs.[B1]
If Dir(myFile) <> "" Then Kill myFile ' deletes file if it exists

Open myFile For Append As #1
Print #1, Data
Close #1

rs.Range("B1:B119").Replace What:=f, Replacement:=f + 1, LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Next f 'next file

Application.ScreenUpdating = True
Application.EnableEvents = True

MsgBox "Finished"
'
End Sub

I run the macro and it just comes up with this popup and if I click to close it just reopens:

The only way I can get out is to CTRL+ALT+DEL.

Not sure whats wrong now? The only thing thats changed is that I've increased the range to B119.

regards

John W

 

[rpaulson]

can you upload your file to drop box or other file sharing site??

 

[johnnywinto]

Unfortunately it didn't work. It just comes up with box asking me to save and hangs if I try to cancel.

Could I just have a find and replace script only which will work on column B so I can do one file at a time? That would be fab if poss. Tbh it works better that way for me I think looking at the code.

 

[rpaulson]

John,
You will need a cell to save the current number you are on. lets put it in cell C1 on P Data sheet

put a 1 in the C1

here is the code. (make sure you are on the P data sheet

put a shape on the sheet and attach he macro to it.
everytime you click on the shape it pill advance all the numbers in the rane by on1.

Sub next_file()
Range("B1:B119").Replace What:=[C1], Replacement:=[C1] + 1, LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
[C1] = [C1] + 1
End Sub

try it on a new sheet to check it out.

small sample is on this file http://www.computermaninc.net/Book1.xlsm
-ross

 

[johnnywinto]

Yes that works fine - thank you very much

regards

Johnny Winto