Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help with my code!
I have to duplicate a file about 1500 times with unique names. I am
trying to write a macro that will automatically save the file as excel(X).xls where (X) is auto generated for as many times as I need. I am new to basic but what I have cobbled together from different posts is ... Dim X As Long Dim sFilename As String For X = 1 To 1500 With ActiveWorkbook.(X) sFilename = "http://intranet/deptSales/excel" & CStr(X) & ".xls" ActiveWorkbook.SaveAs End With Next If someone wouldn't mind telling me what have I got wrong? Thanks for the help, Steve |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help with my code!
Here's what I came up with. This takes the active open workbook and saves
it 10 times with the same name plus an incremented number into the same location. Sub Foo_ManySave() Dim objWkbk As Workbook Dim strFPath As String Dim strFName As String Dim x As Long Set objWkbk = ActiveWorkbook strFPath = "C:\Documents and Settings\UserName\Desktop\New Folder" For x = 1 To 10 strFName = strFPath & "\NewBook" & x & ".xls" objWkbk.SaveAs strFName Set objWkbk = ActiveWorkbook Next x End Sub You will, of course, have to adjust the str variables to suit your own parameters. Ed "smonczka" wrote in message oups.com... I have to duplicate a file about 1500 times with unique names. I am trying to write a macro that will automatically save the file as excel(X).xls where (X) is auto generated for as many times as I need. I am new to basic but what I have cobbled together from different posts is ... Dim X As Long Dim sFilename As String For X = 1 To 1500 With ActiveWorkbook.(X) sFilename = "http://intranet/deptSales/excel" & CStr(X) & ".xls" ActiveWorkbook.SaveAs End With Next If someone wouldn't mind telling me what have I got wrong? Thanks for the help, Steve |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help with my code!
smon,
You almost had it write...here is your code with the needed changes. You needed to remove the .(X) from ActiveWorkbook and add Filename:=sFilename at the end of the SaveAs line. So your code would look like this: Dim X As Long Dim sFilename As String For X = 1 To 1500 With ActiveWorkbook sFilename = "c:\temp\excel" & CStr(X) & ".xls" ActiveWorkbook.SaveAs Filename:=sFilename End With Next "smonczka" wrote: I have to duplicate a file about 1500 times with unique names. I am trying to write a macro that will automatically save the file as excel(X).xls where (X) is auto generated for as many times as I need. I am new to basic but what I have cobbled together from different posts is ... Dim X As Long Dim sFilename As String For X = 1 To 1500 With ActiveWorkbook.(X) sFilename = "http://intranet/deptSales/excel" & CStr(X) & ".xls" ActiveWorkbook.SaveAs End With Next If someone wouldn't mind telling me what have I got wrong? Thanks for the help, Steve |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help with my code!
Wow you mean I really wasn't that far off? Maybe I am actualy learning
something. :) Thanks to both of you for your posts, I was afraid I was going to have to do this maualy. Thanks again. Steve |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
split post code (zip code) out of cell that includes full address | Excel Discussion (Misc queries) | |||
Code to conditional format all black after date specified in code? | Excel Discussion (Misc queries) | |||
Drop Down/List w/Code and Definition, only code entered when selec | Excel Worksheet Functions | |||
stubborn Excel crash when editing code with code, one solution | Excel Programming | |||
VBA code delete code but ask for password and unlock VBA protection | Excel Programming |