Access to Excel

[RexesOperator]

I guess I should have started a new thread on this to avoid confusion. However it is a continuation of the same topic.


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 data in the textbox called 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. I found it through Google.

 

[RexesOperator]

Does anyone have any ideas?

If this shows up multiple times I apoogize in advance. I seem to be having problems posting.

 

[midmented]

I think I helped you once before? I can't remember.

Anyway:

Dim oSheet As Excel.Worksheet
Set oSheet = objXLBook.Worksheets(1)
'Whichever worksheet you want to refer to is the number

 

[KeithG]

Which line of code is giving the error?

 

[RexesOperator]

I think I helped you once before? I can't remember.

That could be - I have had a lot of help from a lot of kind people

Dim oSheet As Excel.Worksheet
Set oSheet = objXLBook.Worksheets(1)
'Whichever worksheet you want to refer to is the number

Didn't work. I get an error on
Dim oSheet As Excel.Worksheet

"User defined type not defined"

 

[RexesOperator]

Which line of code is giving the error?

I get a runtime error on:

objExcelBook.ActiveSheet(1).Range("B9") = Me.txtNAME

(I changed the name of the text box so there is less confusion)

It says Object required.

 

[midmented]

Dim oSheet As Excel.Worksheet
Set oSheet = objXLBook.Worksheets(1)
oSheet.Range("B9") = me.NAME

If that is a cell you want to fill then:
omit oSheet.Range("B9") = me.NAME and use:
oSheet.Cells(2, 9).Value = Me.Name

 

[midmented]

Reference

Also, it sounds like you might not have a reference set for Excel. Have you ran any of this code successfully yet?
Check your references. Open a code module, Tools, references and make sure you have a reference to Microsoft Excel.

If not, check the checkbox and try again.

 

[midmented]

After references

I guess I'll post the code the way I think it should be:

Private Sub LoadInfo_Click()

Dim objXLApp As Object
Dim objXLBook As Excel.Workbook
dim oSheet as Excel.Worksheet

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

oSheet.Cells(2, 9).Value = Me.txtName
objXLBook.Save

objXLBook.Close
set objXLApp = nothing
set objXLBook = nothing
set oSheet = nothing

End Sub

 

[RexesOperator]

I'm not ignoring you - I had to do some errands. I will check back with what you this evening.

 

[RexesOperator]

Dim objXLBook As Excel.Workbook

gives a user-defined type not defined error

 

[RexesOperator]

The problem still seems to be with "Excel.Workbook". There are only 11 fields involved, so I will stick with copy and paste for now.

 

[midmented]

It still sounds like a reference problem. Read the post I posted earlier.

 

[RexesOperator]

You might have better luck with a sample db. I am still getting the user-defined type not defined error.

 

[midmented]

Sample DB attached

Access 2003 attachment.
See example in the attached db. It worked fine for me. I didn't change any of the code. As I said, I think your reference for Excel is not set.

 

[RexesOperator]

Your code doesn't work either. I don't get the error message, but nothing happens

I tried copying your code directly into mine, changing only the field name - and I still get the user-defined type undefined error.

I have also done some more searching in Google and other forums, but can't find this specific problem - ie transfering individual fields from Access to Excel.

Only 11 fields are involved, so I think copy and paste will have to do.

It occurs to me that the spreadsheet has a data entry form that pops up when it is opened. Could this interfere with the data transfer?

 

[Mike375]

midmented's sample works for me.

Are you using his actual sample or copying the code. If you are copying (or importing from his sample) you won't get the reference to Microsoft Excel 11Object Library.

Although when the Microsoft Word Object Library is missing I get debug and the highlighted section. I get that when I import into a blank DB and forget to select Microsoft Word 11 Object in the references.

 

[RexesOperator]

FINALLY! I owe you and midemented at least a cup of Tim Horton's for this. Not sure if there are any Tim Horton's down under. (I have a friend going in October - maybe you could meet?)

Now that I understand what references you were talking about, everthing works.

One last question for other viewers - eventually the database will be split. Will all network users need to load the Microsoft Excel 11Object Library, or just the BE?