Export table to Excel with Macro

[#1]

I'm trying to export data from a table into Excel using a button via a macro. It seems pretty straight forward, but for some reason I can't get it to work. The below code fails on the first line - the Excel object is never created. My button is indeed calling the method - I verified this by putting message boxes before and after the 'Set objXL' line. The first message box fires, but the second one doesn't and the code editor pops up, indicatng and error. Can anyone see anythng wrong with this?

Sub Excel_Dump()

Set objXL = CreateObject("Excel.Application")
Set objWkb = objXL.Workbooks.Add
Set objSht1 = objWkb.Worksheets("Sheet1")
objSht1.Select
ActiveDocument.GetSheetObject("TB01").CopyTableToClipboard true
objSht1.Paste
objSht1.Name = "Export"
XLApp.Visible = true

end Sub

[#2 · Accepted solution]

I figued it out. I had to adjust the Requsted Modue Security and Current Local Security to System Access. I wound up using the following code, if anyone in the future would like it.

Sub Excel_Dump()

Set objXL = CreateObject("Excel.Application")
set objWkb = objXL.Workbooks.Add

objXL.Visible = False
objWkb.Worksheets("Sheet1").Range("A1").Select()

ActiveDocument.GetSheetObject("TB01").CopyTableToClipboard true

objWkb.Worksheets("Sheet1").PasteSpecial
objWkb.Worksheets("Sheet1").Name= "Export"

objXL.Visible = False
end Sub

[Anonymous]

Could you please tell me how to save Excel to a folder,

When I add these code : XLDoc.SaveAs Path,true

it will open the macro editer windows again and always ask me whether replace the existing file

sub ExcelFile

  strDate = CDate(Date)

  strDay = DatePart("d", strDate)

  strMonth = DatePart("m", strDate)

  strYear = DatePart("yyyy", strDate)

  If strDay < 10 Then

    strDay = "0" & strDay

  End If

  If strMonth < 10 Then

    strMonth = "0" & strMonth

  End If

  GetFormattedDate = strMonth & "-" & strDay & "-" & strYear

Path = "C:\Users\Administrator\Desktop\培训\Distribute\Book5.xls"

FileName = "Test_" & GetFormattedDate  & ".xlsx"

set XLApp = CreateObject("Excel.Application")

' XLApp.Visible = true

set XLDoc = XLApp.Workbooks.Add

ActiveDocument.GetSheetObject("CH23").CopyTableToClipboard true

XLDoc.Sheets(1).Paste()

XLDoc.Sheets(1).Rows("1:3000").EntireRow.AutoFit

ActiveDocument.GetSheetObject("CH17").CopyTableToClipboard true

XLDoc.Sheets(2).Paste()

XLDoc.Sheets(2).Rows("1:3000").EntireRow.AutoFit

XLDoc.Sheets(1).Name = "Page One"

XLDoc.Sheets(2).Name = "Page Two"

XLDoc.Sheets(3).Delete

' XLDoc.Sheets(1).Range("A1").Select

XLDoc.SaveAs Path,true

' XLDoc.ExportBiff "C:\Users\Administrator\Desktop\培训\Distribute\Book5.xlsx"

end sub

Thanks a lot