Down loading XL sheet data into the Notepad

  • Thread starter Thread starter ssamband
  • Start date Start date
S

ssamband

Hi,

Please see the following scenario. I have an Excel sheet in which there
are few columns say 10 such as Sl. no, name, Address, Date of Birth and
so on. Now that I want to extract only 2 column from the sheet say,
name and Date of Birth only to the Notead by clicking a button on the
Excel sheet. Is is possible by means of a macro?

Can anyone explain how can I achieve this?

Thank you,
Siva.
 
You could record a macro when you copy the columns to another worksheet.

Then continue recording when you save that new file as a .prn file or .csv file.

Then continue recording when you close that new workbook.

This actually doesn't copy it into notepad, but it creates a text file that can
be opened in notepad.
 
Thank you Dave Peterson.


I tried that. This macro assumes the fixed number of column. Do yo
have any other way that can be used to copy a number of column, thoug
there is change in the column?

Thanks,
Siv
 
How do you know which columns to include?

If it varies each time, you could even prompt the user to select the columns
first.

This worked for me:

Option Explicit
Sub testme()

Dim myRng As Range
Dim myRng2 As Range
Dim myCol As Range
Dim newWks As Worksheet
Dim DestCell As Range
Dim cCtr As Long

Set myRng = Nothing
On Error Resume Next
Set myRng = Application.InputBox _
(Prompt:="Please select all the columns you want copied", _
Type:=8)
On Error GoTo 0

If myRng Is Nothing Then
Exit Sub 'user hit cancel
End If

Set myRng2 = Nothing
On Error Resume Next
Set myRng2 = Intersect(myRng.Parent.Rows(1), myRng.EntireColumn, _
myRng.Parent.UsedRange).EntireColumn
On Error GoTo 0

If myRng2 Is Nothing Then
MsgBox "Please select a range that makes sense!"
Exit Sub
End If

Application.ScreenUpdating = False

Set newWks = Workbooks.Add(1).Worksheets(1)
Set DestCell = newWks.Range("a1")

With myRng2.Parent
For cCtr = 1 To .UsedRange.Columns(.UsedRange.Columns.Count).Column
If Intersect(.Cells(1, cCtr), myRng2) Is Nothing Then
'do nothing
Else
.Cells(1, cCtr).EntireColumn.Copy
DestCell.PasteSpecial Paste:=xlPasteValues
DestCell.PasteSpecial Paste:=xlPasteFormats
Set DestCell = DestCell.Offset(0, 1)
End If
Next cCtr
End With

Application.CutCopyMode = False

With newWks
.UsedRange.Columns.AutoFit
Application.DisplayAlerts = False
.Parent.SaveAs Filename:="C:\myfile.txt", FileFormat:=xlTextPrinter
Application.DisplayAlerts = True
.Parent.Close savechanges:=False
End With

Application.ScreenUpdating = True

End Sub
 
Thanks for spending your time on this. But, it gave me some compilatio
errors. As I am a tyro to Scripts, I am unable to locate the error
Could you please correct it?

Where do I have to copy and paste to execute ??

Thanks
 
There shouldn't be any compile errors in this code (it compiled (and actually
worked!)) before I posted it.

I'm not sure how you got an error.

This is a VBA procedure.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Short course:

Open your workbook.
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste the code in there.

Now go back to excel and test it out via:

tools|macro|macros...
select the macro and click run.


===========
One way to find the line with an error is to click on the "Sub testme()" line
(in the VBE). Then hit F8 to step through each line of the code. You'll find
out which line caused the trouble.
 
Dave Perterson,

Sorry about this. I encountered the same error again. To explain you,
have made a word doc with screen shots and attached here. Please, have
look at it.

Thank you.

-Siva

+-------------------------------------------------------------------
|Filename: compilation error screen shots.zip
|Download: http://www.excelforum.com/attachment.php?postid=3652
+-------------------------------------------------------------------
 
I don't go through the excelforum.

You'd be better served by posting in plain text.
 
Back
Top