Excel Help Part 2

[bowers01]

Hi,
For my project i have another hurdle. I need to make a button that will open save as, is this possible?

If it is then theres a second question.
I currently have the date in a cell that auto updates. Is it going to be possible to make it save as the date or what ever is in the certain cell?
Any help would be great.

Maybe Mike the Excel Counselor could help me again

Cheers,
Nick

[bowers01]

Hi,
I have worked out the following:

Sub Button6_Click()
ThisWorkbook.Save
ThisWorkbook.Close
End Sub

But i still need to make it save as a certain name, is there a command to put on the end of ThisWorkbook.Save to make it save as a field name?
Cheers,
Nick

[objelland]

Hi
You can try this:

Sub SaveAsCell()

Dim strName As String



On Error GoTo InvalidName

strName = Sheet1.Range("A1")

ActiveWorkbook.SaveAs strName



Exit Sub

InvalidName: MsgBox "The text: " & strName & _

" is not a valid file name.", vbCritical, "Ozgrid.com"

End Sub

[bowers01]

Hi,
I did that and get this error:
http://everythingsfree.org/image//im...18792excel.jpg
Any idea why?
Cheers

[bowers01]

Hi,
I also found this code. It seems to do the same thing but i get an error saying "Compile error" http://everythingsfree.org/image//im...5509excel2.jpg

Sub Button1_Click()

ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\" & strName
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Sheets("Cat Nos").Visible = True
Sheets("Supplier Info").Visible = True
Sheets("Codes").Visible = True
Sheets("Buyers").Visible = True
Sheets(Array("Data Sheet", "Sup No Lookup", "Buyer lookup", "Cat Nos", _
"Supplier Info", "Codes", "Buyers")).Select
Sheets("Buyers").Activate
Application.CutCopyMode = False
ActiveWindow.SelectedSheets.Delete


Dim strName As String

On Error GoTo InvalidName
strName = Sheet2.Range("F14")
ActiveWorkbook.SaveAs strName

Exit Sub
InvalidName: MsgBox "The text: " & strName & _
" is not a valid file name.", vbCritical, "Ozgrid.com"
End Sub

Any idea why this might not work? or ideas on how to fix the post above?
Cheers,
Nick

[objelland]

I cant see why you are getting the error message, but I can see that you are refering to the variable strName in the first line of your code, without declaring it. You are declaring the variable at line 18 or so (Dim strName As String). Move that line to the top of your code! That might help!

Cheers!

[bowers01]

Hi,
Thanks for the help but i get another error.
http://everythingsfree.org/image//im...58110excel.jpg
Any ideas?
Nick

[Mike-N-Tosh]

It would seem to be that since you are using the current file path and then concatenating "\" & the string name then it is expecting to find a directory with the name of the "stringname"

example:
current file path = mycomputer\documents\excel\myproject

New file path in your code = mycomputer\documents\excel\\newfilename

Try getting rid of the extra "\" OR create another directory for the save as files and include that after the "\" & "mysavedprojectfiles" & ...

-Mike