Trouble exporting to formatted file

[unclesvenno]

Hello All,

I have a button on a form which exports the result of a query to an Excel spreadsheet with the following code:

Private Sub cmdExport_Click()
On Error GoTo Err_cmdExport_Click

    ' Local Variables
    Dim db As Database
    Dim qDef As QueryDef
    Dim fPath As String
    Dim strFilter As String
    Dim strSaveFileName As String

    'Ask for SaveFileName
    strFilter = ahtAddFilterItem("", "Excel Files (*.xls)", "*.xls")
    strSaveFileName = ahtCommonFileOpenSave(OpenFile:=False, Filter:=strFilter, _
        Flags:=ahtOFN_OVERWRITEPROMPT Or ahtOFN_READONLY)
        
    'Test if Save As... cancelled
    If strSaveFileName = "" Then
        GoTo Exit_cmdExport_Click
    End If
    
    'Save to file
    Set db = CurrentDb
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel7, ("FileRepOrder"), strSaveFileName, True
    Set qDef = Nothing
    Set db = Nothing

    'Indicate task complete
    MsgBox "File " & strSaveFileName & " successfully saved"

Exit_cmdExport_Click:
    Exit Sub
Err_cmdExport_Click:
    MsgBox Err.Number & " " & Err.Description, vbCritical, "Error"
    Resume Exit_cmdExport_Click
End Sub

I would like to have the Excel spreadsheet formatted.

1 - Do I need to format the spreadsheet after I have exported the data?

or

2 - Can I export the data to a templated spreadsheet?

if so, how?

Thanks again,
Uncle Svenno

 

[PHV]

Have you tried to pull the data from excel (menu Data -> External data ->) ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[unclesvenno]

Hi PHV,

Thanks for your suggestion, I've had a look at it and it seems to dislike the set up I have. I have 5 back end .mdb files which sit on a shared server. The front end is an .mde file which sits locally on the users PC. I would need to pull the data from a query within the .mde file, apart from not liking .mde files (ie. if I were to use an .mdb file instead) it also doesn't like the fact the database file is open.

Ideally I would like to simply click the button (re: above code) and have the data exported and formated appropriately.

I have been playing around with it and I have an empty spreadsheet which is formated but when I export data to it, it creates the data on another worksheet in the same file, which of course is not formated. Leaving me with a spreadsheet with a formated blank worksheet and an un-formated worksheet with data in it. I can of course just copy and paste the data to the formated spreadsheet but that would not keep it as efficient as I would like.

Any further ideas/suggestions would be great.

Thanks,
Uncle Svenno

 

[payback]

Just trying to think laterally here, does the new worksheet created always have the same default name and is it always the same size (i.e. cells across and down)?

If so can you just set formulas on the formatted worksheet to read the raw cell information from the unformatted one.

 

[unclesvenno]

Hi Mr Payback,

The number of columns doesn't change but the number of rows does. I think I could make an estimate and suggest it would never exceed 60 rows.

Are you suggesting I ask Excel to read the data from the "additional worksheet" from within Excel and paste it into the formatted cells on the other worksheet? ie. from a macro assigned to a button or something?

Thanks again,
Uncle Svenno

 

[payback]

No, just link them e.g. you create a formula that automatically reads cells from the other worksheet. You can either link within the same book or link to another spreadsheet in a different file.

In the Excel Help index, look at the "link" options.

 

[unclesvenno]

Ohh I see, good idea, I'll give it a go and let you know the result.

Thanks a lot,
Uncle Svenno

 

[unclesvenno]

Unfortunately I cannot think of a way to make this work, if I try to link the cells I will need to have another worksheet to link them too. Seeing as the other worksheet isn't inserted until I export the query result I can't see how i could link them correctly.

I'm not keen to go into the spreadsheet and start copying and pasting or formating cells. I'm thinking I may have to just leave this for a while and work on something else.

Thanks for all your help and suggestions,
Uncle Svenno

 

[payback]

Create a seperate book for your formatted cells and a dummy book to create the links. Then rename your dummy to something else.

When you export, as long as you create your exported file in the same folder, and update the links (Excel will automatically ask you this when you open the formatted file) it should work OK.

In this case you are linking separate files which Excel handles as well as linking within the same book.

I use this all the time and know it works.