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.