VBA/Macro to create lists in notepad

[cgreene87]

Hi all,

I was wondering if the following query is even possible, I'm not certain.

I need to take the numbers on a spreadsheet in column A and generate a notepad file for each of the (price points column B) with the all corresponding numbers per price point saved into a notepad file (.txt) (one notepad file for all the numbers at 2.99, one notepad file for all the numbers at 3.99 etc). I've been copying and pasting these numbers into notepad but it is a laborious task and leaves room for error.

Many thanks if you are able to assist.

Best,

Craig

 

[DocAElstein]

Hi Doc, Lewis & Snb,

Many thanks for your assistance. The file and some examples of what the outputS should look like are linked and shared below additionally you will find a copy of my script qouted. The script is almost working currently except it cuts off the last price point (in this case 179.99). I basically need the numbers on the left to all be put into a text file under their corresponding price points (the right column).

Thanks again!

https://app.box.com/s/73sp1hye1rrhzyvra5a1

Hi,
That download came out by me as just a text file with 2 numbers in it?

( PP Output2.99.txt )

looked like this:




130144328
130296575


When I use Box or similar file sharing things I get a link for every file I upload there. It is much easier if we can see simply how your data looks in the Excel file and a file (text in your case) showing how it should look based on that data. The data should be shortened to make it easier to see, but should include the different variations of data types to demonstrate visually wot you are explaining as well. If you have not mastered the MrExcel HTML Maker jet I can post back some screen shots using it based on the excel file you send for the benefit of others trying to help or follow the thread.

Alan

 

[cgreene87]

Hi Doc,

You are correct that is all the file needs in it. Apologies I placed the excel file in the link but thought all were shared. It is now accessible via the link below.

https://app.box.com/s/429m9ugdo6pfb1w8h6w0

 

[DocAElstein]

Hi Doc,

You are correct that is all the file needs in it..........

. OK
. I see now. I was a bit slow at catching up.. You actually gave all the Excel info in Post #4. I am just used to using the MrExcel Screen shot stuff in this forum which makes it look like an Excel file.
. So for the benefit of others here is an extract from your Excel File…

Book1
	A	B	C
1	Item #	Price Point
2	130144328	2.99
3	130296575	2.99
4	130144361	3.99
5	130280901	3.99
6	130280928	3.99
7	130297455	3.99
8	130297703	3.99
9	130439142	5.99
10	130476736	5.99
cgreene87

. And you want lots of Text files generated from it.

. The First Text file would have the name PP Output2.99.txt and look like this:

130144328
130296575

. The next would have the name PP Output3.99.txt and look like

130144361
130280901
130280928
130297455
130297703

. etc etc….

. Your very first Post # 1 actually described it perfectly….

“I need to take the numbers on a spreadsheet in column A and generate a notepad file for each of the (price points column B) with the all corresponding numbers per price point saved into a notepad file (.txt) (one notepad file for all the numbers at 2.99, one notepad file for all the numbers at 3.99 etc). I've been copying and pasting these numbers into notepad but it is a laborious task and leaves room for error.“......

. but with the screen shots makes it obvious even to me!

Alan.

 

[cgreene87]

Hi Alan,

Exactly, it is extremely simple, just seems the current script I am running missings the last price point 179.99 and I am not sure why. Many thanks for all your attention

 

[DocAElstein]

Hi Alan,

Exactly, it is extremely simple, just seems the current script I am running missings the last price point 179.99 and I am not sure why. Many thanks for all your attention

. Hi I just finished writing a code for you as I caught your message. It is another different method to consider. I need to tidy it up a bit then I will post it in the Thread tomorrow.

. As to your problem I think I might see the problem. I had something similar. You are, as I was comparing the next row for a different value, then putting appropriate values in. I had somehow got Out of step, putting the wrong values in which meant I was putting for example the last value in the last but one text file. So the last text file got an empty cell value – that of the cell just after the end of the file. Check that the actual values in the text files are correct. But I will look more carefully again tomorrow at your code.
. catch you again sometime tomorrow.
Alan

 

[DocAElstein]

. Hi
. Here is a simple VBA text writing code for you which does the job. Tested it a few times, works in my XL 2007. Personally I would prefer the Scripting type from Lewis and snb_ . Unfortunately after some effort I could not get them to work. Probably lies in my ignorance in applying them correctly. I would be very grateful if they have the time to take another look and apply their codes to your example.
. Anyways I give the code, simplified first, then given in a bit more detail with explaining green comments.

. Here we are

Simple “Monochrome”
 
Sub PrintDataToTextFileSimple()
 
    Let LastPPriceRow = 1
    Let LastFileRow = Cells.Find(What:="*", After:=Cells(1, 1), Lookat:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
 
    Let TextFilesFolder = "\TextFileFolder\cgreene87TextFiles"
    Let TextFilesFolderPath = ThisWorkbook.Path & TextFilesFolder
    ThisWorkbook.Worksheets("Markdown list Mid season").Activate
   
      For FileRow = 2 To LastFileRow Step 1
        If Cells(FileRow, 2).Value <> Cells(FileRow + 1, 2).Value Then
          Let Price = Cells(FileRow, 2).Value
          Open TextFilesFolderPath & "\PP Output" & Price & ".txt" For Output As 1
            For PPriceRow = 1 To LastPPriceRow
              Print #1, Cells(FileRow - LastPPriceRow + PPriceRow, 1).Text
            Next PPriceRow
          Close 1
          Let LastPPriceRow = 1
        Else
          Let LastPPriceRow = LastPPriceRow + 1
        End If
      Next FileRow
 
End Sub

……

In Detail “Multicolored”!

[color=green]'Option Explicit[/color]
 
[color=darkblue]Sub[/color] PrintDataToTextFile()
 
    [color=darkblue]On[/color] [color=darkblue]Error[/color] [color=darkblue]GoTo[/color] TheEnd [color=green]'If anything goes wrong do not crash go to TheEnd.[/color]
    [color=darkblue]Dim[/color] LastFileRow [color=darkblue]As[/color] [color=darkblue]Long[/color], FileRow [color=darkblue]As[/color] [color=darkblue]Long[/color], PPriceRow [color=darkblue]As[/color] [color=darkblue]Long[/color], LastPPriceRow [color=darkblue]As[/color] [color=darkblue]Long[/color] [color=green]'File Row,Last Row,Row and last Row for Set prices[/color]
    [color=darkblue]Let[/color] LastPPriceRow = 1 [color=green]' Assume we have at least 1 price to start with![/color]
    [color=darkblue]Let[/color] LastFileRow = Cells.Find(What:="*", After:=Cells(1, 1), Lookat:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row [color=green]' work backwards from first cell(effectively start at last cell) to find end of Price and pick out last Row[/color]
    [color=darkblue]Dim[/color] Price [color=darkblue]As[/color] [color=darkblue]String[/color], TextFilesFolder [color=darkblue]As[/color] [color=darkblue]String[/color], TextFilesFolderPath [color=green]' Each Price, Text Files Folder and Path[/color]
    [color=darkblue]Let[/color] TextFilesFolder = "\TextFileFolder\cgreene87TextFiles" [color=green]'Chgange these two lines to suit..[/color]
    [color=darkblue]Let[/color] TextFilesFolderPath = ThisWorkbook.Path & TextFilesFolder [color=green]'..where you want your text Files to go-[/color]
    ThisWorkbook.Worksheets("Markdown list Mid season").Activate [color=green]'Activate relavent Price Sheet list in Your Excel File so that you are allways referencing there with calling up Cells etc.[/color]
     
      [color=darkblue]For[/color] FileRow = 2 [color=darkblue]To[/color] LastFileRow [color=darkblue]Step[/color] 1 [color=green]'go along whole file[/color]
        [color=darkblue]If[/color] Cells(FileRow, 2).Value <> Cells(FileRow + 1, 2).Value [color=darkblue]Then[/color] [color=green]'Check to see if next price is different[/color]
          [color=darkblue]Let[/color] Price = Cells(FileRow, 2).Value [color=green]'Set price for last same prices[/color]
          [color=darkblue]Open[/color] TextFilesFolderPath & "\PP Output" & Price & ".txt" [color=darkblue]For[/color] Output [color=darkblue]As[/color] 1 [color=green]'Open a Data "Highway" for taking the data string. Note: If no file is there it will be created[/color]
            [color=darkblue]For[/color] PPriceRow = 1 [color=darkblue]To[/color] LastPPriceRow [color=green]'For the number of similar prices[/color]
              [color=darkblue]Print[/color] #1, Cells(FileRow - LastPPriceRow + PPriceRow, 1).Text [color=green]'Print out the corresponding item number[/color]
            [color=darkblue]Next[/color] PPriceRow [color=green]'[/color]
          [color=darkblue]Close[/color] 1 'Allways close this "highway" when finished sending data or can get wiered problems.
          [color=darkblue]Let[/color] LastPPriceRow = 1 [color=green]'Reset the Row count for similar prices[/color]
        [color=darkblue]Else[/color] [color=green]'As the price and next price are similar we...[/color]
          [color=darkblue]Let[/color] LastPPriceRow = LastPPriceRow + 1 [color=green]'Add 1 to the count for similar prices[/color]
        [color=darkblue]End[/color] [color=darkblue]If[/color]
      [color=darkblue]Next[/color] FileRow [color=green]'Check the next File row to see if it still has the same Price[/color]
     
[color=darkblue]Exit[/color] [color=darkblue]Sub[/color] [color=green]'If no errors stop program[/color]
TheEnd: [color=green]'In the case of an error[/color]
    MsgBox (Err.Description) [color=green]'Give message box with error description[/color]
    [color=darkblue]Close[/color] 1 [color=green]'Important: If anything goes wrong make sure the data path is closedClose 1[/color]
[color=darkblue]End[/color] [color=darkblue]Sub[/color] [color=green]'PrintDataToTextFile()[/color]

. Let me know if you try it out and how yours is going.

Alan

P.s. Your file retuned saved as Price List.xlsm with the macros in it in the only module there, Module1
https://app.box.com/s/hkeq9rkt2tdj6hspxa1k

 

[cgreene87]

Hi Alan,

I made a few changes (the path of course and file name of output). I am trying to learn VBA, how can I make the script now reference any worksheet and not just the one specified? Many thanks again!

'Option Explicit
 
Sub PrintDataToTextFile()
 
    On Error GoTo TheEnd 'If anything goes wrong do not crash go to TheEnd.
    Dim LastFileRow As Long, FileRow As Long, PPriceRow As Long, LastPPriceRow As Long 'File Row,Last Row,Row and last Row for Set prices
    Let LastPPriceRow = 1 ' Assume we have at least 1 price to start with!
    Let LastFileRow = Cells.Find(What:="*", After:=Cells(1, 1), Lookat:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row ' work backwards from first cell(effectively start at last cell) to find end of Price and pick out last Row
    Dim Price As String, TextFilesFolder As String, TextFilesFolderPath ' Each Price, Text Files Folder and Path
    Let TextFilesFolder = "C:\Users\cgreene\Desktop" 'Chgange these two lines to suit..
    Let TextFilesFolderPath = "C:\Users\cgreene\Desktop" '..where you want your text Files to go-
    ThisWorkbook.Worksheets("Sheet 1").Activate 'Activate relavent Price Sheet list in Your Excel File so that you are allways referencing there with calling up Cells etc.
     
      For FileRow = 2 To LastFileRow Step 1 'go along whole file
        If Cells(FileRow, 2).Value <> Cells(FileRow + 1, 2).Value Then 'Check to see if next price is different
          Let Price = Cells(FileRow, 2).Value 'Set price for last same prices
          Open TextFilesFolderPath & "\" & Price & ".txt" For Output As 1 'Open a Data "Highway" for taking the data string. Note: If no file is there it will be created
            For PPriceRow = 1 To LastPPriceRow 'For the number of similar prices
              Print #1, Cells(FileRow - LastPPriceRow + PPriceRow, 1).Text 'Print out the corresponding item number
            Next PPriceRow '
          Close 1 'Allways close this "highway" when finished sending data or can get wiered problems.
          Let LastPPriceRow = 1 'Reset the Row count for similar prices
        Else 'As the price and next price are similar we...
          Let LastPPriceRow = LastPPriceRow + 1 'Add 1 to the count for similar prices
        End If
      Next FileRow 'Check the next File row to see if it still has the same Price
     
Exit Sub 'If no errors stop program
TheEnd: 'In the case of an error
    MsgBox (Err.Description) 'Give message box with error description
    Close 1 'Important: If anything goes wrong make sure the data path is closedClose 1
End Sub 'PrintDataToTextFile()

 

[DocAElstein]

Hi Alan,
………Many thanks again!

…..

Hi…You are welcome, I am learning by doing this sort of thing!

………………………………….

Hi Alan,

I made a few changes (the path of course and file name of output).

…..

. 1 ? I get confused easily as I am learning….You mean you changed the complete text File output Path, to qualify it in this one line
Let TextFilesFolderPath = "C:\Users\cgreene\Desktop"
Making this line redundant (no longer necessary)
Let TextFilesFolder = "C:\Users\cgreene\Desktop"
And changed the sheet name to sheet1.
Correct ?

…….

…. I am trying to learn VBA, how can I make the script now reference any worksheet and not just the one specified? Many thanks again!

…..

. 2 Do you really mean just select the Worksheet. I think something like a simple Input Box thing should do that to bring in the name of the sheet. If you mean something like getting the code to ask you for an excel file that is a bit more complicated. I think I did that a few times in Threads I was in. If that is wot you want I could look sometime to see if I can remember / find that and apply it to your case. (Or do you mean that the code should ask you to specify the output path for the text file). If you say exactly wot you want I could have a go when I get the time.

…….

…. I am trying to learn VBA,………..

. 3 - I am too! Mostly part time for a private project.
. Did you get my code to work?. How is yours going—Did you get it too work? If you did or can get yours to work can you put some ‘comments in like I did – It would help and make it interesting to see another working code variation.


Not sure when I am back “on”, bit if you reply to my questions sometime me or someone may help further. No guarantees… as I said I am just learning myself, but keen to ‘ave a go when I can

Alan

 

[cgreene87]

Hi Alan,

The code you provided works great! I will take a look back at my code and see if I can play around with it! I will catch up on my VBA literature and post any more questions I have.

Thanks again,

Craig

 

[DocAElstein]

Hi Alan,

The code you provided works great! I will take a look back at my code and see if I can play around with it! I will catch up on my VBA literature and post any more questions I have.

Thanks again,

Craig

Ok. I think You have the 2 issues
.1 Sorting / splitting up your data with fixed prices into separate “chunks of data”**

.2 the business of creating and sticking that data into text files.

. Just very briefly looking at your code and comparing with the others: I think you tried an intermediate storing of the sorted / separated “chunks of data”** in sheets, then as a second exercise tried to copy each sheet to a teat file. Probably the best idea actually. So keep at it and please post if you get it working. Mine just picked out the “chunks of data”**, and then sent it out to a text file created for each chunk as it went along. Probably not too professional. I am not sure if snb and lewis codes were addressing the sorting / separating “chunks of data”** issue Lewis was just trying to create a file for every row (I think). Maybe snb was doing something very clever that did sort or filter as well, but that is way above me and as I could not get it to work I could not investigate further. Shame. With both lewis and snb codes I think I could have learnt a lot in the area of playing with the Microsoft scripting stuff and text files, creating them etc. I may just sometime try again to follow their codes

.
....... Otherwise glad my code is working for you. Always good to know the stuff I sent works!!!