View Full Version : Solved: file generation + naming from a variable
sleepy_think
05-27-2008, 11:17 AM
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!
mdmackillop
05-27-2008, 12:30 PM
Sub test()
Dim cel as range
For Each cel In Range("C1:C5")
Workbooks.Add
ActiveWorkbook.SaveAs "C:\AAA\" & cel & ".xls"
Next
End Sub
sleepy_think
05-29-2008, 10:44 AM
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?
sai_golden
05-29-2008, 12:09 PM
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.