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
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