Trouble with file destination

[EMoe]

Here's my code to save a file and place it in a certain location on c drive. I get a message "Object doesn't support this property or method"

But the file is created with the correct file name, but is placed or defaulted to "My Documents"

What's missing or out of place?

Sub Test()
Activesheet.Copy
ActiveWorkbook.SaveAs Format (Range("B1"), "mm-dd-yyyy")
ActiveWorkbok.Open "c:\ArchiveFolder.xls"
ActiveWorkbook.Close
End Sub


Thanks
EMoe

[Leith Ross]

Hello EMoe,

You are not using the Format argument correctly. Here are the code corrections


Sub Test()
Activesheet.Copy
Range("B1").NumberFormat = "mm-dd-yyyy"
ActiveWorkbook.SaveAs FileName:="c:\ArchiveFolder " & Range("B1") & ".xls", FileFormat:=xllWorkbookNormal
ActiveWorkbook.Close
End Sub

Sincerely,
Leith Ross

[EMoe]

Leith,

I don't know what's wrong.

I get a RED X with error 400.

The only thing it does is open a new workbook at the bottom of the screen, then the error message.

EMoe

[Leith Ross]

Hello EMoe,

Well, it's progress. Error 400 "Form already displayed, can't showmodally" means somewhere in your code you have UserForm.Show. Since the User Form is already displayed, executing the command again without hiding or unloading the form generates this error. This is not related to creating and saving the new workbook. Take a look in your code and remove the UserForm.Show command.

Sincerely,
Leith Ross

[EMoe]

Thanks for the speedy reply,

I'll try it, and post a reply later.

Thanks again.

[EMoe]

This was my first attempt:

Sub Test()
Activesheet.Copy
ActiveWorkbook.SaveAs Format (Range("B1"), "mm-dd-yyyy")
ActiveWorkbok.Open "c:\ArchiveFolder.xls"
ActiveWorkbook.Close
End Sub

Someone was kind enough to help me edit it to this:

Sub Test()
Activesheet.Copy
Range("B1").NumberFormat = "mm-dd-yyyy"
ActiveWorkbook.SaveAs FileName:="c:\ArchiveFolder " & Range("B1") & ".xls", FileFormat:=xllWorkbookNormal
ActiveWorkbook.Close
End Sub

The Problem with this one was, that I was getting an error 400 code. I was told that I may have some conflict with a userform, which I don't think that I do.

I tried this which does work, however it saves it as test, which it should because this is the way it's programmed.

How do I do a SaveAS filename that is in cell "C1" on the worksheet?
Everytime the user hits the archive button, it must be saved with the current date, which I have as =today() in cell "C1"

Sub test()
ActiveSheet.Copy
ActiveWorkbook.SaveAs Filename:="C:\ArchiveTestFolder\test.xls"
ActiveWorkbook.Close
End Sub

Thanks,
EMoe