[mso] Re: ACCESS 2002: Export Table To Excel Problems

Cathy--

As usual, I'm lost. ;-) No, I'm not sure what if any OS configuration
settings there could be which would cause the problem you're addressing.
It could be you have something goofy in your ISAM drivers. But there's
always more than a single method useful in the process of de-furing a
feline. To wit, here are a couple more suggestions.

1) Try out the TransferSpreadsheet method as follows:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "MM_CL",
"FilePathAndName"

Note that TransferSpreadsheet -- unlike OutputTo -- requires a filename.
Also, you have to use a table or a saved query; you can't pass it a
dynamic SQL statement via VB (so you're okay here).

2) After messing around a few years agowith OutputTo and its balky
brethren, I wrote the following code, and started using it for all my
Access-to-Excel exporting needs. I'm sure it can be improved, but it
works as is. Please note that it uses DAO; anyone using it now might
rewrite it for ADO. Also, as is, it only accepts tables and saved
queries as input:

Function fExportData(strTableOrQueryName As String, strFileName As
String, fHeaderRow As Integer)
  ' Call it like this:
  ' fExportData "qryToBeExported", "C:\Data\DataThatWasExported.csv", -1

  On Error GoTo Err_Export
  Dim intFileNum As Integer
  Dim rst As DAO.Recordset

  Dim fTableDef As Integer
  Dim fQueryDef As Integer
  Dim dbs As DAO.Database
  Dim qdf As DAO.QueryDef
  Dim tdf As DAO.TableDef
  Dim fld As DAO.Field
  Dim flds As DAO.Fields
  Dim aFields() As String
  Dim lngLoop As Integer
  lngLoop = 1
  Dim varData As Variant
  Dim strData As String
  Dim strWrite As String

  Set dbs = CurrentDb

  For Each tdf In dbs.TableDefs
    If tdf.Name = strTableOrQueryName Then
      fTableDef = True
      GoTo ContinueWithTable
    End If
  Next

  For Each qdf In dbs.QueryDefs
    If qdf.Name = strTableOrQueryName Then
      fQueryDef = True
      Exit For
    End If
  Next

ContinueWithTable:
  If fTableDef Then
    Set tdf = dbs.TableDefs(strTableOrQueryName)
    Set flds = tdf.Fields
  ElseIf fQueryDef Then
    Set qdf = dbs.QueryDefs(strTableOrQueryName)
    Set flds = qdf.Fields
  Else
    GoTo Exit_Export
  End If

  ReDim aFields(flds.Count)
  For Each fld In flds
    aFields(lngLoop) = fld.Name
    lngLoop = lngLoop + 1
  Next

  On Error Resume Next
  Kill strFileName
  On Error GoTo Err_Export

  intFileNum = FreeFile(0)
  Open strFileName For Append As #intFileNum

  If fHeaderRow Then
    For Each fld In flds
      strWrite = strWrite & fld.Name & ","
    Next
    Print #intFileNum, Left(strWrite, Len(strWrite) - 1)
    strWrite = vbNullString
  End If

  Set rst = dbs.OpenRecordset(strTableOrQueryName)
  With rst
    Do While Not .EOF
      For lngLoop = 1 To UBound(aFields)
        varData = .Fields(aFields(lngLoop)).Value
        If IsNull(varData) Then
          varData = vbNullString
        End If
        strData = varData
        If Not IsNumeric(strData) Then
          strData = ReplaceWord(strData, ",", "-")
          strData = ReplaceWord(strData, Chr(34), "'")
        End If
        strWrite = strWrite & strData & ","
      Next
      Print #intFileNum, Left(strWrite, Len(strWrite) - 1)
      strWrite = vbNullString
      .MoveNext
    Loop
  End With

Exit_Export:
  On Error Resume Next
  Close #intFileNum
  rst.Close
  Set rst = Nothing
  Set qdf = Nothing
  Set tdf = Nothing
  Set dbs = Nothing
  Exit Function

Err_Export:
  MsgBox ("Error")
  Resume Exit_Export

End Function



--Jim

-----Original Message-----
From: mso-bounce@xxxxxxxxxxxxx [mailto:mso-bounce@xxxxxxxxxxxxx] On
Behalf Of Cathy.Evans@xxxxxxxxx
Sent: Thursday, March 25, 2004 9:48 AM
To: mso@xxxxxxxxxxxxx
Subject: [mso] Re: ACCESS 2002: Export Table To Excel Problems



Thanks Jim, this gets me farther, it opens the file in excel, which my
original file created by my code would not even open.  However, this new
code still takes a long time (3 1/2 minutes from start of export to
opening the document in excel vs. 6 seconds when you right click on the
table and manually export).

I'm wondering if there is something not configured right in my set up or
what is happening different when it's exported from both methods.  I
have learned the OS images they give us sometimes have things turned off
for various security/control issues, and I would not know what aspects
of the Access/XP/Excel interface to even look for to see if that aspect
has been turned off for us (does that make sense?).

Would the SetMenuItem function work in this situation and if so, how
would I set it up? I have never used it and don't know what it can/can't
do - is it worth testing? I got my original code by creating a macro,
then converting the macro to code on the form.  I am really grabbing at
straws here, as I don't know why there is such a discrepancy between 2
methods of doing the same task.  Hope I'm explaining ok, thanks, Cathy


 

                      "Jim Pettit"

                      <j_e_pettit@hotmail      To: <mso@xxxxxxxxxxxxx>

                      .com>

                      Sent by:                 cc:

                      mso-bounce@freelist

                      s.org                    Subject:  [mso] Re:
ACCESS 2002: Export Table To Excel  
                      03/25/2004 11:50 AM         Problems

                      Please respond to

                      mso
.... 
 

 





Cathy--

You say you're running Access 2002? Try the following line instead and
se if it gives you what you want:

DoCmd.OutputTo acOutputTable, "MM_CL", acFormatXLS, "", True

(Of course, by leaving the 4th parameter -- filename -- blank, you'll be
prompted for a file save path and name. And the last two parameters can
be omitted, since you're using the default values anyway.)

--Jim





------------------------------------------------------------------------
-----------------------------
The information transmitted is intended only for the person
or entity to which it is addressed and may contain confidential and/or
privileged material. If you are not the intended recipient 
of this message you are hereby notified that any use, review,
retransmission, dissemination, distribution, reproduction or any action
taken in reliance upon this message is prohibited. If you 
received this in error, please contact the sender and delete the 
material from any computer.  Any views expressed in this message are
those of the individual sender and may not necessarily reflect 
the views of the company.  
------------------------------------------------------------------------
-------------------------------

*************************************************************
You are receiving this mail because you subscribed to mso@xxxxxxxxxxxxx
or MicrosoftOffice@xxxxxxxxxxxxxxxx

To send mail to the group, simply address it to mso@xxxxxxxxxxxxx

To Unsubscribe from this group, send an email to 
mso-request@xxxxxxxxxxxxx with the word "unsubscribe" (without the
quotes) in the subject line.

Or, visit the group's homepage and use the dropdown menu.  This will
also allow you to change your email settings to digest or vacation (no
mail). http://www.freelists.org/webpage/mso

To be able to use the files section for sharing files with the group,
send a request to mso-moderators@xxxxxxxxxxxxx and you will be sent an
invitation with instructions.  Once you are a member of the files group,
you can go here to upload/download files:
http://www.smartgroups.com/vault/msofiles
*************************************************************
*************************************************************
You are receiving this mail because you subscribed to mso@xxxxxxxxxxxxx or 
MicrosoftOffice@xxxxxxxxxxxxxxxx

To send mail to the group, simply address it to mso@xxxxxxxxxxxxx

To Unsubscribe from this group, send an email to 
mso-request@xxxxxxxxxxxxx with the word "unsubscribe" (without the quotes) in 
the subject line.

Or, visit the group's homepage and use the dropdown menu.  This will also allow 
you to change your email settings to digest or vacation (no mail).
http://www.freelists.org/webpage/mso

To be able to use the files section for sharing files with the group, send a 
request to mso-moderators@xxxxxxxxxxxxx and you will be sent an invitation with 
instructions.  Once you are a member of the files group, you can go here to 
upload/download files:
http://www.smartgroups.com/vault/msofiles
*************************************************************

Other related posts: