?
Solved

Data from Excel to Notepad

Posted on 2008-02-05
14
Medium Priority
?
857 Views
Last Modified: 2008-02-06
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
0
Comment
Question by:cwalker4581
  • 6
  • 5
  • 3
14 Comments
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 20826560
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
 

Author Comment

by:cwalker4581
ID: 20826632
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
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 20826714
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 20826950
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
 
LVL 48

Expert Comment

by:Wayne Taylor (webtubbs)
ID: 20827037
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
 

Author Comment

by:cwalker4581
ID: 20827301
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
 
LVL 48

Expert Comment

by:Wayne Taylor (webtubbs)
ID: 20829865
>>Is there a fix to this that you know of?

Use something like what we've posted above.
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 20830398
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
 
LVL 48

Expert Comment

by:Wayne Taylor (webtubbs)
ID: 20831114
Chris - I believe cwalker4581 is saving the Excel file as a Tab Delimited Text file, using the SaveAs command.
0
 

Author Comment

by:cwalker4581
ID: 20831510
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
 
LVL 59

Accepted Solution

by:
Chris Bottomley earned 1600 total points
ID: 20831762
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
 

Author Comment

by:cwalker4581
ID: 20831801
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
 

Author Comment

by:cwalker4581
ID: 20831805
chris, If you accept yours as my solution will you automatically be awarded the points?
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 20832208
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

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Today, unlike web development, the mobile landscape is complex enough for a software engineer and Android is posing more challenging environment thanks to its fragmentation issues on hardware and software fronts.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …

589 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question