Results 1 to 4 of 4
  1. #1
    Ale is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2018
    Posts
    2

    Clear Specific Tab Contents and run query to re-populate and export to same tab... Help!!

    Hello,



    I have Access 2016 with Win 10. I have a few users who run the same DB. Right now I have to open my excel book (RCSC Review) and delete the contents of this tab (RCSC Review), then save and close. I cannot delete the tab, because the book has a bunch of tabs with reference formulas. This is what i have, but it does not delete the RCSC Review Tab content. My book is an .XLSB. The error I now get is "Sorry, we could't find RCSC Review.xlsx. Is it possible it was moved, renamed or deleted?". I am ok if the XLSB book does not open for this to run or be updated.

    Code:
    
    
    Code:
    Private Sub Update RCSC Tab Click()
     
    Dim MyProfile As String
    lblWait.Visible = True
        DoCmd.Hourglass True
    
    MyProfile = Environ("UserProfile")
    MyProfile = MyProfile & "\Desktop\RCSC_Review\"
    MyProfile = MyProfile & "RCSC Review"
    
    'The next 2 lines were what i was trying  
    Set xlWB = xlApp.Workbooks.Open("RCSC Review")
     
    With xlWB.Sheets("RCSC Review").ClearContents
    
    'The below calls the Access Query 
    
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "RCSC Review", _
    MyProfile, True, "RCSC Review"
     
    lblWait.Visible = False
        DoCmd.Hourglass False
    End Sub

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    You have not declared xlWB and xlApp object variables. Need to also set xlApp

    Dim xlApp As Excel.Application, xlWB As Excel.Workbook, MyProfile As String
    Set xlApp = CreateObject("Excel.application", "")
    MyProfile = Environ("UserProfile") & "\Desktop\RCSC_Review.xlsx"
    Set xlWB = xlApp.Workbooks.Open(MyProfile)
    With xlWB.Sheets("RCSC Review").ClearContents
    xlWB.Close savechanges:=True
    xlApp.Quit
    ...
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Ale is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2018
    Posts
    2
    Thank you for the quick response. Now the error that pops up is "Run-time 438" "Object doesn't supportthis property or method". This is an existing .xlsb workbook. Here is what i am now using:
    Code:
    Private Sub RCSC  Tab_Click()
     
    lblWait.Visible = True
       DoCmd.Hourglass True
     
    Dim xlApp As Excel.Application, xlWB As Excel.Workbook, MyProfile As String
    Set xlApp = CreateObject("Excel.application", "")
    MyProfile = Environ("UserProfile") & "\Desktop\RCSC_Review\RCSC Review"
    Set xlWB = xlApp.Workbooks.Open(MyProfile)
    With xlWB.Sheets("RCSC_Review").ClearContents
    xlWB.Close savechanges:=True
    xlApp.Quit
    End With
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "RCSC Review", _
    MyProfile, True, "RCSC Review"
     
     
    lblWait.Visible = False
        DoCmd.Hourglass False
    End Sub

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Have to include the file extension in the file name reference - as shown in my example. I've never seen xlsb extension.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 02-23-2016, 08:36 PM
  2. Export to excel but clear worksheet first
    By mercapto in forum Import/Export Data
    Replies: 3
    Last Post: 05-17-2015, 11:57 AM
  3. Export Query to .ODS file starting at a specific cell
    By Baldeagle in forum Import/Export Data
    Replies: 4
    Last Post: 03-20-2013, 01:03 PM
  4. Export Query to specific excel sheet
    By Perceptus in forum Queries
    Replies: 2
    Last Post: 12-20-2012, 11:50 AM
  5. Export Contents of a Report into Email Body
    By Nosaj08 in forum Reports
    Replies: 4
    Last Post: 05-27-2009, 09:05 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums