×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Excel - Export To Text File Active Worksheet Problem

Excel - Export To Text File Active Worksheet Problem

Excel - Export To Text File Active Worksheet Problem

(OP)
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

RE: Excel - Export To Text File Active Worksheet Problem



Hi,

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

Skip,

glasses Be Advised! A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount! tongue

RE: Excel - Export To Text File Active Worksheet Problem

(OP)
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?

RE: Excel - Export To Text File Active Worksheet Problem

(OP)
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!

RE: Excel - Export To Text File Active Worksheet Problem



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

Skip,

glasses Be Advised! A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount! tongue

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close