using notepad with excel

[Dennis]

Hey

Im new to using excel and visual basic and I want to create a macro, which will collect information from a sheet in excel paste it to notepad and save it in notepad. I would also like it not to replace the previous time it saved notepad.

Thanks if anyone can help

 

[Colo]

Hi Dennis,
Pls click the link following.

http://www.mrexcel.com/board/viewtopic.php?topic=6411&forum=2

 

[Dennis]

Hi colo thanks for the help it works but it means I will have to delete the previous document that has been saved. Is there a way to call the notepad document a different name each time the macro is run like 1, 2, 3...or something like that. Thanks for the help

 

[Colo]

Hi Dennis. May I ask you a question?
The cells where you want to save are always same?
If so, I would like to recommend to use "open statement" to you.

But the cells what you want to save are different for each times,
I would like to recommend to use another way (see following code).<pre>
Sub Test()
Dim arrBuf(), i As Long, c As Integer, shAct As Worksheet
Dim strFName As String 'aFile Name :Full path
With Application
.ScreenUpdating = False
.DisplayAlerts = False
'Copy to clipbord
Set shAct = ActiveSheet
strFName = Application.GetOpenFilename("Textfile(*.txt),*.txt")
If strFName = "False" Then Exit Sub
c = 256
ReDim arrBuf(1 To c)
For i = 1 To c 'Loop
'A reading format is limited to a character sequence.
arrBuf(i) = Array(i, 2)
Next i
delimt = "," 'Or Chr(9) which mean Tab
On Error GoTo READY_OPENED
Workbooks.OpenText FileName:=strFName, _
StartRow:=1, _
DataType:=xlDelimited, _
Other:=True, _
OtherChar:=delimt, _
FieldInfo:=arrBuf

shAct.Range("A1").CurrentRegion.Copy Range("A65536").End(xlUp).Offset(1)
With ActiveWorkbook
.SaveAs FileName:=strFName, FileFormat:=xlText
.Close False
End With
.DisplayAlerts = True
.ScreenUpdating = True
End With
Exit Sub
READY_OPENED:
MsgBox "The file is already opened."
End Sub</pre>

_________________
With regards,
Colo <MARQUEE scrollamount=8 behavior=alternate>

</MARQUEE>
This message was edited by Colo on 2002-04-29 18:17
This message was edited by Colo on 2002-04-29 20:54
This message was edited by Colo on 2002-04-30 19:42