By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,984 Members | 1,461 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,984 IT Pros & Developers. It's quick & easy.

Need help - Access 2003 VBA - range values

P: 2
Hi,

I'm trying to debug some code where I'm trying to retrieve particular ranges in an excel sheet into an Access table, depending on the user selection of the range name in a combo box with a list of range names.

I have already named the ranges I need in the target Excel sheet as follows :

range1 (where range1 =$A$1:$E$10

range2 = $H$1:$L$10 and so on

The combo box control is named Combo1 on a form called select_range

Once the user opens the form and clicks on range1 and then OK, The row source for the combo box is a table containing the rangenames (text fields).

So, if the user clicks on the first value in combo box, range1 where range1 = Sheet1!range1, it should replace the rng String with the value Sheet1!range1 in the command below.

Alernatively, if the user clicks range2, it should replace this with Sheet1!range2 in the command below.


I want the following command executed :

Private Sub Command0_Click()

Dim rng as String

rng = Me.Combo1.Column(0)

DoCmd.TransferSpreadsheet transfertype:=acImport, SpreadsheetType:=5, _
tablename:="Test", Filename:="c:\test.xls", _
Hasfieldnames:=True, Range:="rng"


End Sub

When I execute this, I get a Run-time error 3011, where access says it cannot find the object 'rng".

Can anyone here help debug this ? Any help here would be appreciated.

Thanks in advance.
Mar 9 '07 #1
Share this Question
Share on Google+
2 Replies


10K+
P: 13,264
Moved to access forum.
Mar 10 '07 #2

ADezii
Expert 5K+
P: 8,619
Hi,

I'm trying to debug some code where I'm trying to retrieve particular ranges in an excel sheet into an Access table, depending on the user selection of the range name in a combo box with a list of range names.

I have already named the ranges I need in the target Excel sheet as follows :

range1 (where range1 =$A$1:$E$10

range2 = $H$1:$L$10 and so on

The combo box control is named Combo1 on a form called select_range

Once the user opens the form and clicks on range1 and then OK, The row source for the combo box is a table containing the rangenames (text fields).

So, if the user clicks on the first value in combo box, range1 where range1 = Sheet1!range1, it should replace the rng String with the value Sheet1!range1 in the command below.

Alernatively, if the user clicks range2, it should replace this with Sheet1!range2 in the command below.


I want the following command executed :

Private Sub Command0_Click()

Dim rng as String

rng = Me.Combo1.Column(0)

DoCmd.TransferSpreadsheet transfertype:=acImport, SpreadsheetType:=5, _
tablename:="Test", Filename:="c:\test.xls", _
Hasfieldnames:=True, Range:="rng"


End Sub

When I execute this, I get a Run-time error 3011, where access says it cannot find the object 'rng".

Can anyone here help debug this ? Any help here would be appreciated.

Thanks in advance.
I'm pretty sure that the problems lies with the Sheet1! qualifier. Simply specify the Range Name without the qualifier, e.g. rng = "Range1", and you should be OK. Let me know how you make out.
Mar 11 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.