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

Thank you, Jim, this is great.  The TransferSpreadsheet method works
instantly!   On the export data function, I am pretty dumb about functions
vs private subs. ;-)  Would I set it up in the click property on the form
or in a public function module.  Does that allow the user to browse and
choose path/filename? If yes, then I would like to set it up, since the
TransferSpreadsheet does not.  However, if no, I will stay with your
TransferSpreadsheet since it works so beautifully, and have the user change
their process to open the excel sheet in the set location and save it to
their desired location.

This would be ok, since the user opens the sheet and makes revisions
anyway.  They would just have to save it to its final destination once they
made their changes, and each week they'd re-write it with new data.  Thank
you again, you have been extremely helpful,
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 05:59 PM         Problems                      
                             
                      Please respond to                                         
                             
                      mso                                                       
                       ..... 
                                                                                
                             
                                                                                
                             




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







-----------------------------------------------------------------------------------------------------
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
*************************************************************

Other related posts: