Copy cell contents to access form
 

Hi folks, I hope someone can help me....

I'm working on a macro to do the following from excel:

i) open an access database if it is not already open
ii) copy the contents of the active cell ("job number") in a worksheet into
an access form and then run a macro to view the related job details.

I am trying to use the Transfer Spreadsheet method but cannot get it to
work. I keep getting eror 3011.

Here's the code:
Sub RunAccessMacro()
Dim appAcc As Access.Application


'Open Access or use it if already running
Set appAcc = New Access.Application

'open desired database
With appAcc
.Visible = False
.OpenCurrentDatabase "C:\Documents and Settings\User\My
Documents\Works in progress\database stuff\Copy of job list.mdb"
.DoCmd.RunMacro "GenSearch" 'opens a search form
.DoCmd.TransferSpreadsheet acImport, , "Search",
"C:\data\control\stations.xls", , ActiveCell

^^^
This is where it goes wrong. Error 3011 says Jet Database Engine could not
find the object "011524A" (i.e. the contents of the ActiveCell)


.DoCmd.RunMacro "Search" 'macro to view job details
.Visible = True
End With
End Sub

Apologies for the scrappy code - it's been scavenged from all over the place
;).

Yours hopefully,

Marcel

Copy cell contents to access form
 

When your line for the transferspreadsheet says "ActiveCell", it's
referring to the active cell of the spreadsheet that's currently
open. This parameter of the TransferSpreadsheet method is looking for
the range that you'd like to import from the Excel file ("A1:B100" or
something like that). The way it's written, it's like you're trying
to move the data from the Excel file into a table in Access called
"Search".

Reading what you actually want to accomplish, it sounds like you might
need to dynamically change the parameters of a query, then display the
result in Access.
Without fully testing, that might look something like this:
With appAcc
.Visible = False
.OpenCurrentDatabase "C:\Documents and Settings\User\My
Documents\Works in progress\database stuff\Copy of job list.mdb"
.CurrentDb.QueryDefs("SearchQuery").Sql = "select * from
SearchTable where Job='" & ActiveCell.Value & "'"
.DoCmd.RunMacro "Search"
.Visible = True
End With

Copy cell contents to access form
 

Hi Jason,

thanks for the reply. (I didn't receive any notification but hey ho)

That looks like the kind of thing I'm after. I'm not familiar with QueryDefs
but I'll have a go at it and see what I can achieve.

Thanks, Marcel
"jasontferrell" wrote:

When your line for the transferspreadsheet says "ActiveCell", it's
referring to the active cell of the spreadsheet that's currently
open. This parameter of the TransferSpreadsheet method is looking for
the range that you'd like to import from the Excel file ("A1:B100" or
something like that). The way it's written, it's like you're trying
to move the data from the Excel file into a table in Access called
"Search".

Reading what you actually want to accomplish, it sounds like you might
need to dynamically change the parameters of a query, then display the
result in Access.
Without fully testing, that might look something like this:
With appAcc
.Visible = False
.OpenCurrentDatabase "C:\Documents and Settings\User\My
Documents\Works in progress\database stuff\Copy of job list.mdb"
.CurrentDb.QueryDefs("SearchQuery").Sql = "select * from
SearchTable where Job='" & ActiveCell.Value & "'"
.DoCmd.RunMacro "Search"
.Visible = True
End With