Consulting

Results 1 to 2 of 2

Thread: Export to file with different extensions

  1. #1
    VBAX Newbie
    Joined
    Oct 2018
    Posts
    1
    Location

    Export to file with different extensions

    Hi,

    I have cobbled together the below code which outputs a file from the data in my cells

    It works fine, but i want it to output 14 files, all with different file extensions
    I have the file extensions in a table on a different sheet

    Currently the file extension is referring to the first cell in this table, which i have under MyExtension = Worksheets("Data").Range("B3")

    Apologies if this is confusing in any way, as i say im very new to VBA


    Sub WriteToTextFile()
    
    'Declarations
        Dim iLastRow As Long
        Dim iLastCol As Long
        Dim myPathTo As String
        Dim Filename As String
        Dim MyFilename As String
        Dim MyExtension As String
        Dim i As Integer
    'Declarations End
    
        iLastRow = Range("A" & Rows.Count).End(xlUp).Row
        iLastCol = Cells(1, Columns.Count).End(xlToLeft).Column
        myPathTo = Worksheets("Table").Range("C2")
        MyFilename = Worksheets("Table").Range("C10")
        MyExtension = Worksheets("Data").Range("B3")
        Filename = myPathTo & MyFilename & MyExtension
        
        Open Filename For Output As #1
            For i = 1 To iLastRow
                For j = 1 To iLastCol
                    If j <> iLastCol Then 'keep writing to same line
                        Print #1, Cells(i, j),
                    Else 'end the line
                        Print #1, Cells(i, j)
                    End If
                Next j
            Next i
            'MsgBox "Failed to transfer " & iFail & " file(s).", iFail & " Transfer(s) Failed"
        Close #1
    
    End Sub

  2. #2
    VBAX Mentor
    Joined
    Aug 2012
    Posts
    351
    Location
    are you changing file formats as well?

    can you do this via the 'save as' dialogue?
    if so, you can use vba to 'save as'.

    if not, are you trying to generate name1.aaa.xlsx type formats?
    if so, again you can use the save as commands.

    can you give an example of what you are after?
    Werafa
    Remember: it is the second mouse that gets the cheese.....

Posting Permissions

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