Use open spreadsheet file instead of opening and closing the file in vba

[ccondran08]

Hi, hoping someone can help me out here as it would save me hours of work. I have this macro below that I am using which is used in Powerpoint to call a macro in excel which is working perfectly fine. The macro will open the excel file and perform some actions and then save and close the file. I would like to modify this code so that i can have the excel file open to start off with and then run the macro but not to save or close the file so i continue to the run the macro several times. Other wise this will open and close the file all the time on the macro whereas i would like to leave the file open and toggle between the files to see the results of the macro.


Sub ImportData()


Dim oXL As Excel.Application ' Excel Application Object
Dim oWB As Excel.Workbook ' Excel Workbook Object
Dim sPName As String ' Variable - Active Presentation Name


On Error GoTo Err_PPXL


' -----------------------------------------------------------
' coded by Shasur for http://vbadud.blogspot.com
' -----------------------------------------------------------


Set oXL = New Excel.Application
Set oWB = oXL.Workbooks.Open("C:\Test.xlsm")


' Set Excel as Visibile - Turn Off if not needed
oXL.Visible = True


' Pass and Argument
sPName = ActivePresentation.Name


' Run the Macro without Argument
oXL.Application.Run "'Test.xlsm'!getshapedata"




' Save and Close the Workbook
oWB.Save
oWB.Close (False)




' Quit the Excel
oXL.Quit




' Release Objects - Good Practive
If Not oWB Is Nothing Then Set oWB = Nothing
If Not oXL Is Nothing Then Set oXL = Nothing




Err_PPXL:
If Err <> 0 Then
MsgBox Err.Description
Err.Clear
End If






End Sub

 

[jondavis1987]

What happens if you just delete the line of code that says

[COLOR=#333333]' Save and Close the Workbook[/COLOR]
[COLOR=#333333]oWB.Save[/COLOR]
[COLOR=#333333]oWB.Close (False)[/COLOR]




[COLOR=#333333]' Quit the Excel[/COLOR]
[COLOR=#333333]oXL.Quit[/COLOR]

 

[jondavis1987]

You also may need to make a second macro that has that code deleted and the portion of code that's about opening excel as that may cause problems