Errormessage when button is pressed twice or more

P

Pointerman

Hi

I created a new button in the Toolbar.
Then I connected the button to my macro.
When I press the button for the first time everything works just fine
Everything to this point happened in document.xls

When I press the button a second time I get a Message that Excel can't find macro file.csv!convert.

The macro does these things
1. it stores the name of the active workbook (which is "document.xls"
2. it saves the active workbook (which should also be "document.xls"
3. it saves a sheet of the workbook as "convert.csv
4. it reopens the file stored in (1) because "convert.csv" is the aktive dokumen
6. it closes ThisWorkbook (Funny but it is still "convert.csv"
5. it calls an extern EXE-File that works with "convert.csv" and finally deletes it (But it doesn't delete it now! It needs user Input!

P.S.: It only happens when the macro is called via a button. Using "run Macro" several times works fine


Here is the code
Sub Konvertieren(
Dim WBName As Strin
Const sPath = "E:\test\
WBName = ActiveWorkbook.FullNam
ActiveWorkbook.Sav
Application.DisplayAlerts = Fals
Sheets("transfer").SaveAs Filename:=sPath & "Convert.csv", FileFormat:=xlCS
Application.DisplayAlerts = Tru
Workbooks.Open WBNam
ThisWorkbook.Close savechanges = n
Shell (sVerzeichnis & "convert.exe"
End Sub
 
T

Tom Ogilvy

I assume the macro is in document.xls

When you change the name of the document.xls to convert.csv then the
reference in the button is changed to point to convert.csv.

You will need to add code that changes the onaction property of the button
to reference document.xls.

--
Regards,
Tom Ogilvy


Pointerman said:
Hi!

I created a new button in the Toolbar.
Then I connected the button to my macro.
When I press the button for the first time everything works just fine.
Everything to this point happened in document.xls.

When I press the button a second time I get a Message that Excel can't find macro file.csv!convert.

The macro does these things:
1. it stores the name of the active workbook (which is "document.xls")
2. it saves the active workbook (which should also be "document.xls")
3. it saves a sheet of the workbook as "convert.csv"
4. it reopens the file stored in (1) because "convert.csv" is the aktive dokument
6. it closes ThisWorkbook (Funny but it is still "convert.csv")
5. it calls an extern EXE-File that works with "convert.csv" and finally
Click to expand...
deletes it (But it doesn't delete it now! It needs user Input!)
P.S.: It only happens when the macro is called via a button. Using "run
Click to expand...
Macro" several times works fine.
 
P

Ponterman

Hi Tom
thanks for your fast reply
I even started to solve that problem by code but stuck again (My main task was programming that exe-file in Delphi and I'm a total newbie in VBA).
I found an example where they change the OnAction event by code. They use FindControl with the button's ID but i have no idea where I should get the ID. I just created the Button and connected it to the macro. There was no way of giving it an ID

It would be very nice of you to get me another hint.
 
T

Tom Ogilvy

Well, you should know where the button is since you created it - you
shouldn't need to "Find" it. Just refer to it directly. However, since you
want to refer to it in the macro triggered by clicking it, you can use the

set ctrl = CommandBars.ActionControl
' code to create convert.csv
' code to reopen documents.xls
ctrl.OnAction = "Documents.xls!MyMacro"

--
Regards,
Tom Ogilvy



Ponterman said:
Hi Tom,
thanks for your fast reply!
I even started to solve that problem by code but stuck again (My main task
Click to expand...
was programming that exe-file in Delphi and I'm a total newbie in VBA).
I found an example where they change the OnAction event by code. They use
Click to expand...
FindControl with the button's ID but i have no idea where I should get the
ID. I just created the Button and connected it to the macro. There was no
way of giving it an ID.
 
Top