MrExcel Publishing
Your One Stop for Excel Tips & Solutions

HELLLLLLLLPPPPPPPPPPPPPPPPPPPPPPPPPPPPP!


Posted by KEVIN ROWE on November 15, 2001 6:05 AM

Hello

I am desperately seeking help with VBE and I was hoping you could help me.

I am doing a project in Excel for A level coursework and am totally stuck and have looked everywhere for an answer. My teacher is not a programmer and does not knopw much about Visual Basic or VBA.

I have a worksheet with 11 in cell A1

in vba i have

Sub Button1_Click()
Set newBook = Workbooks.Add
With newBook
.Title = "date"
.Subject = "New Sheet"
.SaveAs Filename:="u:\helloo.xls"

End With
End Sub

This saves a new file when button is clicked which is great but I want the filename to be whatever is in cell A1 so if it is still 11 it would save it as u:\95krowe1\11.xls

Then as i change the cell A1 and press the button that changes too - so if i put 345 in A1 the file will be saved when clicked as

U:\95krowe1\345.xls

If you cant help please could you tell me where I can get help

Also please could you reply @ my home address
kanu25@btinternet.com

Thank You

Kevin Rowe

Treorchy Comprehensive School


Posted by Dank on November 15, 2001 6:09 AM


Posted by Dank on November 15, 2001 6:10 AM

Ignore previous post!


ActiveWorkbook.SaveAs Filename:="U:\95krowe\" & [A1]

Posted by Rick E on November 15, 2001 6:14 AM

Use a variable for the file name.

Define a variable and build the name you want. See added code below:

Sub Button1_Click()
Dim newName as String
Set newBook = Workbooks.Add
newName = "u:\95krowe1\" & Range("A1").Value & ".xls"
With newBook
.Title = "date"
.Subject = "New Sheet"
.SaveAs Filename:= newName

End With
End Sub

This should do it. Good Luck