Calling all Excel Gurus...

[James Burke]

We do a lot of text merging with SignLab and now we need to change over to Gerber Omega. For SignLab, we use tab delimited Excel files saved as text. Gerber Omega does not allow tab delimited text files.

I need to take an Excel spreadsheet like the one on the left (supplied by my customer) and export/save it to a text (or MS Word) file like the one shown on the right. Blank cells in Excel will show up as blank lines on the text file.

Constraints: Our order template for our customer is set up like the Excel file shown and we do not want to change it if at all possible. We can not manually edit data since some customer files contain literally thousands of lines of text (we'd also run the risk of accidently changing some lines while manually editing).

I've tried just about everything I know, and I have no knowledge of macros.

Can anybody give me direction?

Thanks,

JB

 

[bob]

Enter the following code EXACTLY as a macro, select the range of cells you want to merge, and invoke this macro. The range you selected will be replaced by a single column of merged data.

Sub merge_text()

With Selection
hold = .Value
.ClearContents
Set mrange = .Range("A1:A" & .Rows.Count * .Columns.Count)
midx = 1
For ridx = 1 To .Rows.Count
For cidx = 1 To .Columns.Count
mrange.Cells(midx, 1) = hold(ridx, cidx)
midx = midx + 1
Next cidx
Next ridx
End With

End Sub

 

[James Burke]

Bob,

Thank you for your assistance.

Having never done a macro before, do I enter this into the in the function bar? I will visit Google for specifics on how to do this.


Thank you,

Jim

 

[bob]

Bob,

Thank you for your assistance.

Having never done a macro before, do I enter this into the in the function bar? I will visit Google for specifics on how to do this.


Thank you,

Jim

I'm not sure just which version of Excel you're running but I'll assume it's 2007 or better...

On the 'Developer' tab select 'Macros'. A dialog will appear showing the names of any existing macros. Enter 'merge_text' in the 'Macro Name:' text box and click 'Create'. You will be taken to the Visual Basic editor in a new code module initialized with...

sub merge_data()

end sub

Insert the code [WITHOUT the 'sub merge_data' and 'end sub' lines, they are already there] between those two lines, go back to the spreadsheet and you're good to go.