Skip to content
+1-888-319-3663

COMMUNITY FORUM

Excel dynamic range

Florence asked 7 years ago
Hi!

Having an issue with SmartConnect. I'm using an excel file to feed SmartConnect using an ODBC connection. My excel file is a form that generate a sheet formated with columns that I can send to smartconnect to open contracts/projects/budgets.

It's all working fine but… I chose to use the function OFFSET to set up dynamicly the range name of my sheet (so I dont have to do it manualy everytime). Looks like this:

=OFFSET(SmartConnect!$A$1,0,0,COUNTA(SmartConnect!$A:$A),12)

Sadly, smartconnect doesnt seem to like it cuz it throw an error (due to the dynamic range I guess since it's working when I do it manually).

[quote=]ERROR [42S02] [Microsoft][ODBC Excel Driver] The Microsoft Access database engine could not find the object 'SCRD'. Make sure the object exists and that you spell its name and the path name correctly. If 'SCRD' is not a local object, check your network connection or contact the server administrator[/quote]

Does anyone have an idea how I could manage to make it works? Because this form is going to be used by users and I dont want them to have to set up manually the range (they'll forget or wont know how to do it).

Thanks all!

Flo
Answers
Best Answer
Florence answered 7 years ago
It's a SQL referencing problem. Sounds like it can find the range if it's define with an OFFSET function. A bit lame but… Solved the problem using VBA again. So I can generate dynamic excel data sheets and they fit perfectly with SmartConnect using ODBC connection.

Now I have all I need to computerize a tons of long and laborious processes. What an amazing and powerful tool and this is so easy to use!

Thanks all! Have fun with your SC projects!



Example of VBA to define a name range!
[quote=]Worksheets("sheet1").Range("A1:F10").Name = "myRange"[/quote]

If you would like to submit an answer or comment, please sign in to the eOne portal.