Access to Excel and back again

[RexesOperator]

Is this going from Excel to Access? Or from Access to Excel?

 

[Mike375]

It inserts the values of the Excel cells into the unbound text boxes on the Access form....so Excel to Access.

 

[RexesOperator]

OK.

I must apologisze. I didn't even see the attachments. I was looking at the Office 2003 Editions Resource Kit. I will be back at this next week. I am off for a few days.

Thanks for your efforts.

 

[RexesOperator]

It inserts the values of the Excel cells into the unbound text boxes on the Access form....so Excel to Access.

I had a few minutes to play with this this morning and it works like a charm.

Two notes of caution for other users:

The Excel file must be open, and the subdirectories of the address for the Excel file can't have any spaces in the name: files in the \Documents and Settings\ subdirectory will cause an error.

 

[Mike375]

It should bring up a message box asking for a Yes or No on whether to open Excel as you are using DDE or DDESend.

I have found with DDE and DDESend that even if you first open the Excel or Word file in question and then open the Access form the message box will still pop up.

 

[RexesOperator]

I think I am very close to getting Access to load data from a form into a spreadsheet, but I am missing something.

The code below should load the field NAME into cell B9.

After doing some more research, I have this cobbled together this code for two command buttons - OpenExcelFile and LoadInfo. The first button should open the excel file Q11. It does.

The second button should load the data from the Access form into the worksheet. I am getting a runtime error '424' "Object required".

I think I need to identify the worksheet (Tombstone Data) in the workbook. How do I do that?

Private Sub OpenExcelFile_Click()
On Error GoTo Err_OpenExcelFile_Click

Dim objXLApp As Object
Dim objXLBook As Object

Set objXLApp = CreateObject("Excel.Application")
Set objXLBook = objXLApp.Workbooks.Open("C:\Q11.xls")

objXLApp.Visible = True
objXLApp.UserControl = True


Exit_OpenExcelFile_Click:
Exit Sub

Err_OpenExcelFile_Click:
MsgBox Err.Description
Resume Exit_OpenExcelFile_Click

End Sub

Private Sub LoadInfo_Click()
Dim objXLApp As Object
Dim objXLBook As Object

Set objXLApp = CreateObject("Excel.Application")
Set objXLBook = objXLApp.Workbooks.Open("C:\Q11.xls")

objExcelBook.ActiveSheet.Range("B9") = Me.NAME

End Sub

This is thanks to some code borrowed from Bob Larson in another forum