Saving Excel copy of form data with VBA

[stage73]

Hi All

I have a form FrmAuditClientTransactions that populates from a query displaying transaction line items for a given client. I have used a form so that the user can type into text boxes in the footer which then get written to the client table by clicking a button.

I also want the code to save an Excel copy of the form data so I have looked into :

[FONT=Arial][SIZE=3]DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, Forms.FrmAuditClientsTransactions.Form.RecordSource, "C:\Sbapps\test.xlsx", True, ""[/SIZE][/FONT]

After extensive Googling I’m getting the feeling that the TransferSpreadsheet method works with queries and reports but not with forms – am I right ?

If anyone could find a moment to point me in the right direction of how to save an excel copy of the form using VBA I would be ever so grateful.

Kind Regards
Chris

 

[CJ_London]

You are quite correct about transferspreadsheet.

Why do you want something that looks like a form exported to Excel?

I would look at using the Excel object along the lines of below so you can 'map' the form to excel. This example copies the contents of a recordset to the second worksheet in the workbook.

Note that the spreadsheet already exists as a template and has been formatted for the look required (in your case, looking like your form). Once populated it is then saved under a different name.

This is copied straight from a routine I have (names changed to protect the innocent) and has preceding code which sets up the values to be exported

Dim Ebk As Object
Dim ESht2 As Object
 
Set EApp = CreateObject("Excel.Application")
    Set Ebk = EApp.Workbooks.Open(Sys_Export_FilePath & "Template_" & FRst.Fields("xLFormName") & ".xlsx")
        Set ESht2 = Ebk.worksheets(2)
 
With ESht2
            .select
            .rows(Right(FRst.Fields("DataRange2"), 1) & ":1000000").ClearContents
            .range(FRst.Fields("DataRange2")).copyfromrecordset P2Rst
            .columns.autofit
            .range(FRst.Fields("DataRange2")).select
 
End With  
 
        Ebk.SaveAs Sys_Export_FilePath & Sys_Current_Year & "\" & StrConv(Replace(Replace(Vrst.Fields("CustName"), "\", ""), "/", ""), vbProperCase) & "_Stmt_" & Vrst.Fields("CustNo") & "_" & Sys_Period & "_" & Sys_DocCount & ".xlsx"

 

[stage73]

Thanks CJ, your input is much appreciated.
Apologies, I'm very new to this and teaching myself...
So I'm getting the code to copy the records from the current form to an Excel spreadhseet ? - How do I tell Access what I'm copying ? Is this the bit where you detail:
copyfromrecordset P2Rst

You speak of 'preceding code which sets up the values to be exported' - How do you do this ? Do you have to detail each of the fields separately ?

Thanks again for you input. Much appreciated.

 

[CJ_London]

Copyfromrecordset is a standard function

here is a link to explain it

http://msdn.microsoft.com/en-us/library/office/aa223845(v=office.11).aspx

not sure if it works with 2003 (if that is what you are using) but you can investigate

With regards the preceding code this would be the creation of a recordset - identified as P2rst in the code I sent you, so you might have

set P2rst=mydb.openrecordset("Select * from SomeTable") to gather the data to be entered into the spreadsheet

In addition, in my case. the FRst recordset referred to in the code opens a table which lets me know where to put each value in Excel. so the table might be populated with


ExlLocation FieldName
"A1" CustomerName
"B5" CustomerID

set FRst=mydb.openrecordset("Select Exlocation, Fieldname from tblFormprofile where formname='" & formname & "')

So the code determines to put the value in the customername field from your P2rst recordset into cell A1 of the spreadsheet and the customerid field value in cell B5. In this case you do need to detail each field separately

or in the case of the code supplied, using Copyfromrecordset it places all of the data in the P2rst recordset in one go rather than assigning field by field i.e. creates a table view. So if the datarange2 value =b2 and the R2Rst recordset is 5 columns wide and 10 deep it will populate cells B2:K12. In this case you do not detail each field separately

I would think of all your data in terms of recordsets - either created as above or by reference to your form if it is open or a combination of both depending on what your actual requirements are.

 

[LPurvis]

I've a long standing generic function to do that recordset insert work for you.
CopyFromRecordsetPassed. Same concept, wrapped up re-usably.
http://www.utteraccess.com/forum/Export-Excel-Sql-t1976533.html
You'll see how to call it there too.
You should be able to see that site no problem without joining.
Though if you prefer, I see (thanks o Google) the function has also been suggested here at AWF.
http://www.access-programmers.co.uk/forums/showthread.php?t=150129

 

[LPurvis]

And yes, CopyFromRecordset works in 2003. As it's part of Excel's object model and as it accepts either a DAO or ADO recordset, then by the point of calling it, Access as a data source is essentially redundant.

 

[stage73]

Thanks so much CJ and Lpurvis.
Greatly appreciated. You're goodn's !
Kind regards
Chris