Using access to feed data into Excel

  • Thread starter Thread starter Colin Foster
  • Start date Start date
C

Colin Foster

Hello all,
I've managed to write the code to open up an excel workbook from within
access and then go to a specified named range. However, once that has
happened, I need some specific data to be automatically input into the excel
workbook.
I can make this work in word (having received copious help from this NG) by
using bookmarks, so I've tried a similar route using named ranges in excel &
got so far, then hit the brick wall that probably means I'm almost there!

The code that I'm using is as follows...

Dim oexcel As Object
' start Microsoft Excel.
Set oexcel = CreateObject("Excel.Application")

With oexcel ' Make the application visible.
.Visible = True
' Open the workbook.
.workbooks.Add ("C:\GBTDatabase\Templates\Precast Concrete Production
Sheet.xlt")
' Move to each bookmark and insert text from the form.
ActiveWorkbook.Names("Project").RefersTo
(CStr(Forms!FrmProjects.ProjectName))


End With

Its the last bit of "RefersTo" that I think that I need the help with.
At the moment, if I run this code, the excel workbook opens & the cursor is
sat at the range "Project", however, back in Access, I receive an error of
424. Object required.

Thanks for any help
Regards
Colin Foster
 
Unfortunately, Access doesn't know what ActiveWorkbook is, since you haven't
qualified it.

..ActiveWorkbook.Names("Project").RefersTo(CStr(Forms!FrmProjects.ProjectName
))

might work. The difference is the period in front, so that it's really

oexcel.ActiveWorkbook.Names("Project").RefersTo(CStr(Forms!FrmProjects.Proje
ctName))

However, I just noticed that I've always got the equivalent of

oexcel.Application.Workbooks.Add ("C:\GBTDatabase\Templates\Precast Concrete
Production Sheet.xlt")
oexcel.Application.ActiveWorkbook.Names("Project").RefersTo(CStr(Forms!FrmPr
ojects.ProjectName))

in my code.

I'm also not sure what you expect to have happen with that 2nd statement,
though. RefersTo is a property that "returns or sets the formula that the
name is defined to refer to". You need to either assign it to a variable (if
you want to know what it currently is), or assign it a value (if you want to
change it)

Perhaps you need

oexcel.Application.ActiveWorkbook.Names("Project").RefersTo = "=" &
CStr(Forms!FrmProjects.ProjectName)

(assuming Forms!FrmProjects.ProjectName contains something like
Sheet1!R1C1:R3C3, or some other way of expressing a range)
 
Hi Doug,
Thanks for the swift response.
The result that I'm hoping to achieve is that, lets call the current project
"Project1", then when I click the relevant button on a form to which this
code is attached (via the OnClick property), excel opens with a new document
based upon the "Precast Concrete Production Sheet.xlt" template and inputs
(automatically!) the name "Project1" in the named range (within the
spreadsheet) "Project".

In word, using bookmarks, this works with the following code...

".ActiveDocument.Bookmarks("Project").Select
.Selection.Text = (CStr(Forms!FrmProjects.ProjectName))"
But , of course (!), excel has to be different!!

Regards
Colin
 
Colin,

Look at the TransferSpreadsheet function in the Help file. It allows you to add
data from a Table or Query to a specific range in Excel.
 
Back
Top