Export from Access to specific cells in Excel

[kbestle]

I would like to export a single table record out of Access into Excel. I would want to take each field in Access and put the data in a specific cell in Excel. Example: The FirstName field goes into cell F3, LastName goes into G3 etc. I want to export this into an exisiting Excel file that already has data in the other cells. If this is not feasable than my second choice would be to export this data into a second worksheet in that same Excel file(template). Than I can just reference those cells from the target worksheet. Thanks in advance.

Kent

 

[ThomasLafferty]

Where do you want the primary control to reside? Access or Excel? In other words, how are you most likely to initiate the transfer process? Do you want to do it from a click button in Excel, in which case Excel is fetching the data, or do you want to have it happen from the click event of a form in Access, in which case Access is in control.

Tom

Born once die twice; born twice die once.

 

[kbestle]

Tom,

The control will be in Access. I would want a button on a form so the when the user was satified that the form was showing the required data they could click to move the data to Excel.

Thanks
Kent

 

[ThomasLafferty]

OK - let's say that all of your data is loaded in text boxes on your form, and that you have a command button named something like btn_MoveData. In the click event of your button, try something like this to get the values of the controls loaded into a variable which you will then use to read the values into Excel at the specified location. It would help to know if they all reside in the same spreadsheet row, the same spreadsheet column or if your data will be spread across the sheet.

Private Sub btn_MoveData_Click()
[COLOR=green]'*****VARIABLE DECLARATION*****[/color]
Dim obj As Object, wbk as Workbook

On Error GoTo Err_btn_MoveData_Click

For each c in Controls
     If c.AcControlType = AcTextBox Then
          ValToMove = c.Value
          [COLOR=green]'Code to move data here[/color]
     End If
Next c

Exit_btn_MoveData_Click:
     Exit Sub

Err_btn_MoveData_Click:
     Msgbox Err.Description
     Resume Exit_btn_MoveDataClick

End Sub

You will likely need a shell to open your workbook so that Access is running the show, but maybe this is enough to get you started?

Tom

Born once die twice; born twice die once.

 

[Griffyn]

Have you tried setting up a link table of the excel sheet within your access database? That way you can then refer to the excel sheet as if it were a table, and insert and delete rows and data with normal queries.