topic Re: 'Edit Module' popping up upon Macro Execution in QlikView

'Edit Module' popping up upon Macro Execution

mrthomasshelby — Thu, 03 May 2018 08:43:22 GMT

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!

Re: 'Edit Module' popping up upon Macro Execution

Miguel_Angel_Baeyens — Thu, 03 May 2018 08:51:10 GMT

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

Re: 'Edit Module' popping up upon Macro Execution

olivierrobin — Thu, 03 May 2018 08:57:39 GMT

hello

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

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

Re: 'Edit Module' popping up upon Macro Execution

olivierrobin — Thu, 03 May 2018 08:58:10 GMT

sorry

the name you use to save the xl file

Re: 'Edit Module' popping up upon Macro Execution

mrthomasshelby — Thu, 03 May 2018 09:04:06 GMT

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.

Re: 'Edit Module' popping up upon Macro Execution

olivierrobin — Thu, 03 May 2018 09:10:35 GMT

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

Re: 'Edit Module' popping up upon Macro Execution

mrthomasshelby — Thu, 03 May 2018 09:14:20 GMT

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.

Re: 'Edit Module' popping up upon Macro Execution

olivierrobin — Thu, 03 May 2018 09:22:56 GMT

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 ?

Re: 'Edit Module' popping up upon Macro Execution

mrthomasshelby — Thu, 03 May 2018 09:31:41 GMT

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.

Re: 'Edit Module' popping up upon Macro Execution

olivierrobin — Thu, 03 May 2018 09:36:21 GMT

verify there's no other excel opened on your computer

Re: 'Edit Module' popping up upon Macro Execution

Miguel_Angel_Baeyens — Thu, 03 May 2018 09:38:32 GMT

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.

Re: 'Edit Module' popping up upon Macro Execution

mrthomasshelby — Thu, 03 May 2018 09:49:34 GMT

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

Re: 'Edit Module' popping up upon Macro Execution

tamilarasu — Thu, 03 May 2018 10:01:58 GMT

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.

Re: 'Edit Module' popping up upon Macro Execution

mrthomasshelby — Thu, 03 May 2018 10:12:16 GMT

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.

Re: 'Edit Module' popping up upon Macro Execution

tamilarasu — Thu, 03 May 2018 10:19:46 GMT

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?

Re: 'Edit Module' popping up upon Macro Execution

mrthomasshelby — Thu, 03 May 2018 10:21:47 GMT

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!

Re: 'Edit Module' popping up upon Macro Execution

tamilarasu — Thu, 03 May 2018 10:27:05 GMT

Great, Phalgun. Have a fantastic day!!

Re: 'Edit Module' popping up upon Macro Execution

mrthomasshelby — Thu, 03 May 2018 10:28:44 GMT

You too my friend!