Excel A couple of toughies...

I've created toolbar button that opens a workbook that, when it opens, a form pops up. In the form I enter the name of a ftp file and click a start button that imports the file. then a series of macros run that edit, copy, paste, etc. into the workbook opened by the toolbar button. (follow that?) after everything's done the form closes and up pops a saveas box with the appropriate name in it.

Here's the problems: If I choose cancel on the saveas box, the box closes and ALL OF THE DATA THAT WAS IMPORTED INTO THE NEW BOOK IS CLEARED OUT!! The importing and editing takes about 10 minutes due to the amount of data in the ftp file. i know a simple solution would be not to cancel out of the saveas box, but the users for this 'application' want to be able to cancel out in order to review the document before saving it! (picky people!)

And: if saveas isn't canceled out of, the file saves fine. But, the macro assigned to the toolbar button is changed causing it to not work! The original macro for the button opens the file my network that imports the ftp file, etc... After saving, the toolbar button macro now tries to open the new file! I've tried adding a hyperlink to the button instead of using a macro to open the import file, but it keeps giving me an error saying it can't find the specified file. I know that has something to with trying to pull the file from the network server, which is why I used the macro to open the book instead...

anyway, i could really use some help on this one; especially the one that's clearing all data...

thanks

Cory

I was thinking that I could put the open macro in the Personal.xls macro holder so it'd be available whether or not a book was open in excel. Am I thinking right and if so how do I recreate the Personal.xls macro holder (as mine's been deleted)

Cory

Hi Cory,

I believe you are using the built-in SaveAs dialog
called by:

Application.Dialogs(xlDialogSaveAs)

to save your workbook. I believe that if you switch to using the

filename = Application.GetSaveAsFilename(...

you will gain control of the Cancel button and not
lose your imported data when you run it. This is because this function only retrieves the file name and you have to do the subsequent SaveAs explicitly, so the SaveAs doesn't occur unless you want it to. The code would look something like:

Dim filename As Variant
filename = Application.GetSaveAsFilename(...

If filename <> False Then

ActiveWorkbook.SaveAs fname:=filename

EndIf

Which only saves the file if the user doesn't cancel.

I didn't understand your second question. Can you explain why the toolbar button macro code is changed? What changed it, and what did it change to?

Damon

Thanks for responding. About the first part (saveas), I have been using what you suggested, which leads me to believe I've just been using it inappropriately. Here's the code (maybe by seeing it you could help point out the error):

Sub SaveAs()
Dim response
Dim Fs As String
response = MsgBox("Would you like to save before exiting?", vbYesNo, "Exiting PPSAR 2.1...")
'if 'no' then exit and close
If response = vbNo Then
frmMain.Hide
ActiveWorkbook.Close
Application.DisplayAlerts = True
Exit Sub
'here's where it saves...
Else
Application.DisplayAlerts = True
Fs = Application.GetSaveAsFilename("PPSAR " & Sheet1.Range("i1").Text & " 2001", fileFilter:="Microsoft Excel File (*.xls), *.xls")
'i think this is where the problem is...
If Fs = "False" Then
frmMain.Hide
Cancel = True
Else
ThisWorkbook.SaveAs Fs
Application.DisplayAlerts = False
ActiveWorkbook.Close
frmMain.Hide
Exit Sub
End If
Application.DisplayAlerts = True
End If
End Sub

And about the second part, i customized a 'macro' toolbar button and added it to my format toobar. I then assigned a macro to it that is supposed to open a workbook i created. works fine the first time, evrytime. here's a sample of the code:

Sub OpenPPSAR()
Application.DisplayAlerts = False
ChDir "U:\hpurc2\windows\Personal\Product Parameter Skipped Audit Report"

Workbooks.Open Filename:= _
"U:\hpurc2\windows\Personal\Product Parameter Skipped Audit Report\PPSAR.xls"
End Sub

First it changes directories to where the book is located. then it opens that book (PPSAR.xls).
what that book does is pop up a tiny form where i enter the name of another file and hit a 'start' button. that file is then imported, edited, and finally saved as a new book using the first code i gave you above. like i said, works great the first time...
Problem: after pressing the button on the toolbar and running that whole process, the workbook gets saved under a different name (which is how i need it so i can keep the original file as a template...). once the new file has been saved and everything has ended and been closed out, pressing the button on the toolbar causes excel to try and open the NEW file i saved, NOT THE ORIGINAL it's supposed to. Unless I reassign the old macro to it, it doesn't work! (follow that?)

And, last but not least (totally different problem), if i use the button to run the process, after everything's done, saved, and closed : the button automatically tries to run itself again! it's acting like once i press the button, it gets caught in a loop and keeps trying to run the whole process over and over again.

Thanks again!

Cory

Hi Cory,

First problem: Fs should be declared As Variant, because it has to be able to accept either a string value (the file name) or a boolean value (True/False, which is not the same as the string "True" or "False"). So your If test should also be changed to:

If Fs = True Then ...

Regarding the second problem, I can't see how the button can get assigned to a different macro. Have you examined the button to see what macro it is assigned to after the problem occurs? You can check this by right-clicking on the button while in Customize mode, and selecting Assign Macro. In addition, you should put a debug breakpoint
at the beginning of the OpenPPSAR procedure so that you can watch what happens when you click the button, and find out if it is getting executed. Since the filename is explicitly named, complete with path, I can't see how this macro can go wrong.

The third problem I believe is a bug in Excel. I have experienced it myself, and have never found a solution. I have not checked the Excel known bugs list at the microsoft.com website for this, but I would bet it is there. There may even be a workaround given.

Damon

Cory, does this help?

Sub SaveME()
Dim response As String
Dim Fs As Variant

response = MsgBox("Would you like to save before exiting?", vbYesNo, "Exiting PPSAR 2.1...")

If response = vbNo Then
ActiveWorkbook.Close False
Exit Sub
Else
Fs = Application.GetSaveAsFilename("PPSAR " & Sheet1.Range("i1").Text & " 2001", fileFilter:="Microsoft Excel File (*.xls), *.xls")
If Fs <> False Then
ActiveWorkbook.Close True
End If
End If

frmMain.Hide
Exit Sub

End Sub

Archive of Mr Excel Message Board

A couple of toughies...

Or, how do you recreate the Personal.xls file again?

Re: A couple of toughies...

Damon!

Re: Damon!

Re: Damon!

Check out our Excel VBA Resources