Create, Copy & Name a file using VB 1

[Firery]

Using MS Office XPPro
I have a excel WorkBook with numerous sheets,
One sheet "Summary" displays a summary of information
from the other sheets by way of formula's.
1. I wish to save the worksheet "Summary" as a new xls file with only the values and formats displayed, no links to the original.
2. I also wish to have the new file named using the current date when it was saved. eg "21-3-04.xls" or using a value from a cell in the worksheet.

I've searched for hours and tried combining different bits of code with only partial success.
I'm sure this is simple to most of you, but I would appreciate the help.

 

[tedsmith]

I presume you are using VB to do this:
Create a new spreadsheet
Open your old worksheet
You can get the values out of a range of cells from your old worksheet and put them in the blank spreadsheet
Search in help for "range" "workbook" there is a lot of info and examples on it.
It might be harder to also save the formats and formulae but it can be done
Best to use Year, month day for Filenames as it sorts into order in the folder correctly that way
Filename=Format(date,"yyyy_mm_d") & ".xls
You can copy and rename etc using filesystemobjects (read help). You have set project references to get this and Excel to work as per help .

 

[jcrtektip]

See if this helps....

applfile = "c:\oldfile.xls"
mmonth = "dd-mm-yy"
'you have to create this string from the values or date

savefile = "c:\ mmonth + ".xls"

finalsave = mmonth + ".xls"

Workbooks.Open applfile
Set datasheet = Worksheets("sheet1")
'you can give your sheet name

Sheets("sheet1").Select
datasheet.SaveAs savefile
Workbooks(finalsave).Close savechanges:=True

'Acctually you open the excel file and save the file (save as)

 

[johnwm]

I find the easiest way with Excel is to record a macro, then copy and paste the code obtained. If you pasting into VB6 rather than VBA under Excel you need to set the reference to the XL application object first of course.

I recorded this quickly to do what you want (or fairly close)

Sub Macro2()
Sheets("Summary").Select
Range("C6:C13").Select
Selection.Copy
Workbooks.Add
Range("A12").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\John M\My Documents\" & Format(Date, "dd-mm-yy") & ".xls", FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup _
:=False
End Sub

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'If we're supposed to work in Hex, why have we only got A fingers?'

 

[Firery]

Thanks all for your support.
johnwm made it the simplest.
I have recorded a few macro's to see how the code reads.
Coping the data wasn't the problem, creating a file with the date as the name was the hard one.
Now how do I name a file using the value of a cell,
eg. cell a1="2 Mar 04" (fixed date)
File name saved as "2 Mar 04.xls"

 

[johnwm]

Just change the string in the SaveAs line, something like:

ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\John M\My Documents\" & Sheets("Summary").Range("A1").Value & ".xls", FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup _
:=False

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'If we're supposed to work in Hex, why have we only got A fingers?'