Results 1 to 4 of 4

Export to excel but clear worksheet first

  1. #1
    mercapto is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    Jan 2012
    Location
    Spain
    Posts
    91

    Export to excel but clear worksheet first

    I am using a code to export a query to excel, but I find that if I export less rows that are on the worksheet, the old exceeding rows remain on the worksheet.


    I need the whole table to be updated.

    Is there any way to clear the table before writing the data? Or maybe is there any exporting method to export blank rows that could overwrite the old ones? Any other option?

    Deleting the file is not an option for me, I need to keep the same file and structure.

  2. #2
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,863
    I would guess the only way would be to Automate Excel. I would use early binding and the beginning of the code would look something like ...

    Code:
    Dim xlApp As Excel.Application
    Dim myBook As Workbook
    Dim mySheet As Worksheet
    Set xlApp = New Excel.Application
    Set myBook = xlApp.Workbooks.Open(strPath & "Template\Product_By_PO.xlsx")
    Set mySheet = myBook.Sheets.Item(1) 'Grab the first worksheet

  3. #3
    mercapto is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    Jan 2012
    Location
    Spain
    Posts
    91
    Quite not satisfied with the solution but I finally used this:

    Code:
    Function BorraExcel(ByVal strRutaHojaExcel As String)
    
    Dim wb As Object
    Dim XLapp As Object
    Dim tbl As Object
         
    Set XLapp = CreateObject("Excel.Application")
    XLapp.Visible = False
    Set wb = XLapp.Workbooks.Open(strRutaHojaExcel, True, False)
    wb.Sheets(1).Range("A1").Select
        
    Set tbl = XLapp.ActiveCell.CurrentRegion
    tbl.Offset(1, 0).Resize(tbl.Rows.Count - 1, tbl.Columns.Count).Select
    XLapp.Selection.ClearContents
    tbl.Range("A1").Select
    
    wb.Close True
    Set wb = Nothing
    XLapp.Quit
    Set XLapp = Nothing
    
    End Function
    Thanks for answering.

  4. #4
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,863
    I am not an expert but this is the way I understand it. So I will explain it how I understand it and anybody is welcome to chime in if I am not correct.

    If you create a reference to Microsoft Office XX.X Object and employ early binding, intellisence will display the object's members.

    For instance, the code example I provided uses axlApp as a class and it has access to the objects within. xlApp represents the application, Excel.

    Here, xlApp is instantiated and initialized in one line.
    Set xlApp = New Excel.Application

    From here you can create new objects in memory and access their members. Intellisence will help you by displaying what is available within your objects.
    Set myBook = xlApp.Workbooks.Open(strPath & "Template\Product_By_PO.xlsx")


    When instantiating the myBook object, you can view objects and classes available within the xlApp class by using the dot notation.
    Set myBook = xlApp.

    If you simply instantiate your object as a generic object (dim MyObj as Object), you will not have access to intellisense. This is known as late binding. Early binding is when you define the object at the time of instantiation. When using early binding, you need to reference the files using the reference dialog within your VBA editor (under Tools).

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

Similar Threads

  1. Export Worksheet Labels
    By BR549 in forum Import/Export Data
    Replies: 4
    Last Post: 09-05-2014, 07:35 AM
  2. Export Form And Subform Data To Excel Form Same Worksheet
    By tomtheappraiser in forum Import/Export Data
    Replies: 6
    Last Post: 08-12-2013, 10:39 AM
  3. Delete Contents of worksheet before export - xlWSh.Cells.Select
    By allenjasonbrown@gmail.com in forum Programming
    Replies: 1
    Last Post: 10-14-2012, 10:38 PM
  4. export query to hidden excel worksheet
    By allenjasonbrown@gmail.com in forum Programming
    Replies: 3
    Last Post: 10-14-2012, 12:39 PM
  5. VBA to Import Excel Worksheet
    By bdaniel in forum Programming
    Replies: 2
    Last Post: 11-23-2010, 10:53 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
  •  
Tech Forums: Microsoft Office Forums