Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Display contents of a cell in a user form text box -- Excel 2003 VBA | Excel Discussion (Misc queries) | |||
Copy Contents from Cell | Excel Programming | |||
copy cells contents to access form | Excel Programming | |||
how to access or copy excel form controls (eg. dropdownlist) values to a database | Excel Programming | |||
From excel - open word doc and copy form field contents to excel c | Excel Programming |