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

 

[rpaulson]

try this.

Sub Create_File()
myfile = "c:\test\" & worksheets("P DAta").[B1]
If Dir(myfile) <> "" Then Kill myfile ' deletes file if it exists
data=""

For r = 1 To 334
    data = data & Cells(r,"A") & vbcr
Next r

Open myfile For Append As #1
Print #1, data
Close #1
End Sub

 

[johnnywinto]

Hi RPaulson

Genius!

Firstly, would like to say a massive thanks. I've changed the file path in the script to the location I want to store the files and it works absolutely perfectly!

Could I be cheeky and ask a small added question? The way the website pages are created are from the file P Data where all the fields that change are located in the worksheet in column B (see image screenshot below).

To generate the P1 Output all I do is a find and replace to change the cell reference in the formula. The number in the cell reference for all items in Column B are the same (in this case 14). So in the example screenshot above, I would want to change all the formulas in Column B of P Data from 14 to 15 and this will create the next page for output. There will be 200 web pages. Is there a way in VBA where Column B can change the number in the formula to the next one i.e if I start at 1, then find and replace to 2, find and replace to 3 and so on?

If its possible, I could tie in the two script to launch from a single click which would save me masses of time. So in essence, change the cell references in column B by 1 and then output the script to the .htm file.

Any help would be appreciated.

Best Wishes

Johnny WInto

 

[rpaulson]

im sure we can do your second request, but im nut sure exactly what you are trying to do??

 

[johnnywinto]

Thanks for the reply. I'll try and explain better.

Im creating 200 web pages from an excel database. The worksheet 'P Data' Column B basically lists all the page elements that change (since the majority of a web script doesn't).

What I currently do manually to load the next page is the following (I include screenshots to explain - note that these are truncated):

• Select entire Column B
• Find and replace (CTRL+H) and in this example, find 6 and replace with 7 and replace all. See screenshot below:

When I click replace all, the data changes to this:

You can see that by changing the formula reference by 1 that it loads the next set of web page changes. Note that the screenshot takes a truncated view (if it needs to be done by a cell range it is currently B1:B115. this automatically changes the P1 - Output worksheet (see screenshot below):

So by find and replacing, it has changed the web page output for webpage 2014A aluminium to 2618A aluminium. Cycling through from 1 to 200 will create all 200 web pages.

The utopian solution for the is would be as follows all performed in a single click:

- The starting point will be the first web page which in P Data the cell reference will be 1
- The macro button would be located on the page P Data.
- Run the output script you've current provided on P1 (since the P Data content is already set) - Output, then go back to P Data and change all references in Column B form 1 to 2
- Run the output script again to create the second page and then repeat the process changing Column B from 2 to 3
- Repeat this process 200 times
- Website is built!

All I would need to do is ensure the database I've created is tested and complete.

I hope this all makes sense. Even if it is not possible, the script you've already provided will save me a huge amount of time.

Many thanks

JohnnyWinto

 

[rpaulson]

try this on a copy of you data.

i'm pretty sure because of the replace if will replace all the filename to something you are not wanting.

example if currently file is "2024 Aluminum" it will change to

3034 then
4044 then
5055 ...

eventailly the the 50 will go to
5155...
.....

good luck.

Sub Create_Files()
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:\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").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

-Ross

 

[johnnywinto]

Unfortunately it doesn't work but thanks for trying - Im going to stick with it and see if I can find a way to do it.

Thanks for all your help.

regards

John W

 

[johnnywinto]

Just a thought - how easy would it be just to use VBA to peform a find and replace in Column B of P Data by 1 step increments each time its executed? Or over cell range B1: B115. I think if it could do this I can do the rest? (ie. arrange for the final code to be outputted with the macro previously written).

 

[rpaulson]

John,

I just realized that code is incorrect, as it only did a find and replace in cell B1 (the Filename).

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

should be
rs.Range("B:B").Replace What:=f, Replacement:=f + 1, LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

or
rs.Range("B1:B115").Replace What:=f, Replacement:=f + 1, LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

-Ross

 

[johnnywinto]

I'll give it another go. it didn't have an issue creating the different file names but the files read like this:

Instead of 2618a-aluminiu.htm

it read as...

output2618a-aluminium.htm

Not sure if what you ahve written fixes that?

regards

JohnW