Results 1 to 8 of 8
  1. #1
    joshynaresh is offline Competent Performer
    Windows 7 32bit Access 2007
    Join Date
    Aug 2013
    Posts
    131

    export data to existing excel file

    Hello sir,



    I want to export data to existing excel file. can it is possible?

    Thank you in advance.

  2. #2
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664

  3. #3
    joshynaresh is offline Competent Performer
    Windows 7 32bit Access 2007
    Join Date
    Aug 2013
    Posts
    131
    Thanku You.

    Dim lngColumn As Long
    Dim xlx As Object, xlw As Object, xls As Object, xlc As Object
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim blnEXCEL As Boolean, blnHeaderRow As Boolean

    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 = True

    ' 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:\Filename.xls")

    ' 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("WorksheetName")
    ' Replace A1 with the cell reference into which the first data value
    ' is to be written

    Set xlc = xls.Range("A1") ' 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 rst = dbs.OpenRecordset("QueryOrTableName", dbOpenDynaset, dbReadOnly)

    If rst.EOF = False And rst.BOF = False Then
    rst.MoveFirst

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

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

    rst.Close
    Set rst = 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
    is helpful to be.
    but i have some cell written protected.
    I want that where cell are written protected transfer should be skipped to another unprotected cell.
    can it is possible.

    Thank you once again

  4. #4
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Yes. you have to be able to determine when a cell in protected and have the code skip that cell. I haven't written code for Excel for years - I think the last time was for Excel 2003.
    So I can't tell you exactly how to write the code. But I think it can be done.

    Good luck.

  5. #5
    joshynaresh is offline Competent Performer
    Windows 7 32bit Access 2007
    Join Date
    Aug 2013
    Posts
    131
    have you any code for this.

    Thank You in advance

  6. #6
    joshynaresh is offline Competent Performer
    Windows 7 32bit Access 2007
    Join Date
    Aug 2013
    Posts
    131
    hey buddy,

    can anybody help?

    Thanks in advance

  7. #7
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    have you any code for this
    No, sorry. You will have to modify the code to suit your purpose.

    Try recording a macro in Excel, then adapt the code to your Access code. That is what I do when I have to export to excel and format the data in the worksheet..

  8. #8
    joshynaresh is offline Competent Performer
    Windows 7 32bit Access 2007
    Join Date
    Aug 2013
    Posts
    131
    i don't no how can record a macro in excel?
    Thanks in advance

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

Similar Threads

  1. Export to Excel as a shared file
    By ran-d in forum Programming
    Replies: 7
    Last Post: 07-23-2013, 04:04 PM
  2. Replies: 2
    Last Post: 05-16-2013, 07:43 PM
  3. export quety result to existing excel workbook
    By jsimha in forum Import/Export Data
    Replies: 1
    Last Post: 01-19-2013, 06:49 AM
  4. export data on excel file
    By Mina Garas in forum Queries
    Replies: 1
    Last Post: 12-01-2012, 02:43 PM
  5. how export and replace existing file
    By sk88 in forum Access
    Replies: 6
    Last Post: 10-01-2012, 12:55 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