Excel - Export To Text File Active Worksheet Problem

[DAmoss]

I have the following vba code for exporting my worksheet to a text file. As you can see I also pass it the worksheet that I want it to export. Unfortunately I can't seem to get to work! It always exports 'Sheet1'.

Can someone tell me where I'm going wrong? I thought I was activating the right sheet. Do you think it's because this code is currently stored under 'Sheet1'?

Cheers


Public Sub ExportToTextFile(FName As String, WSheet As String)

Dim WholeLine As String
Dim FNum As Integer
Dim RowNdx As Long
Dim ColNdx As Integer
Dim StartRow As Long
Dim EndRow As Long
Dim StartCol As Integer
Dim EndCol As Integer
Dim CellValue As String
Dim Seperator As String
Dim CurSheet As String

Seperator = ","
CurSheet = ActiveSheet.Name
Application.ScreenUpdating = False
Worksheets(WSheet).Activate

On Error GoTo EndMacro:
FNum = FreeFile

With ActiveSheet.UsedRange
StartRow = .Cells(1).Row
StartCol = .Cells(1).Column
EndRow = .Cells(.Cells.Count).Row
EndCol = .Cells(.Cells.Count).Column
End With

Open FName For Output Access Write As #FNum

For RowNdx = StartRow To EndRow
WholeLine = ""
For ColNdx = StartCol To EndCol
If Cells(RowNdx, ColNdx).Value = "" Then
CellValue = Chr(34) & Chr(34)
Else
CellValue = Cells(RowNdx, ColNdx).Text
End If
WholeLine = WholeLine & CellValue & Seperator
Next ColNdx
WholeLine = Left(WholeLine, Len(WholeLine) - Len(Seperator))
Print #FNum, WholeLine
Next RowNdx

EndMacro:
On Error GoTo 0
Application.ScreenUpdating = True
Close #FNum
Worksheets(CurSheet).Activate

End Sub

Private Sub CommandButton1_Click()

ExportToTextFile "DataExport.csv", "Sheet2"

End Sub

 

[SkipVought]

Hi,

Is there some reason why you are not using SaveAs to a .csv?

Skip,
[sub]
[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount! [/sub]

 

[DAmoss]

Yes, I am trying to automate the process so that the user only has to click on a button to export the data in a worksheet.

It works, just not for different worksheets when the parameter is passed.

Any ideas?

 

[DAmoss]

It's Ok I've solved it ... Should have realised I should have put the routine in a module and then called it.

It works a treat now!

 

[SkipVought]

You can SaveAs in a procedure. This sure looks like code overkill.

Skip,
[sub]
[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount! [/sub]