gereesna
10-01-2018, 08:04 PM
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
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