Can I send a space delimited file of only some columns/rows?

[LindaGibson]

Hopefully this can be done. Here is my goal:

I am in the beginning stages of creating a spreadsheet that has several columns. I would like to have only some of those columns and some of the rows get sent to a text file, seperated by spaces, but I want all the information in this one place. There will be other reports pulling out other information as well.

I was thinking of creating a column where I would put an x if it was to be used for report A, etc. But I really dont even know if a report of that kind can be made.

Report A will be sent as a space delimited text file to be used in a filemaker database. Report B will be simply for printing as a look up reference of information we refer to often. B is not as critical with spacing and such.

In case this helps, I will describe the information being put in here. (this may be too much information, but I hope not)

I work for a commercial printer, where we get different contract pricing for particular brands of paper, in different sizes. This is based on a thousand weight price, which is figured differently depending on the weight of the paper, which is different for every basis and size.

The columns will be:

A - Filemaker (there will be an asterisk here if it is to be sent to the "filemaker" report (A) ...there may be a better way to do that though)

B - Grade (will either say offshore or domestic...again for filemaker, and will be used only for sorting the info in the "filemaker" report)

C - Paper (brand)

D - Type (book, cover, offset)

E - Basis (weight)

F - Size (19x25, 23x35 etc)

G - M/Weight (sheet weight, used in factoring price)

H - JC CWT (cwt price from JC Paper)

I - JC price (will have a formula... G multiplied by H, rounded to nearest dollar)

J - UNI CWT (cwt price from Unisource Paper)

K - UNI price (will have a formula... G multiplied by J, rounded to nearest dollar)

L - SP CWT (cwt price from Spicers Paper)

M - SP price (will have a formula... G multiplied by L, rounded to nearest dollar)

N - XPX CWT (cwt price from Xpedx Paper)

O - XPX price (will have a formula... G multiplied by N, rounded to nearest dollar)

P - BEST Vendor (will have a formula...of the 4 columns (I, K, M, O) the lowest price will have the Vendor's initials in this column

Q - BEST Price (will have a formula...of the 4 columns (I, K, M, O) the lowest price will be listed in this column)


Each row will have all the different types of paper, in all the weights and sizes that we use, each with prices from all 4 vendors. (some of these vendors do not offer contract pricing, so the cell will be blank in that instance)

Not all paper sizes and types need to go to the Filemaker Report (A), thus the X or some mark in column A to indicate if it needs to or not.

This "A" report will have in a space delimited text file...

sorted by:
Column A (if marked), then Type, then grade, then alphabetically by Paper brand, basis and size

What will actually print on the text file will be:

Paper
Basis
Size
Vendor
Price

and it will need to come out as such:

Endeavor-Velvet Book 70lb 17.5x22.5---SP $44

I will add the dash in the Paper name, but am not sure how I would get the 3 dashes between the size and Vendor Initials since there can be no spaces there.

Actually, I am not sure how to print out a report at all, or if I can simply select columns and rows this way...or if it can even be sent as a space delimited file.

I am afraid I may be in too deep...but am hoping to hear that this really is not all that hard!

Thanks!
Linda

 

[parry]

Re: Can I send a space delimited file of only some columns/r

Hi Linda, got to shoot to a meeting but this could be done with code very easily. Heres an example presuming you have data from row 2 downwards in the sheet.

Sub ExportSpaceDelimitedText()
Dim FilePath, FileNumber, NumRows As Long, i As Long

FilePath = "C:\MyTextFile.txt" 'change path to suit
FileNumber = FreeFile
NumRows = Range("A65536").End(xlUp).Row

Open FilePath For Output As #FileNumber

For i = 2 To NumRows
Print #1, Cells(i, 3) & "-" & Cells(i, 5) & " " & Cells(i, 6) & "---" & Cells(i, 16) & " " & Cells(i, 17)
Next i

Close FileNumber

End Sub

 

[parry]

Re: Can I send a space delimited file of only some columns/r

Hi Linda, let me know if you have any problems. The code would work but theres one line of code I should change so heres an update.

Sub ExportSpaceDelimitedText()
Dim FilePath, FileNumber, NumRows As Long, i As Long

FilePath = "C:\MyTextFile.txt"
FileNumber = FreeFile
NumRows = Range("A65536").End(xlUp).Row

Open FilePath For Output As #FileNumber

For i = 2 To NumRows
Print #FileNumber, Cells(i, 3) & "-" & Cells(i, 5) & " " & Cells(i, 6) & "---" & Cells(i, 16) & " " & Cells(i, 17)
Next i

Close FileNumber

End Sub

 

[LindaGibson]

My goodness...I knew someone would have some sort of solution, but I am very much a newbie at this. I am not computer ignorant, but I dont have any experience in coding, except for some in web design.

To be honest, I have not a clue on how to implement this code. Is there an easy way for you to explain this?

I am so sorry for my further questions.

I do so appreciate your help!
Linda

 

[parry]

Re: Can I send a space delimited file of only some columns/r

No problem Linda.

To add the code above to a Module do the following:-
1. Open the Visual Basic Editor (ALT+F11 or Tools|Macro|Visual Basic Editor from the menu)
2. Select Insert|Module from the menu
3. Paste the code in the right-hand window
4. Close the Visual Basic Editor (ALT+Q or File|Close and return to Microsoft Excel from the menu)

To run the macro select Tools|Macro|Macros then select the macro ExportSpaceDelimitedText then click run.

What it does...
1) Creates a text file on your C:\ drive called MyTextFile.txt. You can just change the path in the code to create a file of a different name saved somewhere else
2) The code loops through from cell A2 downwards with each row being a separate line in the text file
3) The columns used in the text file are C,E,F,P & Q. These are separated by spaces or dashes as you indicated, so you shouldnt have to do this manually.

Just run the macro and look at the file created and see if its what you want.

regards
Parry

 

[parry]

Re: Can I send a space delimited file of only some columns/r

Hi Linda, heres some commented code which hopefully makes more sense to you. Ive also added a message box to advise when the operations complete.

Sub ExportSpaceDelimitedText()
'Declarations of variables to be used in the procedure
Dim FilePath As String, FileNumber As Integer, NumRows As Long, i As Long

'Set a variable representing the path and filename to be created
'You can simply amend the text within the quotes to change the path/filename
FilePath = "C:\MyTextFile.txt"

'Create a filenumber to represent a file being worked with for I/O operations
FileNumber = FreeFile

'Find how many rows of data you have to process. This finds the last used row in column A
'This will be used in the For i = statement below
NumRows = Range("A65536").End(xlUp).Row

'Create the text file so we can add the data to it
Open FilePath For Output As #FileNumber

'Loop through from row 2 to the last used row (NumRows)
For i = 2 To NumRows

'This adds the text to each line
'Cells represents a cell in the sheet referenced by Row,column ie Cells(Row,Column)
'So this is grabbing data from cells in the sheet with i being the row
'and the next number being the column. eg Cells(i,3) in the first loop will mean
'the cell in row 2 column 3 (ie C2), the next loop it will be row 3, column 3 etc
'The & is used to join cells together with spaces or --- separating data as required
Print #FileNumber, Cells(i, 3) & "-" & Cells(i, 5) & " " & Cells(i, 6) & "---" & Cells(i, 16) & " " & Cells(i, 17)

'Go to the next row to process
Next i

'We have finished now so close the text file
Close FileNumber

'Produce a message box advising the jobs done
MsgBox "The text file " & FilePath & " has been created.", vbInformation, "Text File Created"

End Sub

 

[LindaGibson]

Re: Can I send a space delimited file of only some columns/r

Thanks so much, I am adding some data to the worksheet currently so I can test the information you gave me. I so appreciate your help!