dcsimg
Results 1 to 4 of 4

Thread: [RESOLVED] VBA Excel 7.1. Issue when trying to write to text file.

  1. #1

    Thread Starter
    New Member
    Join Date
    Jun 2019
    Posts
    2

    Resolved [RESOLVED] VBA Excel 7.1. Issue when trying to write to text file.

    I'm trying to output a worksheet to a semicolon delimited file, as there are commas in the data.

    I keep getting the error "Run-time error '52': Bad file name or number"

    I originally thought it was because I had a UNC path for the output text file, so I've mapped a network drive, but that didn't help.

    I've output the variable that I've put into the "Open Out_File For Output As #f" and it is the following:

    Z:\Dev\Data\Rankings\QS_2019.txt

    The drive exists, the file doesn't as I'm trying to create it.

    The code I'm trying to run is from a sheet in that mapped drive, so I tried without the folder name, same error.

    Code below:


    Code:
    Public Sub export_QS()
        Dim Workb As Workbook, This_WB As Workbook, This_Sht As Worksheet, File_Sht As Worksheet, _
        Sht_Count As Integer, Last_Rw As Integer, Last_Col As Integer, out_String As String, Out_File As String
        
        Set This_WB = ActiveWorkbook
        
        'ensure correct workbook is being used
        If This_WB.Name <> "Create_TXT.xlsm" Then
            MsgBox ("Close other workbooks before continuing")
            Exit Sub
        End If
        
        Set This_Sht = This_WB.Worksheets("Main_St")
        
        Dir = This_Sht.Cells(1, 2).Value
        
        File_N = This_Sht.Cells(2, 2).Value
        
        
        
        'ensure variables are set
        If Dir = "" Then
            MsgBox ("Put value in cell B1")
            Exit Sub
        ElseIf IsNull(Dir) = True Then
            MsgBox ("Put value in cell B1")
            Exit Sub
        End If
        
        If Right(Dir, 1) <> "\" Then
            Dir = Dir & "\"
        End If
        
        If File_N = "" Then
            MsgBox ("Put value in cell B2")
            Exit Sub
        ElseIf IsNull(File_N) = True Then
            MsgBox ("Put value in cell B2")
            Exit Sub
        End If
        
        'open the workbook
        Set Workb = Workbooks.Open(Filename:=Dir & File_N, ReadOnly:=True)
        
        Sht_Count = Workb.Sheets.Count
        
        For i = 1 To Sht_Count
            Set File_Sht = Workb.Worksheets(i)
            
            'set last row and column of data.  Assume column A has full data... fair assumption for these data sets.
            Last_Rw = File_Sht.Cells(File_Sht.Rows.Count, "A").End(xlUp).Row
            
            Last_Col = File_Sht.Cells(7, File_Sht.Columns.Count).End(xlToLeft).Column
            
            Out_File = Dir & Replace(File_Sht.Name, " ", "_") & ".txt"
            
            'Out_File = Dir & Out_File & ".txt"
            
            'debug
            This_Sht.Range("a4").Value = Out_File
            
            
            'error is on this line!!
            Open Out_File For Output As #N
            
            
            For rws = 1 To Last_Rw
                out_String = ""
                For cols = 1 To Last_Col
                    out_String = out_String & File_Sht.Cells(rws, cols).Value & ";"
                Next cols
                Print #N, out_String
            Next rws
            
            Close #N
            
        Next i
        
        
        
        
    End Sub

  2. #2
    Fanatic Member
    Join Date
    Nov 2017
    Posts
    837

    Re: VBA Excel 7.1. Issue when trying to write to text file.

    I didn't test your code, but the first thing I would recommend trying is replacing #N with #1 as indicated in the bold lines below:

    Code:
            Open Out_File For Output As #1
            
            
            For rws = 1 To Last_Rw
                out_String = ""
                For cols = 1 To Last_Col
                    out_String = out_String & File_Sht.Cells(rws, cols).Value & ";"
                Next cols
                Print #1, out_String
            Next rws
            
            Close #1

  3. #3

    Thread Starter
    New Member
    Join Date
    Jun 2019
    Posts
    2

    Re: VBA Excel 7.1. Issue when trying to write to text file.

    I've sorted it.

    Change the Out_File variable to a long, then setting the

    Out_File = FreeFile

    then parsing the file name into that variable

  4. #4
    PowerPoster
    Join Date
    Dec 2004
    Posts
    24,568

    Re: [RESOLVED] VBA Excel 7.1. Issue when trying to write to text file.

    you should avoid using DIR (or other reserved words or functions) as a variable name
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width