Errormessage when button is pressed twice or more
 

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

Errormessage when button is pressed twice or more
 

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" wrote in message
...
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
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 String
Const sPath = "E:\test\"
WBName = ActiveWorkbook.FullName
ActiveWorkbook.Save
Application.DisplayAlerts = False
Sheets("transfer").SaveAs Filename:=sPath & "Convert.csv",
FileFormat:=xlCSV
Application.DisplayAlerts = True
Workbooks.Open WBName
ThisWorkbook.Close savechanges = no
Shell (sVerzeichnis & "convert.exe")
End Sub

Errormessage when button is pressed twice or more
 

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.

Errormessage when button is pressed twice or more
 

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" wrote in message
...
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.

Errormessage when button is pressed twice or more
 

Thank you very much! That's exactly what i've been searching for!