[AccessD] Export to Excel

Why do you go to all that trouble rather than just using the built in
statement FileCopy source, destination ?

 

On 10 Feb 2003 at 11:37, John W. Colby wrote:

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


-- 
Stuart McLachlan
Lexacorp Ltd
Application Development,  IT Consultancy
http://www.lexacorp.com.pg