Solved: file generation + naming from a variable

Hey all,

what would be command to create a file using a variable from an array?

example:

range (c1:c5) holds information like : data.xls. datz.xls, etc...

need to know how to open\create a file (including location)...
any ideas?

Thanks!

[VBA]Sub test()
Dim cel as range
For Each cel In Range("C1:C5")
Workbooks.Add
ActiveWorkbook.SaveAs "C:\AAA\" & cel & ".xls"
Next
End Sub
[/VBA]

Hi mdmackillop,

Thanks, I should have added that I dont want to see the screens flying by (there is alot of cells to go through in the actual script) heres what I got so far:

Dim Filename As String
Dim aduserid As String
Dim secgroup As String
Dim userid As String
Dim Resname As String
Dim WS As Worksheet


destinationpath = "c:\test\"

Set WS = Worksheets("Lookup")

For Each cell In WS.Range("L2:L50")

aduserid = cell.Value
userid = cell.Offset(0, -9).Value
secgroup = cell.Offset(0, -2).Value
Resname = cell.Offset(0, 7).Value


Filename = destinationpath & Resname

Dim OutputFile As String

OutputFile = Filename

If Len(Dir(OutputFile)) Then
Kill OutputFile
End If

' Do While cell <> ""

Open Filename For Output As #1

Write #1, "blah blah blah"

Close #1

Next cell

' Loop

End Sub

I am having an issue with when it hits a blank cell , it gives me file not found and crashes out. I was trying to see about a do hile statement to bypass the blank cell issue... any ideas?

Hi ,

Try using an if condition like:

For Each cell In WS.Range("L2:L50")
if cell.value<>"" then
aduserid = cell.Value
userid = cell.Offset(0, -9).Value
secgroup = cell.Offset(0, -2).Value
Resname = cell.Offset(0, 7).Value


Filename = destinationpath & Resname

Dim OutputFile As String

OutputFile = Filename

If Len(Dir(OutputFile)) Then
Kill OutputFile
End If

' Do While cell <> ""

Open Filename For Output As #1

Write #1, "blah blah blah"

Close #1
else
endif
Next cell