Exporting one value from a Access data entry Form to Excel

  • Thread starter Thread starter morry
  • Start date Start date
M

morry

Hello

I am not real good with VBA in Access so I was hoping someone coul
tell me how to write the code for this

I have an Access Form that is used as a data entry form by customers.
I have a field that is an auto number field. I have a button on m
Access Form that is a hyperlink to an Excel document (the document i
not embedded in Access). I need to export the current value in th
auto number field to a cell in the Excel document. I want to put th
code into the button so when it is clicked that auto number will b
exported

I tested it with the export function in Access and I can do i
manually but I am not sure how to write the code for it

The way I did it manually is:

I had the form open and typed values into the first few text boxes.
This populated the Auto number box with a new value. Then I went t
FILE --> EXPORT. I change the "Save as Type" to Microsoft Exce
97-2003. Then I clicked Export All. This saved a Excel spreadshee
to My Documents with the same name as the form. All of th
information that I had typed into the form before exporting was save
in the Excel document

Can anyone tell me how to write the code to do this

Thanks

Morr
 
Hi Morry,

If you just want to put the current value of the autonumber field into a
particular cell on a particular sheet in the Excel workbook, the
simplest way is probably to build and execute a little SQL update query.

However, I don't know what you mean when you say (a) that the button "is
a hyperlink to an Excel document" and (b) that you want the autonumber
to be exported when the button is clicked. I'll assume that you have the
path to the Excel document in a hyperlink field called XLHyper, which is
displayed in a textbox called txtXLHyper, and that the autonumber is
displayed in txtID. On that basis, you need something like this air
code in the button's Click event procedure:


Dim strXLPath As String
Dim strSQL As String
Dim strCell As String


'get the path from the hyperlink
strXLPath = hyperlinkpart(Me.txtXLHyper.Value, acAddress)
'if it's not a hyperlink field but a text field, use this:
'strXLPath = Me.txtXXX.Value
'where txtXXX is the name of the textbox.

'say where you want it to go
strCell = "Sheet1$C4:C4" 'specify a single cells, using your
'actual sheet name and cell address

'build the SQL statement for the query
strSQL = "UPDATE [Excel 8.0;HDR=NO;Database=" _
& strXLPath & ";].[" & strCell & "] SET F1=" _
& Me.txtID.Value & ";"

'execute the query to write the value to the worksheet
CurrentDb.Execute strSQL, dbFailOnError




On Fri, 24 Jun 2005 19:38:28 -0500,
 
Back
Top