How to call a click button which is on another spread sheet in your MACRO

[kbriaz]

Hi All,

Sorry, a very noob question here.

My macro for spead sheet called test0

1) Open a different spead sheet ( test1 )

2) Click the relevent sheet (sheet1)

3) Press a button which is on the spead sheet ( on test1 )

4) Save as test1 as test_final.

Macro does not record me clicking that button !

I don't know how else can this be done. I don't know if this can be done or not ! sorry if i didn't explain it properly

Thank you for your help in advance - ta

[Debraj Roy]

Can you please post the recorded macro..

[kbriaz]

Sub New_Q_ALL()
'
' New_Q_ALL Macro
'

'
Workbooks.Open Filename:= _
"K:\A & A\test1.xlsm"
Windows("test1.xlsm").Activate
Sheets("sheet1").Select
Application.Run RefreshData_Adhoc
ActiveWorkbook.SaveAs Filename:= _
"K:\A & A\test_final.xlsx" _
, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
End Sub

[kbriaz]

Furthermore, under test1 spread sheet there is a button which i want to press. Below is code linked to that button

button is called cmdAdhocRun_Click()
which Calls RefreshData_Adhoc

ta

[Debraj Roy]

Try this..

Please Login or Register  to view this content.

[kbriaz]

@ Debraj Roy you sir are amazing. many thanks. it works.

Another quick problem, Test1 is a macro enabled spead sheet, when i save it as, i save it as a non macro workbook.

Which creats a popup saying you sure you want to change type. How can i automate that 'yes' click.

Macro doesn't seem to record that action !

Thanks again

[Debraj Roy]

Try this..

Please Login or Register  to view this content.

[kbriaz]

Sorry, it didn't work.

I Want to automate saving of a macro to non-macro workbook

sorry

[Debraj Roy]

OK.. then try this..

Please Login or Register  to view this content.

[kbriaz]

I get the 'object doesn't suppost this property or method' error.

sorry to trouble you mate.

p.s its the 'macro free workbook' pop up which i want to get rid off. it always asks for me to press 'Yes' or no - ta

[kbriaz]

Got it. Thanks for your help mate

Sub Test()
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:="C:\Test Folder\Test.xlsx", _
FileFormat:=xlOpenXMLWorkbook
Application.DisplayAlerts = True
ActiveWorkbook.Close
End Sub

[Debraj Roy]

Glad that you solved it..