[RESOLVED] Select string of data and reformat into single cell/txt file

[heinz1218]

I have a few hundred lines of data, each containing a 5 or 6 digit number in column C. I need to use them to search in a different program, but i need to have it in the following format:

111111 OR 222222 OR 33333 OR 444444 etc.

How would I go about returning the string of the first 5/6 digits, and put that data either into a single cell in the above format (so I could CTRL+C it), or even a .txt file (doesn't matter to me, as long as it's in that format)

I tried searching online but there doesn't seem to be a good reference site for VBA. Anyone know of one?

[westconn1]

For i = 1 To Sheet1.UsedRange.Rows.Count
    txtstr = txtstr & " OR " & Cells(i, 3)
Next
somecell = txtstr 'put in cell
f1 = FreeFile
Open "test.txt" For Output As f1
Print #f1, txtstr    '  or write to text file
Close f1

should do what you ask

pete

[heinz1218]

Awesome that worked, thanks a bunch.

what's a "FreeFile", and can you usually get away without Dimming the i and the f1?

p.s. for anyone reading: it outputs by default to your my documents folder.

[si_the_geek]

FreeFile is a function that gets the next available reference number for working with files (many people use 1 instead of that, then get errors because they already have a file open).

You should always Dim your variables (and give them data types too), no matter what they are used for. It makes any code that uses them faster, and it is also more memory efficient.

p.s. for anyone reading: it outputs by default to your my documents folder.

Actually it doesnt - that's just a coincidence. If outputs to the folder where your code is being run from (which isnt necessarily the same folder as it is stored in).

If you want to save in a specific folder, use something like this:

Open "C:\My Folder\test.txt" For Output As f1

..if you want to save it in the same folder as the program, use this:

Open App.Path & "\test.txt" For Output As f1

[heinz1218]

FreeFile is a function that gets the next available reference number for working with files (many people use 1 instead of that, then get errors because they already have a file open).

So do you Dim that as something? I'm not quite sure what you mean by "next available reference number"....my bad.

[si_the_geek]

It's ok, I didn't explain very well!

You don't need to Dim it, as it is a Function. A function is like the "Close" on the last line of westconn's code, except it returns a value of some sort.


As to the reference number thing... it is possible to work with several files at the same time, so you need a way of saying which file each line of code should act on - for that you use a reference number.

Here is an example of code for working with two files (not good code, but explains the concept):

'open both files
Open "test1.txt" For Output As #1
Open "test2.txt" For Output As #2
 
'write some data to them
Print #1, "First file"
Print #2, "Second file"
Print #1, "..a bit more for the first file"
 
'close both files
Close #1
Close #2

As you can see, we are using the numbers 1 and 2 to refer to the different files. Using numbers direcltly lke this is a bad idea, as you may have re-used the same number (or accidentally not closed a file), which will give you errors.

In westconn's code, the variable f1 is used instead, and the value comes from FreeFile - which gives you the next available unused number. For working with two files, it would need to be like this (as the number only gets counted as "used" when you open the file):

'open both files
Dim f1 as Integer, f2 as Integer
  f1 = FreeFile
  Open "test1.txt" For Output As f1
  f2 = FreeFile
  Open "test2.txt" For Output As f2
...

[heinz1218]

Awesome, thanks a bunch man.