'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]

When that happens, it means the macro stopped before completing.

In this case, the SaveAs method does not work, it could be among others because the path is wrong, the evaluation does not return a valid value for a file (like a "/" character) or lack for permissions for the path specified.

EDIT: at a first glance, indeed, the ":" character returned by the Evaluate of the date is not allowed by Windows as part of a file name or folder

[olivierrobin]

hello

the problem is the filename. it is invalid for Windows.

try with another name (i.e try _ instead of - , and avoid : )

[olivierrobin]

sorry

the name you use to save the xl file

[mrthomasshelby]

Hi Olivier, I have tried replacing the filename part with 'Book1' instead of the date part. But the edit dialog box still keeps popping up.

[olivierrobin · Accepted solution]

the problem is that part :

ActiveDocument.Evaluate("date(Now(), 'DD-MM-YYYY ...

you can't use : for the name of the file

try something else

[mrthomasshelby]

Yes Olivier I understood that and replaced "date(Now(), 'DD-MM-YYYY hh:mm:ss')" with "Book1".  But  the macro still keeps popping up an edit module box.

[olivierrobin]

as i'm using a french version of excel, I have to change those 2 lines

oXL.Sheets("Feuil2").Delete 
oXL.Sheets("Feuil3" ).Delete

and when i execute the macro, creates a file test_.xls

if the file already exist, you have a popup on excel  to overwrite the file

and excel doesn't close but there is no error

are you sure the directory c:\temp exist and you are allowed to save documents in this directory ?

[mrthomasshelby]

Even when I changed the directory path to 'C:\Users\XYZ\Documents' I get the same error. And I'm pretty sure that this directory exists and I am allowed to save documents in this directory.

[olivierrobin]

verify there's no other excel opened on your computer