Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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.
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
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.
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.
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?
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!
Great, Phalgun. Have a fantastic day!!
You too my friend!