INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Question about TransferSpreadsheet

Question about TransferSpreadsheet

(OP)
thread705-1525294: Question about TransferSpreadsheet

Somehow this thread got closed and not resolved, so I am opening it up again...

Quote:

Good day,
I normally do not use the Transferspreadsheet function but rather CopyFromRecordset function. You can specify where you want to place the data and if needed any formating can be done from within Access. I find this method easier to use.

Below is an answer I gave someone a few months ago. Try it and see if it helps.

I use a different method to export data from Access to Excel. I copy the whole table/query to the sheet with the steps indicated below. It is an extract of the method I use for all data exported to Excel and do most of the formatting from within Access.

If need you could trasfer the data to a different work sheet and then from within Excel append it to the table in Excel or do a row count via Access in Excel and use that number to append it from there.

Remember to synchronize your query with the columns in Excel if you copy directly into and existing table.

Hope this helps.

Hennie

Set objExcel = CreateObject("Excel.Application")
'Run qryMisaRSP and transfer data to sheet RSP for annual statistics.

Set rs1 = db.OpenRecordset("tblMisaRSP", dbOpenSnapshot)

'Set the object variable to reference the file you want to see.

With objXL
.Visible = True

Set objWkb = .Workbooks.Open(conWKB_NAME)

On Error Resume Next

Set objSht = objWkb.Worksheets(conSHT_NAME1) 'RSP
objWkb.Worksheets("RSP").Activate

objWkb.Windows("RSP").Visible = True

With objSht

'Copy data from the two record sets
.Range("A2").CopyFromRecordset rs1

Thanks Hennie but there seems to be a few things missing.

I added DIM statements at the top of the code below along with the End With but the code crashes
with an Object required message on the Set Rs1 line.

What other definitions are needed for this?


Thanks


CODE -->

Dim rs1 As Recordset
Dim objExcel As Object

Set objExcel = CreateObject("Excel.Application")
    'Run Ord_tbl_qry and transfer data from Ord_tbl to sheet Order for Sales Order information.

    Set rs1 = db.OpenRecordset("Ord_tbl", dbOpenSnapshot)

        'Set the object variable to reference the file you want to see.

        With objXL
            .Visible = True

            Set objWkb = .Workbooks.Open(conWKB_NAME)

            On Error Resume Next

            Set objSht = objWkb.Worksheets(conSHT_NAME1)         'RSP
            objWkb.Worksheets("RSP").Activate

            objWkb.Windows("RSP").Visible = True

            With objSht

                'Copy data from the two record sets
                .Range("A2").CopyFromRecordset rs1
            End With
        End With 

RE: Question about TransferSpreadsheet

(OP)
Thanks SamIAm

Quote:

Transferring multiple tables/queries from Access to a single Excel workbook is something we do all the time. Here is a sample of the code to transfer 4 tables from Access to a single xls book called 'FileName'.

Dim SheetName1 As String
Dim SheetName2 As String
Dim SheetName3 As String
Dim Sheetname4 As String

SheetName1 = "tblOne"
SheetName2 = " tblTwo "
SheetName3 = " tblThree "
Sheetname4 = " tblFour "

DoCmd.TransferSpreadsheet acExport, 8, SheetName1, FileName, False, ""

DoCmd.TransferSpreadsheet acExport, 8, SheetName2, FileName, False, ""

DoCmd.TransferSpreadsheet acExport, 8, SheetName3, FileName, False, ""

DoCmd.TransferSpreadsheet acExport, 8, Sheetname4, FileName, False, ""

We then format each sheet in xls using Access VBA code.

I have tried the DoCmd.TransferSpreadsheet acExport function but my experience with this
command is that it will only export to Column 1 Row 1 and because I am attempting to append
to an existing spreadsheet it creates a new worksheet (tab in my spreadsheet).

RE: Question about TransferSpreadsheet

You should always set "Option Explicit" in your general declarations and then make sure your code compiles. You have different spellings of the same object and you haven't dim'd:
  • db
  • objXL
  • objWkb
  • objSht
You should be explicit with

CODE --> vba

Dim db as DAO.Database
Dim rs1 As DAO.Recordset 

I trust your constants are declared somewhere and have values.

Duane
Hook'D on Access
MS Access MVP

RE: Question about TransferSpreadsheet

This works for me after I added some declarations and pull the constants from procedure arguments.


CODE --> vba

Option Compare Database
Option Explicit
Public Sub CreateExcel(conWKB_NAME As String, conSht_NAME1 As String)
    
    Dim db As DAO.Database
    Dim rs1 As DAO.Recordset
    
    Dim objXL As Object
    Dim objWkb As Object
    Dim objSht As Object
    Set objXL = CreateObject("Excel.Application")
    'Run Ord_tbl_qry and transfer data from Ord_tbl to sheet Order for Sales Order information.
    Set db = CurrentDb
    Set rs1 = db.OpenRecordset("OneTable", dbOpenSnapshot)
    
    'Set the object variable to reference the file you want to see.
    
    With objXL
        .Visible = True
        
        Set objWkb = .Workbooks.Open(conWKB_NAME)
        
        On Error Resume Next
        
        Set objSht = objWkb.Worksheets(conSht_NAME1)         'RSP
        objWkb.Worksheets("RSP").Activate
        
        objWkb.Windows("RSP").Visible = True
        
        With objSht
        
            'Copy data from the two record sets
            .Range("A2").CopyFromRecordset rs1
        End With
    End With
End Sub 

Duane
Hook'D on Access
MS Access MVP

RE: Question about TransferSpreadsheet

(OP)
Thanks Duane-

This works pretty good and places the table data into the correct worksheet but it over-writes existing records.

How difficult would it be to find the last row used and append to the next row on the spreadsheet?

RE: Question about TransferSpreadsheet

I think last row can be found with this code.

CODE --> vba

Dim lngLastRow as Long
lngLastRow = objSht.Cells.Find(What:="*", _
                            After:=objSht.Range("A1"), _
                            LookAt:= 2, _
                            LookIn:= -4123, _
                            SearchOrder:= 1, _
                            SearchDirection:= 2, _
                            MatchCase:=False).Row 

Duane
Hook'D on Access
MS Access MVP

RE: Question about TransferSpreadsheet

(OP)
Thanks again Duane-

I inserted this into the code and modified the .Range("A2") to the point to the next row

Works great.

thanks again for your help Duane!


CODE -->

With objXL
        .Visible = True
        
        Set objWkb = .Workbooks.Open("C:\Order_Stuff.xlsx")
        
        On Error Resume Next
        
        Set objSht = objWkb.Worksheets("NewOrders")         'RSP
        objWkb.Worksheets("RSP").Activate
        
        objWkb.Windows("RSP").Visible = True

        lngLastRow = objSht.Cells.Find(What:="*", _
                            After:=objSht.Range("A1"), _
                            LookAt:=2, _
                            LookIn:=-4123, _
                            SearchOrder:=1, _
                            SearchDirection:=2, _
                            MatchCase:=False).Row
        With objSht
        
            'Copy data from the two record sets
  '          .Range("A2").CopyFromRecordset rs1
            .Range("A" & lngLastRow + 1).CopyFromRecordset rs1
        End With
    End With 

RE: Question about TransferSpreadsheet

(OP)
Well Duane, I tried to give you a star for each of the two great posts that made on this thread but apparently
only one can be displayed per thread unless I am not doing it right.

There is one strange thing that I noticed and that is the spreadsheet is opened by this code (and left open)
and when I close the Spreadsheet from the Excel application, there seems to be some residual blank ghost excel
workbook that is open which I have to also close manually from the Excel application.

Have you seen this anomaly before?

Thanks

RE: Question about TransferSpreadsheet

(OP)
I tried both of these in order to close the excel spreadsheet after it was updated but neither of these worked.

objWkb.Workbooks("C:\Order_Stuff.xlsx").Close SaveChanges:=True

objWkb.Workbooks.Close SaveChanges:=True

RE: Question about TransferSpreadsheet

I think you need to set the objects to nothing like:

CODE --> vba

set objXL = Nothing 

Duane
Hook'D on Access
MS Access MVP

RE: Question about TransferSpreadsheet

(OP)
Thanks Duane-

set objXL = Nothing

does not close the excel spreadsheet either
I also tried both of these individually on either side of the
set objXL = Nothing and still doesn't work...'
objWkb.Workbooks("C:\Order_Stuff.xlsx").Close SaveChanges:=True

objWkb.Workbooks.Close SaveChanges:=True

RE: Question about TransferSpreadsheet

Setting excel/workbook to nothing only releases variables without touching the objects. You need close objects (firstly workbook next excel application) and then set variables to Nothing:
objWkb.Workbooks("C:\Order_Stuff.xlsx").Close SaveChanges:=True
objXL.Quit
Set objXL = Nothing
Set objWkb = Nothing

combo

RE: Question about TransferSpreadsheet

(OP)

Quote:

Setting excel/workbook to nothing only releases variables without touching the objects. You need close objects (firstly workbook next excel application) and then set variables to Nothing:
objWkb.Workbooks("C:\Order_Stuff.xlsx").Close SaveChanges:=True
objXL.Quit
Set objXL = Nothing
Set objWkb = Nothing
combo

Thanks Combo

I tried what you suggested and the excel spreadsheet now displays a pop-up window asking if
I want to save the changes made.

Is there a way to squelch this from happening so that the spreadsheet is saved and closed
without user involvement?

RE: Question about TransferSpreadsheet

The code works for me. You might want to stop hiding errors by leaving the error handler on. Also try remove:

objWkb.Windows("RSP").Visible = True

Duane
Hook'D on Access
MS Access MVP

RE: Question about TransferSpreadsheet

(OP)
Thanks Duane but this doesn't work for me either...

Could it be something in the way Excel is set up on my PC?

Here is what my code looks like:

CODE -->

Private Sub tektips_Click()

Dim strSql As String
Dim db As DAO.Database
Dim rs1 As DAO.Recordset
Dim objXL As Object
Dim objWkb As Object
Dim objSht As Object
Dim lngLastRow As Long


    Set objXL = CreateObject("Excel.Application")
    'Run Ord_tbl_qry and transfer data from Ord_tbl to sheet Order for Sales Order information.
    Set db = CurrentDb
    Set rs1 = db.OpenRecordset("Ord_tbl", dbOpenSnapshot)
    
    With objXL
        .Visible = True
        
        Set objWkb = .Workbooks.Open("C:\Order_Stuff.xlsx")
        
        On Error Resume Next
        
        Set objSht = objWkb.Worksheets("NewOrders")         'RSP
        objWkb.Worksheets("RSP").Activate
        
'        objWkb.Windows("RSP").Visible = True

        lngLastRow = objSht.Cells.Find(What:="*", _
                            After:=objSht.Range("A1"), _
                            LookAt:=2, _
                            LookIn:=-4123, _
                            SearchOrder:=1, _
                            SearchDirection:=2, _
                            MatchCase:=False).Row
        With objSht
            .Range("A" & lngLastRow + 1).CopyFromRecordset rs1
        End With
    End With

  objWkb.Workbooks("C:\Order_Stuff.xlsx").Close SaveChanges:=True

    objXL.Quit
    
    Set rs1 = Nothing
    Set objXL = Nothing
    Set objWkb = Nothing

End Sub 

RE: Question about TransferSpreadsheet

(OP)
Ok,

I made a few changes and this actually now works:

CODE -->

Private Sub tektips_Click()

Dim strSql As String
Dim db As DAO.Database
Dim rs1 As DAO.Recordset
Dim objXL As Object
Dim objWkb As Object
Dim objSht As Object
Dim lngLastRow As Long


    Set objXL = CreateObject("Excel.Application")
    'Run Ord_tbl_qry and transfer data from Ord_tbl to sheet Order for Sales Order information.
    Set db = CurrentDb
    Set rs1 = db.OpenRecordset("Ord_tbl", dbOpenSnapshot)
    
    With objXL
        .Visible = True
        
        Set objWkb = .Workbooks.Open("C:\Order_Stuff.xlsx")
        
        On Error Resume Next
        
        Set objSht = objWkb.Worksheets("NewOrders")         'RSP
        objWkb.Worksheets("RSP").Activate
        
'        objWkb.Windows("RSP").Visible = True

        lngLastRow = objSht.Cells.Find(What:="*", _
                            After:=objSht.Range("A1"), _
                            LookAt:=2, _
                            LookIn:=-4123, _
                            SearchOrder:=1, _
                            SearchDirection:=2, _
                            MatchCase:=False).Row
        With objSht
            .Range("A" & lngLastRow + 1).CopyFromRecordset rs1
        End With
    End With

    objWkb.Save
    objWkb.Close
    objXL.Quit
    With objXL
        .Visible = True
        .EnableEvents = True
        .DisplayAlerts = True
    End With

    Set objXL = Nothing
    Set rs1 = Nothing

End Sub 

Thanks everybody for your help with this...

RE: Question about TransferSpreadsheet

You can remove
With objXL
    .Visible = True
    .EnableEvents = True
    .DisplayAlerts = True
End With 
You apply it to non-existing object (excel is already closed: objXl.Quit) and you have no error message only due to prior On Error Resume Next.

combo

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close