'Edit Module' popping up upon Macro Execution

[mrthomasshelby]

Hello,

I have incorporated a macro written by tamilarasu to export all tables in a sheet in one of my apps which I found in this link. The macro is exporting the tables to excel fine but after running, the 'Edit Module' box keeps popping up. I have read in some threads that this is caused due to some error in the macro. Please help me to identify if there's any mistake in the macro that I'm using:

sub exportToExcel 


'==============================================================
' File Path & Name


 Path = "C:\temp\"
 FileName = "Test_"
 strSaveFile = Path & FileName
'==============================================================
'Open Excel


set oXL=CreateObject("Excel.Application") 
oXL.visible=True 
oXL.Workbooks.Add


aSheetObj=Array("CH391","CH392","CH393","CH394","CH395")  ' Chart ID's here


'==============================================================
for i=0 to UBound(aSheetObj)
 Set oSH = oXL.ActiveSheet

 num_rows = oSH.UsedRange.Rows.Count

 If num_rows = 1  then
 oSH.Range("A2").Select    
 Else
 oSH.Range("A" & num_rows+4).Select 
 End If

 Set obj = ActiveDocument.GetSheetObject(aSheetObj(i))
 obj.CopyTableToClipboard True
 oSH.Paste 
 sCaption=obj.GetCaption.Name.v
 set obj=Nothing  

oSH.Cells.Select


oSH.Columns("A").ColumnWidth = 12.17
oSH.Columns("B").ColumnWidth = 12.17


 If num_rows = 1  then
oSH.Range("A" & num_rows).Value = sCaption
oSH.Range("A" & num_rows).Font.Bold = True
'oSH.Range("A" & num_rows).Font.ColorIndex = 3
oSH.Range("A" & num_rows).Interior.ColorIndex = 40
 Else

oSH.Range("A" & num_rows+3).Value  = sCaption
oSH.Range("A" & num_rows+3).Font.Bold = True
'oSH.Range("A" & num_rows+3).Font.ColorIndex = 3
oSH.Range("A" &num_rows+3).Interior.ColorIndex = 40
 End If
 'oXL.Selection.Columns.AutoFit  


next
'==============================================================
  oSH.Range("A1").Select
  oXL.Sheets("Sheet2").Delete  
    oXL.Sheets("Sheet3").Delete  
    oSH.Name = "Data"

   oXL.ActiveWorkBook.SaveAs strSaveFile & ActiveDocument.Evaluate("date(Now(), 'DD-MM-YYYY hh:mm:ss')") & ".xlsx"

set oSH = Nothing    
set oXL=Nothing
end sub

I already enabled 'Allow system access' and 'System Access' along with Allow Macro Override from Document Settings, but this still happens.

tamilarasu‌ Kindly help me out here. I'm attching my app to the post. You can find the macro button in the tab titled 'Tables'. Thanks in advance!

[Miguel_Angel_Baeyens]

Replacing the path and removing the colons from the evaluate works for me (Excel 2010): exports the workbook and saves it with the date. No module popup. Verify as mentioned the paths and permissions.

[mrthomasshelby]

Looks like something is wrong with my folder permissions. I have done as you both suggested but my excel file is not getting saved, is opening as a temp file titled 'Book1' and the popup keeps coming up. I have checked the folder permissions and I have full control for the folder too. Really weird. Thanks for the efforts mbaeyens‌ and olivierrobin‌. Guess I'll have to make do with no macros

[tamilarasu]

Hi Phalgun,

Oliver and Miguel has already pointed out the issues. Below attached file is working for me. Make sure you change the below path and allow "System access" in module security. If you still have any issues let us know.

[mrthomasshelby]

Thanks tamilarasu‌ for the original macro as well as the help on this question! Really really valuable macro that you have there. I'm sure it has helped a lot of people till now.

[tamilarasu]

Ha ha. I'm glad you liked it . That was posted couple of years ago. If you want to do some formatting you can find the code by googling. If you need any help feel free to tag me. I will try my level best to help you.

BTW, is it working for you now?

[mrthomasshelby]

Yes. It's weirdly working now that I'm saving the files to desktop. Guess my permissions are different from what I see for the folders I was trying to save in earlier. Thanks a lot!

[tamilarasu]

Great, Phalgun. Have a fantastic day!!

[mrthomasshelby]

You too my friend!