Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
mrthomasshelby
Creator III
Creator III

'Edit Module' popping up upon Macro Execution

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!

1 Solution

Accepted Solutions
olivierrobin
Specialist III
Specialist III

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

View solution in original post

17 Replies
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
Specialist III
Specialist III

hello

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

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

olivierrobin
Specialist III
Specialist III

sorry

the name you use to save the xl file

mrthomasshelby
Creator III
Creator III
Author

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
Specialist III
Specialist III

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
Creator III
Creator III
Author

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
Specialist III
Specialist III

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
Creator III
Creator III
Author

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
Specialist III
Specialist III

verify there's no other excel opened on your computer