Results 1 to 3 of 3
  1. #1
    manics31 is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2016
    Location
    Philadelphia
    Posts
    23

    Exporting Query From Access to Excel Template

    Hello all,


    I've been looking at the post here for Access to Excel export of data and I have a found a few however what I am looking for exactly is a code or some type of function
    that can export the selected Data from a query without creating another worksheet just replacing the already available data let's say from A2:A100.
    This is the function i am currently using:
    Code:
    Sub exportspreadsheet()
    On Error GoTo HandleError
        
    Dim objXLApp As Object
    Set objXLApp = CreateObject("Excel.Application")
    Dim objXLBook As Excel.Workbook
    
    Dim db As DAO.Database
    
        Set db = CurrentDb
        
        conPath = GetPath(db.Name)
        
        'delete the spreadsheet
        Kill conPath & "BYWEEKLYREPORT.xlsx"
        
        ' create a workbook from the template
        Set objXLApp = New Excel.Application
        Set objXLBook = objXLApp.Workbooks.Open(conPath & "BYWEEKLYREPORT.xltx")
        'objXLApp.Visible = True
    
        objXLBook.SaveAs (conPath & "BYWEEKLYREPORT.xlsx")
        objXLBook.Close
    
        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "qryDeptWeeklyReport", conPath & "BYWEEKLYREPORT.xlsx", True
    
    
    Dialog.Box "Done!" & vbCrLf & vbCrLf & "Look in the directory" & vbCrLf & vbCrLf & "where the application sits for ""BYWEEKLYREPORT.xlsx"""
    
    ProcDone:
        On Error Resume Next
        
        ' Let's clean up our act
        Set qdf = Nothing
        Set db = Nothing
        Set rs = Nothing
        Set objResultsSheet = Nothing
        Set objXLBook = Nothing
        Set objXLApp = Nothing
    
        
        
    ExitHere:
        Exit Sub
    HandleError:
        Select Case err.Number
            Case 3265
                Resume Next
            Case 1004
                Set objXLBook = objXLApp.Workbooks.Open(conPath & "BYWEEKLYREPORT.xlsx")
                Resume Next
            Case 53
                Resume Next
            Case 75
                Resume Next
            Case Else
                Dialog.Box err.Description, vbExclamation, _
                 "Error " & err.Number
        End Select
        Resume ProcDone
    End Sub
    however that function does not export data in the sheet I want it to, it creates another sheet that is not formatted.
    to get a better idea I have attached the Excel file so anyone can see the light red shaded area, that's where i want the data to go every time IBYWEEKLYREPORT4.zip export.

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    if you want to preserve the excel formatting, you will need code to loop through each column and each row to assign the relevant value. Any routine which copies/pastes (such as transfer spreadsheet) will overwrite your formatting.

    Suggest google something like 'preserve excel formatting when pasting using vba'

    This is one such link https://www.extendoffice.com/documen...lues-only.html

  3. #3
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Seeing as how you're using Automation anyway, why delete and recreate the sheet (or workbook)? Why not create a dynamic named range in the sheet (not the workbook itself if I recall) and when the sheet is automated, delete the contents of the range (thus you don't have to worry about how tall it is)? Then write the data as Ajax suggested. I guess if the range size will never change, you could simply delete then repopulate the contents of such a fixed range without making it dynamic, but I still think a named range is easier to delete.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

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

Similar Threads

  1. Replies: 15
    Last Post: 10-31-2017, 07:16 PM
  2. MS Access query to MS Excel Template
    By hect1c in forum Import/Export Data
    Replies: 4
    Last Post: 11-11-2014, 12:47 PM
  3. Replies: 2
    Last Post: 10-09-2014, 01:32 PM
  4. Replies: 1
    Last Post: 10-28-2013, 12:32 PM
  5. Exporting to a custom Excel template
    By theronlightfoot in forum Import/Export Data
    Replies: 2
    Last Post: 04-12-2010, 11:51 PM

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