Hi,

Does anyone know how I can export tables to an Excel file on a specific 
sheet?
What I want to do is export a heap of tables into one Excel file, but on 
different sheets. 
I can't do this as a linked table situation because deleting can't be done 
(Deleting not supported by this ISAM)

So what I want to do is, for example, 
transfer "Table1" to C:\Data.xls!Table1

but that doesn't work itself. Does anyone know how to get around this?

TIA

Steven

I really should think a bit more before I post here.
When you transfer a spreadheet, it gives the sheet the table name, so I 
can just transfer them all to the same file - 

I think I was dropped on the head as a baby - that MUST be the reason

You will want to use automation.  Download and install the following
help files on automation for the version of Access you are using.  They
provide excellent examples of what you are trying to do.

For Access97
http://support.microsoft.com/default.aspx?scid=kb;EN-US;q167223
 
For Access2K
http://support.microsoft.com/default.aspx?scid=kb;EN-US;q260410

For AccessXP
http://support.microsoft.com/default.aspx?scid=kb;EN-US;q302460

John V. Ruff - The Eternal Optimist :-)
Always Looking For Contract Opportunities

www.noclassroom.com
Live software training
Right over the Internet

Home:  xxx.xxx.xxxx
Cell:  xxx.xxx.xxxx
9306 Farwest Dr SW
Lakewood, WA 98498

"Commit to the Lord whatever you do,
    and your plans will succeed."  Proverbs 16:3





-----Original Message-----
From: Steven White [mailto:Steve.White@m...] 
Sent: Friday, July 19, 2002 4:47 AM
To: Access
Subject: [access] TransferSpreadsheet problems


Hi,

Does anyone know how I can export tables to an Excel file on a specific 
sheet?
What I want to do is export a heap of tables into one Excel file, but on

different sheets. 
I can't do this as a linked table situation because deleting can't be
done 
(Deleting not supported by this ISAM)

So what I want to do is, for example, 
transfer "Table1" to C:\Data.xls!Table1

but that doesn't work itself. Does anyone know how to get around this?

TIA

Steven

In addition to the other suggestions you might read this Knowledgebase
artilce about reading and writing to excel from ADO:


http://support.microsoft.com/default.aspx?scid=kb;EN-US;q257819

|-----Original Message-----
|From: Steven White [mailto:Steve.White@m...]
|Sent: Friday, July 19, 2002 4:47 AM
|To: Access
|Subject: [access] TransferSpreadsheet problems
|
|
|Hi,
|
|Does anyone know how I can export tables to an Excel file on a specific
|sheet?
|What I want to do is export a heap of tables into one Excel file, but on
|different sheets.
|I can't do this as a linked table situation because deleting can't be done
|(Deleting not supported by this ISAM)
|
|So what I want to do is, for example,
|transfer "Table1" to C:\Data.xls!Table1
|
|but that doesn't work itself. Does anyone know how to get around this?
|
|TIA
|
|Steven
|

> Hi,

> Does anyone know how I can export tables to an Excel file on a specific 
s> heet?
W> hat I want to do is export a heap of tables into one Excel file, but on 
d> ifferent sheets. 
I>  can't do this as a linked table situation because deleting can't be 
done 
(> Deleting not supported by this ISAM)

> So what I want to do is, for example, 
t> ransfer "Table1" to C:\Data.xls!Table1

> but that doesn't work itself. Does anyone know how to get around this?

> TIA

> Steven

I found this code on one of the Microsoft web pages.  I find it does the 
job perfectly:

Public Sub GetData()
    Dim dbs As DAO.Database
    Dim rstEmployees As Recordset
    Dim fldEmployees As Field
    Dim intCount As Integer
    Dim empLname As String
    Dim empFname As String
    Dim empTitle As String

    intCount = 1
    
    Set dbs = DBEngine(0).OpenDatabase("C:\Program Files" _
        & "\Microsoft Office\Office\Samples\Northwind.mdb")

    Set rstEmployees = dbs.OpenRecordset("Employees", dbOpenTable)

    ' Set header values for sheet 1.
    With Worksheets("Sheet1").Rows(9)
        .Font.Bold = True
        .Cells(1, 5).Value = "Last Name"
        .Cells(1, 6).Value = "First Name"
        .Cells(1, 7).Value = "Job Title"
    End With

    ' Loop through all records, sending selected fields to AddToSheet
    ' function one row at a time.
    Do Until rstEmployees.EOF
        Set fldEmployees = rstEmployees.Fields(1)  ' "LastName" field.
        empLname = fldEmployees.Value
        Set fldEmployees = rstEmployees.Fields(2)  ' "FirstName" field.
        empFname = fldEmployees.Value
        Set fldEmployees = rstEmployees.Fields(3)  ' "Title" field.
        empTitle = fldEmployees.Value
        intCount = intCount + 1
        Call AddToSheet(intCount, empLname, empFname, empTitle)
       
        rstEmployees.MoveNext
    Loop
    
    With Worksheets("Sheet1").Columns("E:G")
        .AutoFit

    End With
End Sub 


Public Function AddToSheet(intCount As Integer, empLname As String, _
    empFname As String, empTitle As String)

' This function adds recorset data to sheet one row at a time.
' Values are passed from GetData procedure.
' The intCount variable is incremented one each pass so a new
' row is used for each record.
    
    With Worksheets("Sheet1").Rows(intCount)
        .Cells(10, 5).Value = empLname
        .Cells(10, 6).Value = empFname
        .Cells(10, 7).Value = empTitle
    End With
    
End Function

Note You can refer to a Field object by using its name, as shown in the 
following line of code:
Set fldEmployees = rstEmployees.Fields("LastName")
You can also refer to a Field object by using its ordinal number in a 
collection. Members of most collections are numbered beginning with zero. 
The following line of code refers to the second field in the Fields 
collection of the Recordset object:
Set fldEmployees = rstEmployees.Fields(1)
The code example in this section refers to fields by their ordinal number.

Sorry, I cant remember the address for the page though.

Darren.

Forgot to say that the above code should be written in Excel rather than 
Access.