Saveas Not Working

[ehale]

Hello,

I'm having a problem with SAVEAS in following code. Basically trying to Add
a new Workbook (wb2) so that I can move sheets to it from an existing
workbook (wb1). When running, I get the dialogue box asking me to save as in
the correct directory, and it seems to save, but at the end of the routine
the file doesn't exist in the directory. When I step through the code, I can
see at the point right after I get the SaveAs Dialogue box (wb2.SaveAs
ChosenFileName, xlWorkbookNormal) that the workbook still has it's original
default name assigned by the Workbooks.Add command (Book1.xls, or whatever it
is). Not sure how to troubleshoot this . . . I don't get any error.

Any help is very appreciated. Thanks in advance.

Dim CurrentPath As String
Dim Filename As Variant

'Store the current path
CurrentPath = CurDir

'Change the path to the one we want
SetCurrentDirectory "\\SRV1\RATER\Attach PIC Rater"


'setting activeworkbook (self-rater) as wb1
Set wb1 = ActiveWorkbook
wb1Name = ActiveWorkbook.Name


'Adding a new workbook to move Quote tab to
Dim wb2 As Workbook
Set wb2 = Workbooks.Add
tamfile = InputBox("Enter your Prospect Code", "Save As Prospect Code",
wb1Name)

ChosenFileName = Application.GetSaveAsFilename(tamfile, "Excel Files
(*.xls),*.xls,", , "Title")
wb2.SaveAs ChosenFileName, xlWorkbookNormal

 

[joel]

I ran this porftion of your code and it worked perfectly
-----------------------------------------------------------------------------------
'Adding a new workbook to move Quote tab to
Dim wb2 As Workbook
Set wb2 = Workbooks.Add
tamfile = InputBox("Enter your Prospect Code", "Save As Prospect Code",
wb1Name)

ChosenFileName = Application.GetSaveAsFilename(tamfile, "Excel Files
(*.xls),*.xls,", , "Title")
wb2.SaveAs ChosenFileName, xlWorkbookNormal

-----------------------------------------------------------------------------------------

I would start by trying to save the file to your local c:\folder and see if
the same thing happens. Don't change any code just select the C:\drive and
save the file in a location you know you have sacved filesd befoire. I think
the code is working bt the file is going to another folder.

Another way of proving this is to go to the new workbook after your executed
the SAVEAS and using the menu

File - Saveas

Look at the full default folder wherer the file is being saved bu pressing
the arrow in the pull down folder menu at the top of the SAVEAS window.

 

[Barb Reinhardt]

You may want to take a look at this site. In Excel 2007, you need to have a
fileformat that matches the file extension or you get wierd results. I've
done it and files seemed to save, but wouldn't open later.

http://www.rondebruin.nl/saveas.htm

 

[Barb Reinhardt]

You may want to take a look at this site. In Excel 2007, you need to have a
fileformat that matches the file extension or you get wierd results. I've
done it and files seemed to save, but wouldn't open later.

http://www.rondebruin.nl/saveas.htm

 

[Dave Peterson]

I'd guess that either the folder wasn't what you wanted when the user ok'd the
getfilesaveasname dialog or you didn't notice the full name of the workbook:

I'd add some msgboxes just to help with the debugging:

Dim ChosenFileName As Variant 'could be false if the user cancels
....

ChosenFileName = Application.GetSaveAsFilename(InitialFileName:=TamFile, _
filefilter:="Excel Files ,*.xls,", Title:="Save As")

If ChosenFileName = False Then
'user hit cancel
'what should happen
Else
MsgBox ChosenFileName
wb2.SaveAs Filename:=ChosenFileName, FileFormat:=xlWorkbookNormal
MsgBox wb2.FullName
End If

 

[ehale]

Hi Joel,

Thanks for your response. I tried saving to C drive with the same results.
It actually sometimes saves the file fine but other times doesn't (should
have mentioned that in original post). When I see the SaveAs dialogue box, I
can see all the other files in the folder that have been sucessfully saved
via the same routine so I know the path is correct. I click ok after
entering the desired file name, but the file simply retains the generic
"Book1.xls" name and isn't saved in the folder. No error.

I decided to change the path of the file from the absolute path //srv01 . .
.. to R:/Attach . . .

I also moved the saveas to the end of the routine. So far all my tests have
been sucessful, but I'll see what happens tomorrow when users give it a try.
Not too optimistic since I still don't know what the problem is!

Thanks again,
Erica

 

[ehale]

Thanks Barb -- using older version of Excel.

You may want to take a look at this site. In Excel 2007, you need to have a
fileformat that matches the file extension or you get wierd results. I've
done it and files seemed to save, but wouldn't open later.

http://www.rondebruin.nl/saveas.htm

 

[ehale]

Hi Dave,

Thanks for responding -- message boxes are a good idea.

The Saveas worked initially, then one user started having trouble and
suddenly multiple users (including me) had trouble. Now, It's back to
working with identical code and message boxes are confirming path and all are
correct. When the problem returns, which I'm guessing it will, I will use
the message boxes to see what's going on.

Thanks,
Erica