Copy & Paste

[Sanderch]

Hi All,

I have created a simple code (not verry good with VBA) to export a column of data and paste it comma delimited into a txt file.

It is usable, but would be great if you could help me iron out a few of the below issues.

1) I will be using this code at work and am struggling to change the save to location, so if i pass this onto a collegue they cant save as they dont have access top my desktop. my work username is 82416309 can i get this to change to current user rather than a set location? (a pop up save to would be ideal)

Open "C:\Documents and Settings\[COLOR=red]82416309[/COLOR]\Desktop\Export.TXT" For Append As #FileNum

2) The output file creates as I would like other than the #FileNum which is the first number of the new row. could I change this to txt to say Line 1, Line 2 ect. I use an oracle base sysyem so it would be good if I could get it to say (for example) "Where Basic.Membno in" instead of a number ?

3) I generaly have a list over 1000 records but the system we use can only acceppt finds of 1000 or less. I have added the last bit of code to remove the 1000 lines and have to press the button again to make it select the next 1000 lines. can i automate this to loop untill the column is empty?

4) (just been picky now) can I stop it adding a comma at the start of every row.

Sub PrintToTextFile()
Dim FileNum As Integer, cl As Range, z As Integer, y As Integer
Dim myStr As String
FileNum = FreeFile '''''' Next free filenumber ''''''
Open "C:\Documents and Settings\82416309\Desktop\Export.TXT" For Append As #FileNum
Print #FileNum, [a1]
z = 10
For Each cl In [a1:a1000]
    y = cl.Column
    If y = z Then
        myStr = myStr & "," & cl
 
'''''' Appends the input to an existing file write to the textfile ''''''
 
Else: Print #FileNum, myStr
        z = cl.Column
        myStr = "": myStr = myStr & "," & cl
    End If
Next
 
'''''' Appends the input to an existing file write to the textfile ''''''
 
Print #FileNum, myStr
Close #FileNum
 
'''''' Message Box to inform of completion '''''''
 
MsgBox "Export File Created on Your Desktop" & vbCrLf & _
"" & vbCrLf & _
"Re-Export for Additional Row's" & vbCrLf & _
"", vbInformation + vbOKOnly, "Export Complete"
 
'''''' Delete's A1:A1000 and resets to A1 '''''''
 
ScreenUpdating = False
    Range("A1:A1000").Select
    Selection.Delete Shift:=xlUp
    Range("A1").Select
ScreenUpdating = True
End Sub

Thanks in Advance

SanderCH

 

[VoG]

For #1 try

Open "C:\Documents and Settings\" & Environ("username") & "\Desktop\Export.TXT" For Append As #FileNum

 

[Sanderch]

Thanks VoG

 

[Sanderch]

Sorted #4 (had the code the wrong way around)

 If y = z Then
        myStr = myStr & cl & ","
        
    Else: Print #FileNum, myStr
        z = cl.Column
        myStr = "": myStr = myStr & cl & ","

On the back of this, as I restrict my lines to 1000, if i only imput 50 nino's into the sheet I get 950 comma's at the end of the txt file. Is there a way to sop inserting the comma's after the cells are empty?

SanderCH