Excel VBA Macro from MS Access - object variable or with block variable not set -

answer: set xlbook = xl.workbooks.open(mysheetpath)

i trying troubleshoot else's macro. have macros in access db effect excel workbook.

there 2 segments of code in question.

xlbook.sheets("item detail frozen").select set xlsheet = xlbook.worksheets("item detail frozen") xlsheet    xlsheet.cells.select    xlsheet.range("a1").activate    selection.delete shift:=xlup  end  xlbook.sheets("item detail").select set xlsheet = xlbook.worksheets("item detail") xlsheet     xl.windowstate = xlminimized     activeworkbook.refreshall     .range("a1:d1").select     .range(selection, activecell.specialcells(xllastcell)).select     selection.copy end 

i "object variable or block variable not set" on "selection.delete shift:=xlup"

and if comment out on "activeworkbook.refreshall"

i got around 1 actively setting active book, on ".range(selection, activecell.specialcells(xllastcell)).select" same error block/variable not set. i'm @ loss here. when did macro recorder in excel same type of macro works when coming access doesn't it.

here code in entirety.

    option compare database     option explicit      function fileexists(byval filetotest string) boolean        fileexists = (dir(filetotest) <> "")     end function     sub deletefile(byval filetodelete string)      deletefile:         if fileexists(filetodelete) 'see above           on error goto deletefile_errorhandler           kill filetodelete        end if        exit sub     deletefile_errorhandler:        on error resume next        msgbox "there error deleteing file(s), " & filetodelete & _               ".  check see if or 1 has of these files open , have them closed; press ok.  "      end sub        public function prepareoutputfile() variant         'variables refer excel , objects         dim mysheetpath string         dim xl excel.application         dim xlbook excel.workbook         dim xlsheet excel.worksheet         dim newfilepath string         dim newpathdir string         dim lastslashpos      string         dim attachmentdir      string         dim newfilewildcard      string         dim newfilename string           ' tell location of actual excel file         mysheetpath = "w:\sams-libertysport\sams-libertysport- week #x - of mm-dd-yyyy.xls"          'open excel , workbook         set xl = createobject("excel.application")         set xlbook = getobject(mysheetpath)          'make sure excel visible on screen         xl.visible = true         xlbook.windows(1).visible = true          xlbook.sheets("item detail frozen").select         set xlsheet = xlbook.worksheets("item detail frozen")         'with xlsheet           '   .cells.select           '  .range("a1:d1").activate            ' selection.delete shift:=xlup          'end         xlsheet            xlsheet.cells.select            xlsheet.range("a1").activate            selection.delete shift:=xlup          end          xlbook.sheets("item detail").select         set xlsheet = xlbook.worksheets("item detail")         xlsheet             xl.windowstate = xlminimized             activeworkbook.refreshall             .range("a1:d1").select             .range(selection, activecell.specialcells(xllastcell)).select             selection.copy         end          xlbook.sheets("item detail frozen").select         set xlsheet = xlbook.worksheets("item detail frozen")         xlsheet              .range("a1").select             selection.pastespecial paste:=xlpastevalues, operation:=xlnone, skipblanks _                 :=false, transpose:=false             .range("a1").select             selection.pastespecial paste:=xlpasteformats, operation:=xlnone, _                 skipblanks:=false, transpose:=false             .cells.select             .cells.entirecolumn.autofit             xl.cutcopymode = false             chdir "w:\"             newfilepath = replace(replace(replace(mysheetpath, "w:\", "r:\"), _                            "#x", "#" & cint(right(dlookup("endingwmweek", "period", "periodcode='lw'"), 2))), _                           "mm-dd-yyyy", format(dlookup("[as-of date]", "as-of date"), "mm-dd-yyyy"))             lastslashpos = instrrev(newfilepath, "\")             attachmentdir = left(newfilepath, lastslashpos - 1) & "\emailattachments"             newfilewildcard = mid(newfilepath, lastslashpos + 1, instr(lastslashpos, newfilepath, "-", vbtextcompare) - lastslashpos) & "*.*"             newfilename = mid(newfilepath, lastslashpos + 1, len(newfilepath) - lastslashpos)               while fileexists(newfilepath)                 deletefile newfilepath             wend             activeworkbook.saveas filename:= _                 newfilepath, fileformat:= _                 xlexcel8, password:="", writerespassword:="", readonlyrecommended:=false _                 , createbackup:=false          end         xlbook.sheets("topline overview").select         set xlsheet = xlbook.worksheets("topline overview")         xlsheet.range("a1").select         xlsheet.range("a1").activate         activeworkbook.save          'clean , end worksheet visible on screen         activeworkbook.close (false) 'discard changes         set xlsheet = nothing         set xlbook = nothing         xl.quit         set xl = nothing          while fileexists(attachmentdir & "\" & newfilewildcard)             deletefile attachmentdir & "\" & newfilewildcard         wend         filecopy newfilepath, attachmentdir & "\" & newfilename      end function       public sub preparedownloadedmdbfiles()          'variables refer excel , objects         dim mydbpath string         dim db database         dim newdbname string          dim fdr string         dim filenames() string         dim fileindex integer         dim filecount integer         fileindex = 0          filenames = getfilenames("w:\lib394a_*.mdb")         fileindex = 0 ubound(filenames) - 1              fdr = filenames(fileindex)             'open database             set db = workspaces(0).opendatabase("w:\" & fdr)             dim td tabledef             newdbname = ""             each td in db.tabledefs                 if left(td.name, 4) <> "msys"                     newdbname = td.name                 end if             next td             db.close              if newdbname <> ""                  deletefile "w:\" & newdbname & ".mdb"                 name "w:\" & fdr "w:\" & newdbname & ".mdb"              end if         next fileindex       end sub      public function getfilenames(template string) string()          'given filename template such w:\ab*.*, return array of filenames          dim mydbpath string         dim db database         dim newdbname string          dim fdr string         dim filenames() string         dim fileindex integer         dim filecount integer         fileindex = 0         redim filenames(0)          fdr = dir(template)         while fdr <> ""             redim preserve filenames(fileindex + 1)             filenames(fileindex) = fdr             fileindex = fileindex + 1             fdr = dir()         loop          getfilenames = filenames      end function 

i altered to

xlbook.sheets("item detail frozen").select set xlsheet = xlbook.worksheets("item detail frozen")  xlsheet.cells.clear  xlbook.sheets("item detail").select set xlsheet = xlbook.worksheets("item detail") xlsheet     xl.windowstate = xlminimized     xlbook.refreshall     xlsheet.range("a1:d1").select     xlsheet.range(selection, activecell.specialcells(xllastcell)).select     selection.copy end 

now error on xlsheet.range(selection, activecell.specialcells(xllastcell)).select , same not set error.

oh- think you're getting error on set.

set xlbook = xl.workbooks.open(mysheetpath) 

this deletes data on worksheet, why not just:

worksheets(("item detail frozen").cells.clear 

there many times, error not indicate wrong, particularly when controlling 1 office product another. when remove line, , error occurring on following line, clear indication not causing problem.