Results 1 to 6 of 6
  1. #1
    cbuechner is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Jan 2021
    Posts
    60

    Exporting Inserting Rows


    Ok this one is beyond my little bit of skills.

    I have Query 1, that contains "Data". I want to export INTO a specific excel file ("FILE") and insert rows while keeping the same format of the receiving file. SO if there are 5 results, the excel file will insert just 5 rows.

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    make a query that pulls the data you need . you can also set TOP 5.

    Code:
    vFile = "c:\folder\myFile.xlsx"
    vQry = "qsExport2Excel"
    
    
    docmd.TransferSpreadsheet acExport ,acSpreadsheetTypeExcel12,vQry, vFile, true,vSheetName

  3. #3
    cbuechner is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Jan 2021
    Posts
    60
    How can i make that go into a specific cell? FOr example B2

  4. #4
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    If you want to export to a particular range, consider automation:

    http://www.accessmvp.com/KDSnell/EXCEL_MainPage.htm

    or any of the other examples there that might work for you.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    cbuechner is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Jan 2021
    Posts
    60
    Thank you, I was able to use one from the link you provided and changed it around a little to add a popup. Final code

    Public Function ExportTerms()


    Dim lngColumn As Long
    Dim xlx As Object, xlw As Object, xls As Object, xlc As Object
    Dim dbs As DAO.Database
    Dim rs As DAO.Recordset
    Dim blnEXCEL As Boolean, blnHeaderRow As Boolean
    Dim PartID As String
    PartID = Inputbox("Enter Part ID")




    blnEXCEL = False


    ' Replace True with False if you do not want the first row of
    ' the worksheet to be a header row (the names of the fields
    ' from the recordset)
    blnHeaderRow = True


    ' Establish an EXCEL application object
    On Error Resume Next
    Set xlx = GetObject(, "Excel.Application")
    If Err.Number <> 0 Then
    Set xlx = CreateObject("Excel.Application")
    blnEXCEL = True
    End If
    Err.Clear
    On Error GoTo 0


    ' Change True to False if you do not want the workbook to be
    ' visible when the code is running
    xlx.Visible = False


    ' Replace C:\Filename.xls with the actual path and filename
    ' of the EXCEL file into which you will write the data
    Set xlw = xlx.Workbooks.Open("C:\Users\File\Desktop\Excel EXPORT Template.xlsx")


    ' Replace WorksheetName with the actual name of the worksheet
    ' in the EXCEL file
    ' (note that the worksheet must already be in the EXCEL file)
    Set xls = xlw.Worksheets("Sheet2")


    ' Replace A1 with the cell reference into which the first data value
    ' is to be written
    Set xlc = xls.Range("B2") ' this is the first cell into which data go


    Set dbs = CurrentDb()


    ' Replace QueryOrTableName with the real name of the table or query
    ' whose data are to be written into the worksheet
    Set rs = CurrentDb.OpenRecordset("SELECT Terms FROM Terms_Export WHERE Engine_ID=" & PartID)


    If rs.EOF = False And rs.BOF = False Then


    rs.MoveFirst


    If blnHeaderRow = True Then
    For lngColumn = 0 To rs.Fields.Count - 1
    xlc.Offset(0, lngColumn).Value = rs.Fields(lngColumn).Name
    Next lngColumn
    Set xlc = xlc.Offset(1, 0)
    End If


    ' write data to worksheet
    Do While rs.EOF = False
    For lngColumn = 0 To rs.Fields.Count - 1
    xlc.Offset(0, lngColumn).Value = rs.Fields(lngColumn).Value
    Next lngColumn
    rs.MoveNext
    Set xlc = xlc.Offset(1, 0)
    Loop


    End If


    rs.Close
    Set rs = Nothing


    dbs.Close
    Set dbs = Nothing


    ' Close the EXCEL file while saving the file, and clean up the EXCEL objects
    Set xlc = Nothing
    Set xls = Nothing
    xlw.Close True ' close the EXCEL file and save the new data
    Set xlw = Nothing
    If blnEXCEL = True Then xlx.Quit
    Set xlx = Nothing


    End Function

  6. #6
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Thanks for posting your solution. If you ever want to post more than a few lines of code for an issue you're having, please use code tags (# on forum posting toolbar) and use proper indentation to increase your chances of getting others to look at it, plus it avoids issues that the forum software will introduce if you don't.
    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. exporting more than 65000 rows
    By TheReb in forum Import/Export Data
    Replies: 3
    Last Post: 02-08-2015, 12:09 PM
  2. Replies: 4
    Last Post: 07-14-2014, 02:26 PM
  3. Replies: 1
    Last Post: 09-13-2013, 12:00 PM
  4. Exporting more than 65000 Access rows to Excel
    By TonyBender in forum Import/Export Data
    Replies: 8
    Last Post: 08-22-2013, 04:56 PM
  5. Inserting multiple rows using a value in a field
    By z1efuller1 in forum Queries
    Replies: 1
    Last Post: 01-06-2010, 11:20 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