Dynamic File Naming In Excel

[mordja]

Hi,

Im having problems getting my code to create and dynamically name and save workbooks. Ive included some of the following code, leaving out the unecessary workbook operations.


Dim path, extension, name As String

Application.ScreenUpdating = False

path = "G:\Izac\"
extension = ".xls"

For i = 1 To valueCount
'Create New Workbook and Set Sheet Names
Set newWB = Workbooks.Add
newWB.Worksheets.Item(1).name = ("Summary")
newWB.Worksheets.Item(2).name = ("Summary With Line")
'Set Path And Save
name = path & TLnames(i) & extension
newWB.SaveAs (path)
Next i

End Sub


TLnames is a global variable with a set of names. Ive checked that name is as it should be both in lenght and content.

I get a run time error 1004.
"The file could not be accessed"

Any ideas as to what Im doing wrong.

Thanks

Izac

 

[nnett]

Hi,

If you add "newWB.Worksheets.Add" between the two lines renaming the worksheets your code should work, you may need to change the second line to "newWB.Worksheets.Item(1).name = ("Summary With Line")" as the new sheet will become item 1

Hope this helps

 

[mordja]

all,

figure it out myself, pretty silly.

 

[mordja]

nnet,

No that wasnt the problem. By default a workbook opens with three sheets and I was simply renaming two of them. The problem seemed to be that I was doing newWB.SaveAs(path)
instead of newWB.SaveAs FileName:=Path.

Thanks

 

[PHV]

Shouldn't be this:
newWB.SaveAs FileName:=Path
replaced by this ?
newWB.SaveAs FileName:=name

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244