[AccessD] Export to Excel

Arthur, they don't even have to do that.  I use the filesystem object
whenever possible.  If the network guy has disabled that, then this will
have to be done another way.

Function CopyFile(StrFileName As String, strDestDir As String) As Boolean
On Error GoTo Err_CopyFile
Dim fs As FileSystemObject
    Set fs = CreateObject("Scripting.FileSystemObject")
    'On Error Resume Next
    'if the file doesn't exist, ignore the error
    fs.CopyFile StrFileName, strDestDir
    CopyFile = True
Exit_CopyFile:
On Error Resume Next
    Set fs = Nothing
Exit Function
Err_CopyFile:
        MsgBox Err.Description, , "Error in Sub basUtils.CopyFile"
        Resume Exit_CopyFile
    Resume 0    '.FOR TROUBLESHOOTING
End Function

John W. Colby
Colby Consulting
www.ColbyConsulting.com
  -----Original Message-----
  From: accessd-admin at databaseadvisors.com
[mailto:accessd-admin at databaseadvisors.com]On Behalf Of Arthur Fuller
  Sent: Monday, February 10, 2003 11:08 AM
  To: accessd at databaseadvisors.com
  Subject: RE: [AccessD] Export to Excel


  JC has provided you an excellent start, and I think all this can be
automated, so the end-user-involvement can be reduced (I think) to naming
the new file.



  -----Original Message-----
  From: accessd-admin at databaseadvisors.com
[mailto:accessd-admin at databaseadvisors.com] On Behalf Of Mark Boyd
  Sent: February 10, 2003 10:49 AM
  To: accessd at databaseadvisors.com
  Subject: RE: [AccessD] Export to Excel



  John -

  Thanks for the reply.

  This may be something we can work with.

  I'll run it by my boss, but I'm guessing there will be complaints about
too much user interaction.

  The thing that drives me crazy is there isn't that much interaction on the
user's part.  They have to move a few fields around, or re-total a field
here and there . not a big deal if you ask me.



  Thanks,

  Mark



  -----Original Message-----
  From: John W. Colby [mailto:jcolby at colbyconsulting.com]
  Sent: Monday, February 10, 2003 10:28 AM
  To: accessd at databaseadvisors.com
  Subject: RE: [AccessD] Export to Excel



  Mark,



  I think you are taking the long way around the farm.  I did a lot of
movement of data between Access and Excel down in Mexico for an app where
the users used Excel Analysis on the resulting data.  What we did is to
build queries that got the data that was desired, then exported that data
directly into the spreadsheet.  You can then record a macro on the
spreadsheet as you format the data the way you want it to run.  Save and
name the macro.  Now, whenever you need to run this process, Copy a
"template" spreadsheet that has this macro in it to a new name, export the
data into the new spreadsheet, then run the macro from Access to format the
data the way you want to see it.

  John W. Colby
  Colby Consulting
  www.ColbyConsulting.com

    -----Original Message-----
    From: accessd-admin at databaseadvisors.com
[mailto:accessd-admin at databaseadvisors.com]On Behalf Of Mark Boyd
    Sent: Monday, February 10, 2003 10:18 AM
    To: AccessD at databaseadvisors.com
    Subject: [AccessD] Export to Excel

    We are having an issue with exporting data from an Access report to an
Excel spreadsheet.

    It seems that whenever there are calculated fields such as report
totals, the data doesn't display properly after exporting.

    Also, some fields are moved way to the right, while others are way left.

    Does anyone know of 3rd party software that will export an Access report
to Excel as clean as possible?



    Thanks,

    Mark Boyd

    Sr. Systems Analyst

    McBee Associates, Inc.


-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://databaseadvisors.com/pipermail/accessd/attachments/20030210/be1ab0e2/attachment-0002.html>