Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

save as help 1

Status
Not open for further replies.

Pandab2002

IS-IT--Management
Apr 16, 2003
47
US
Hi. I have an excel spreadsheet the has 132 rows of information. I am trying to come up with a way to use the data in column "I" as "save as" file names for another spreadsheet that is open. In other words, I want to select the contents of "I2", do a CTRL-C (to copy), the select the other workbook, do a ALT-F-A (to save as), do a CTRL-V (to paste the new save name). And finally, send a "S" to save. Then I want to repeat the whole process 131 more times, stepping down to "I3" then "I4" and so on. Any suggestions?

Thank you
Paul...
 
I'd use automation instead of SendKeys ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
something along the lines of:
Code:
Sub SaveOtherBooksAs()

Dim x As Integer

For x = 2 To 132
    With Workbooks(x - 1)
        .SaveAs ThisWorkbook.Sheets(1).Cells(x, 9)
        .Close
    End With
Next

End Sub

The code needs to be in the workbook that has the list in column I.

How will you know the name of the workbook that needs to be saved as the value in Ix though? Are the workbooks in the same order as the values in column I? (my code assumes that the wb with the list to use is wb 1 and the rest is to be saved in the same order as the values in column I)

This hasn 't been tested by the way.

Cheers,

Roel
 
Thanks Roel,

The contents of column "I" are a 3 digit code and an airport code. "010_MCI" for example. I have two spreadsheets open, the one with the 132 rows containing the information above, and another spreadsheet, which I want to do the save as command 132 times. In the end, I want 132 spreadsheets with unique names. I can do this manually but it is time consuming and I have to do it weekly.

Paul
 
And what have you tried so far ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
PHV,

I clicked record and did everything manually for 10 saves. It works, but there must be an easier way.

Paul
 
And what is the recorded code for one save ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
try this
Code:
    Dim folder$
    Dim FileName
    folder$ = "C:\"
    
    row = 1
    
    Do While (Cells(row, 9) <> Null)
        FileName = Cells(row, 9).Value
        Workbooks("WorkbookName").Activate
        ActiveWorkbook.SaveAs FileName:= _
            folder & FileName, FileFormat:= _
            xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
            , CreateBackup:=False
        Workbooks("WorkbookName2").Activate
        row = row + 1
    Loop
and replace WorkbookName with the name of the workbook your are saving ,WorkbookName2 with the name of the workbook you are getting the data from and C:\ with the folder you want to save the files in
 
Thanks Fr33dan. I mad a couple modifications and it works like a charm. Here is the finished product.

Dim folder$
Dim FileName
Dim WorkbookName
WorkbookName = "xyz.xls"
folder$ = "C:\Documents and Settings\Administrator\"
Workbooks.Open folder & WorkbookName
Workbooks("abc.xlsx").Activate
Row = 2

Do While (Cells(Row, 1) <> "end")
FileName = Cells(Row, 1).Value
Workbooks(WorkbookName).Activate
ActiveWorkbook.SaveAs FileName:= _
folder & FileName, FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False
Workbooks("abc.xlsx").Activate
Row = Row + 1
WorkbookName = FileName + ".xls"
Loop



I moved all the data to column A. For some reason the "Do While (Cells(Row, 1) <> Null)" did not work so I added "end" to the last cell and used "Do While (Cells(Row, 1) <> "end"). It works perfectly. Thanks again.

Paul
 
To avoid all those Activate:
...
Workbooks("abc.xls").Activate
Row = 2
Do While (Cells(Row, 1) <> "end")
FileName = Cells(Row, 1).Value
Workbooks(WorkbookName).SaveAs FileName:= _
folder & FileName, FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False
Row = Row + 1
WorkbookName = FileName & ".xls"
Loop

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top