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

 

[snb_]

It might be beneficial to upload the results in the clipboard:

Sub M_snb()
  ActiveSheet.Cells(1).CurrentRegion.Resize(, 2).Copy

  With GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
    .GetFromClipboard
    CreateObject("scripting.filesystemobject").createtextfile("G:\OF\file_snb.csv").write .GetText
  End With
End Sub

To upload use:

https://www.wetransfer.com/#

 

[DocAElstein]

Hi snb
Re Post #30
. Thanks for the “.csv open in Notebook stuff”. (I prefer Notebook as well as I “thought” I could then see everything****).
. I did not try that variation as the .csv files were automatically stored and opened with my default settings in Excel. So I tried doing it as you said. Initially**** the results “looked” like before with no tab….
… but I “zoomed in” and played with the left right arrows on the keyboard going left and right stepping through the data and counting characters…. It came out that there was an extra character between the item and price, so presumably indicating indeed that a Tab was there.! contrarily to wot I had been saying before. Just strangely it was shown incredibly narrow. So I went back and checked the text .txt files again… and .. same thing!!!. So the tabs are there !!!. They are strangely just shown very narrow so you cannot see them easily. (I think I have sometimes seen that Tabs are shown in different widths before, but until now never so narrow that they are hardly detectable to the “naked eye” ).
. So I must always check for tabs by stepping through the data and counting characters rather than just “looking at it!” So I learnt something good there!!

. Thanks for the link. Was helpful. Confirms a bit wot I thought was going on with the clipboard stuff as well as clarifying nicely again the Early and Late binding ideas..

. The “tab inconsistency in the produced data files stuff” was then a bit down to my inexperience with looking for them in Files. Sorry about that!. But the penny would probably not ‘ave dropped without your continued feedback. So thanks again. I think we are almost there!

. Just a last couple of last questions then

.1 Do I assume then that your code is intended to give the item number with the price alongside in the produced data files. (I find that adequate (or better). But it is not wot the OP wanted.)

.2 Could you modify your code to just produce the item in the data files as mine does?. I ask that as that could really wrap up wot has been a very good learning and demonstration thing here: I mean if you compare your code with my “opened up” version it is a very good demonstration indeed of the whole “Object orientated hierarchical programming idea”, which allows an amazing amount of things to be done in a short code line (Unfortunately I lack the experience just now to do it!)

Alan.

P.s. . The Let stuff: Yeah I know you do not need it. Just a through back to 25 years ago, - the last time I did any programming!!!!! And as it “Lets” you do it, I include it as it helps me to distinguish and think about the difference in wot is going on between lines with Let, Set, and the code lines where you are not allowed to include it.

 

[snb_]

I wouldn't mind if you used 'what' instead of 'wot'.

You might have figured out yourself:

Sub M_snb()
    ActiveSheet.Cells(1).CurrentRegion.Resize(, 2).Copy

   With GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
      .GetFromClipboard
       sn = filter(Split(.GetText, vbCrLf),vbtab)
   End With

   With CreateObject("scripting.filesystemobject")
      Do Until UBound(sn) = -1
         c00 = Split(sn(0), vbTab)(1)
         .createtextfile("G:\OF\file" & c00 & ".csv").write [COLOR=#ff0000]replace([/COLOR]Join(Filter(sn, vbtab & c00), vbCrLf)[COLOR=#ff0000], vbtab & c00,"")[/COLOR]
         sn = Filter(sn, vbtab & c00, False)
      Loop
   End With 
End Sub

 

[DocAElstein]

Hi snb
Re Post #33
. Thank you very much for the final full working Code. That I think very nicely completes the thread, (and helps me very much with my learning). I did try very hard to get there alone and indeed had the “work around” that did it. But as a VBA Beginner and Computer novice it was very difficult – I needed a few hours of intense thinking to understand your codes generally and to produce my attempt, which I needed and will still, for a while at least, need to understand what is going on!!. And again I needed a bit of time to get the syntax etc. right for doing something similar to your last modification in place of the “work around” I my code. I have done that now and for completeness include what I think is the final version of my Code below.
. Clearly in comparing your and my final codes we have an excellent example of how one can achieve so much using “Object Orientated Hierarchical Programming” in a few lines when one has the experience that you clearly have.
. Sir, thank you very much for your patience!

Alan Elston

My Final Code:

    [color=darkblue]Sub[/color] M_snb5AlanMod()
   
    ActiveSheet.Cells(1).CurrentRegion.Resize(, 2).Copy [color=green]'Copy all data (and headings) to clip board[/color]
   
    [color=darkblue]Dim[/color] objClipboard [color=darkblue]As[/color] [color=darkblue]Object[/color] [color=green]'Clipboard Object: It has a Funny long name as it is ..not necerssarily anything to do with Excel or FileScripting[/color]
    [color=darkblue]Set[/color] objClipboard = GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}") [color=green]'..not necerssarily anything to do with Excel or FileScripting[/color]
        [color=green]'The above two lines is a "Late Binding" approach to making Clipboard stuff available. An alternative "Early Binding" would be to first set a referrence to Microsoft Forms 2.0 Object Library - Tools>>References>>scroll down and check the box next to Microsoft Forms 2.0 Object Library..and then the next two lines.....[/color]
[color=green]'   Dim objClipboard As DataObject[/color]
[color=green]'   Set objClipboard = New DataObject[/color]
    objClipboard.GetFromClipboard [color=green]' a necerssary inbetween step. Not quite sure wot it does, maybe sort of preparing the clipboard to give stuff[/color]
 
    [color=darkblue]Dim[/color] TextFromClipboard [color=darkblue]As[/color] [color=darkblue]String[/color]
    [color=darkblue]Let[/color] TextFromClipboard = objClipboard.GetText [color=green]'This is a bit subtle: It is actually a long string , a TextString comprising values and in this case carriage returns, between them[/color]
   
    [color=darkblue]Dim[/color] sn() [color=darkblue]As[/color] [color=darkblue]String[/color] [color=green]'Array. We do not know the size: It will be set by the Split Function[/color]
   
    [color=darkblue]Dim[/color] FirstLine() [color=darkblue]As[/color] [color=darkblue]String[/color] [color=green]'First Line picked out each time after filtering out last individual price lists[/color]
   
    [color=darkblue]Dim[/color] ItemsAndPrices() [color=darkblue]As[/color] [color=darkblue]String[/color] [color=green]'Array for individual Item And Price lists for similar prices[/color]
    [color=darkblue]Dim[/color] OutputTextString [color=darkblue]As[/color] String [color=green]'The text string to sent out as a Text Stream[/color]
   
    [color=darkblue]Let[/color] sn = Split(TextFromClipboard, vbCrLf) [color=green]'Takes the [color=darkblue]String[/color] from Clipboard, breaks it at the carriagereturn with linefeed which effectively gives a 1 dimensional array which I think is a long horizontal thing, that is to say a list aslong columns as it were?!?[/color]
   
[color=green]'    Dim fsoFileSystem As Object[/color]
[color=green]'    Set fsoFileSystem = CreateObject("Scripting.FileSystemObject")[/color]
 
    [color=darkblue]Dim[/color] fsoFileSystem [color=darkblue]As[/color] Scripting.FileSystemObject [color=green]'Useful object to do data and data handeling ("Early Binding" here))[/color]
    [color=darkblue]Set[/color] fsoFileSystem = [color=darkblue]New[/color] Scripting.FileSystemObject [color=green]'This one is to do with writing but note in writing you often make something like a file[/color]
[color=green]'Note: the above is done with early binding and requires library reference to MS Scripting Runtime - Tools>>References>>scroll down and check the box next to Microsoft Scripting Runtime[/color]
[color=green]' we could replace the two lines above with -[/color]
[color=green]'    Dim fsoFileSystem As Object[/color]
[color=green]'    Set fsoFileSystem = CreateObject("Scripting.FileSystemObject")[/color]
[color=green]' but the next line only works with early binding to MS Scripting Runtime so stay with Early binding for this one.[/color]
   
    [color=darkblue]Dim[/color] fsoTextFile [color=darkblue]As[/color] Scripting.TextStream [color=green]'A sort of file path or Highway for data I think. This appear only to be possible with Early binding[/color]
       
    [color=darkblue]Dim[/color] j [color=darkblue]As[/color] [color=darkblue]Long[/color] [color=green]'Number for loop count[/color]
    [color=darkblue]Dim[/color] c00 [color=darkblue]As[/color] String [color=green]'An Individual price to use in filtering and in this case identifying a file name.[/color]
   
    [color=darkblue]For[/color] j = 0 [color=darkblue]To[/color] [color=darkblue]UBound[/color](sn) [color=green]' Use UBound to start with to be on the safe side - that would be maximium if no filtering. The j will give the number of files but otherwise is not much use so a Do while sn(0) <>"" Loop is probabbly better similar to wot snb did: better programming practice.[/color]
      [color=darkblue]If[/color] sn(0) = "" [color=darkblue]Then[/color] [color=darkblue]Exit[/color] [color=darkblue]For[/color] [color=green]'sn will reduce in size due to the filtering and evtl nothing will be left. Would not be necerssary using the Do While Loop[/color]
      [color=darkblue]Let[/color] FirstLine = Split(sn(0), vbTab) [color=green]'Split First line   to a 1,2 Array[/color]
      c00 = FirstLine(1) [color=green]'The price given in second column (1) of Array, 8First column is (0)[/color]
      [color=darkblue]Set[/color] fsoTextFile = fsoFileSystem.createtextfile("C:\Users\Elston\Desktop\cgreene87TextFiles\PP Output" & c00 & ".txt") [color=green]'Some how by seting this the text file is created,[/color]
     
      [color=darkblue]Let[/color] ItemsAndPrices = Filter(sn, vbTab & c00) [color=green]'1 Column Array of individual items and prices  with similar prices[/color]
      [color=darkblue]Let[/color] OutputTextString = Join(ItemsAndPrices, vbCrLf) [color=green]' This is a bit subtle: It is actually a long string, a TextString comprising values and a carriage return between them[/color]
      [color=darkblue]Let[/color] OutputTextString = Replace(OutputTextString, vbTab & c00, "") [color=green]'Remove the Tab and Price[/color]
      fsoTextFile.write OutputTextString
      sn = Filter(sn, vbTab & c00, [color=darkblue]False[/color]) [color=green]'We filter out effectivelly up to the next price so that the first valie in second column is the next price to consider[/color]
    [color=darkblue]Next[/color] j
   
 [color=darkblue]End[/color] [color=darkblue]Sub[/color]

Note also my code requires the “Early Binding” to MS Scripting Runtime discussed in Post #2 and #5 – (I got stuck trying to use the more flexible “Late Binding” approach (as the snb_ code does) because of my use of the .TextStream which I was unable to find a “Late Binding” solution for.

 

[cgreene87]

Hi Alan,

I tried the code above but received a compile error message for line 27 "User defined type not identified"

 

[DocAElstein]

Hi Alan,

I tried the code above but received a compile error message for line 27 "User defined type not identified"

Hi,
. I tested my and snb_ final codes extensively.
. Difficult to see at this distance from your description where the problem lies.
. Post the code you are using back and highlight where the error is or send me your file and I will try to take a look if I get time tomorrow.

Alan