Data from Excel to Notepad

Hello all,

I am having a problem copying data from excel into notepad.  I am able to open, create and close the notepad file but no data is being pasted to the file.  Here is my code:

Set xlRng = xlWB.Worksheets("Sheet1").UsedRange
Open strFileName For Output As #1
Print #1, xlRng.Value
Close #1

Thanks again for the help
cwalker4581Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Chris BottomleySoftware Quality Lead EngineerCommented:
You need to step through each cel in teh range i.e. something like:

Chris
Dim cel As Range
 
Set xlRng = xlWB.Worksheets("Sheet1").UsedRange
Open strFileName For Output As #1
For Each cel In ActiveSheet.UsedRange.Cells
    Print #1,  cel
Next
Close #1

Open in new window

0
cwalker4581Author Commented:
That's much closer than I have been able to come up with, but not quite there yet.  The UsedRange is from A1:G100 so the data needs to appear in notepad as tab delimited, which i believe is the way it appears if I were to manually copy the data from excel into notepad.  

Any ideas...
0
Chris BottomleySoftware Quality Lead EngineerCommented:
Then treat them as rows, haven't tested but for instance:

Chris
Dim col As Long
Dim row As Long
Dim rowData As String
 
Set xlRng = xlWB.Worksheets("Sheet1").UsedRange
Open strFileName For Output As #1
For row = 1 To ActiveSheet.UsedRange.Rows.Count
    For col = 1 To ActiveSheet.UsedRange.Columns.Count
        rowData = rowData & ActiveSheet.Cells(row, col) & vbTab
    Next
    Print #1,  rowData
Next
End Sub
Close #1

Open in new window

0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Chris BottomleySoftware Quality Lead EngineerCommented:
Sorry should be presented using your range as:

Chris
Dim col As Long
Dim row As Long
Dim rowData As String
 
Set xlRng = xlWB.Worksheets("Sheet1").UsedRange
Open strFileName For Output As #1
For row = 1 To xlRng.Rows.Count
    For col = 1 To xlRng.Columns.Count
        rowData = rowData & xlRng.Cells(row, col) & vbTab
    Next
    Print #1,  rowData
Next
End Sub
Close #1

Open in new window

0
Wayne Taylor (webtubbs)Commented:
You may also like to try this, which may be slightly quicker as it doesn't loop through each of the columns....
    Dim xlrng As Range
    Dim row As Long
    Dim rowData As String
 
    Set xlrng = xlWB.Worksheets("Sheet1").UsedRange
    Open strFileName For Output As #1
    For row = 1 To xlrng.Rows.Count
        rowData = Join(Application.Transpose(Application.Transpose(xlrng.Rows(r).Value)), vbTab)
        Print #1, rowData
    Next
    Close #1

Open in new window

0
cwalker4581Author Commented:
I was able to get it working by saving the file as a text file, however columns that have a comma in the data are surrounded by double quotes.  Is there a fix to this that you know of?
0
Wayne Taylor (webtubbs)Commented:
>>Is there a fix to this that you know of?

Use something like what we've posted above.
0
Chris BottomleySoftware Quality Lead EngineerCommented:
I have checked my earlier post, (reproduced below) and it doesn't put quotes in ... can you add any more info?

Chris
Set xlRng = xlWB.Worksheets("Sheet1").UsedRange
Open strFileName For Output As #1
For row = 1 To xlRng.Rows.Count
    For col = 1 To xlRng.Columns.Count
        rowData = rowData & xlRng.Cells(row, col) & vbTab
    Next
    Print #1, rowData
Next
Close #1

Open in new window

0
Wayne Taylor (webtubbs)Commented:
Chris - I believe cwalker4581 is saving the Excel file as a Tab Delimited Text file, using the SaveAs command.
0
cwalker4581Author Commented:
Hi guys,

Sorry for the delay, I went home for the night.  webtubbs is correct.  I did what chris bottomley suggested and that added the data with tabs between each column, but other than the first row of data there wasn't a break after each row.  So the data would look something like this:

row1a    row1b    row1c
row2a    row2b    row2c    row3a    row3b    row3c
row4a    row4b    row4c    row5a    row5b   row5c

So I decided just to save the file as text but that only created a new problem.  I thought about doing a find and replace to the data once it is saved as text, but it I don't think that would be the best, most efficient way to do this.  
0
Chris BottomleySoftware Quality Lead EngineerCommented:
OOps missed that ... try the following

Chris
Set xlRng = xlWB.Worksheets("Sheet1").UsedRange
Open strFileName For Output As #1
For row = 1 To xlRng.Rows.Count
    For col = 1 To xlRng.Columns.Count
        rowData = rowData & xlRng.Cells(row, col) & vbTab
    Next
    Print #1, rowData
    rowdata = ""
Next
Close #1

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
cwalker4581Author Commented:
I think that's got it...Looks good.  I'll run it through our clients system and see if it accepts it.  

Thanks to everyone for your help
0
cwalker4581Author Commented:
chris, If you accept yours as my solution will you automatically be awarded the points?
0
Chris BottomleySoftware Quality Lead EngineerCommented:
As soon as you accept an answer the points are allocated to the person(s) you choose as helping automatically so yes I have the points.

Glad it worked finally and I do apologise for missing the reset.  My test data was so large I missed the duplication and let myself and you down.

Chris
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.