PDA

View Full Version : Export to file with different extensions



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

werafa
10-02-2018, 08:41 PM
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