How do I use an excel workbook as a data source without a named range?

[Stever1]

Hello, 

I have some excel workbooks that I'd like to use as a datasource in a DataFlux 2.5 data job. I found a way to do it by creating an ODBC connection to the workbooks but I have to go into each one and make a named range for DataFlux to read. The problem is, each time the workbook is updated the data gets entered into a new sheet named "Current" and the old sheet is renamed with the date. This means I'd have to go in each time the workbook is updated and create a new named range. Is there a way to have the whole workbook as a datasource without creating a named range?

I appreciate any help!

[RonAgresta]

Hi,

I'm not aware of any option in SAS Data Management Studio ("DataFlux") to do what you have described. Named ranges are a requirement of the ODBC driver. Have you explored any macro options in MS Excel that might automatically create or copy a named range when the new sheet is created?

Ron

[Stever1]

Thanks for the reply Ron. I didn't realize a named range was a requirement for the ODBC driver. I have a macro like you said but I was going to use it as one of my backup plans. I was trying to avoid messing with the workbook since I'm not the owner. I'll have to pick whether to use a macro or save as csv files. Thanks for the help!

[SASKiwi]

How about creating a CSV file of the Current sheet and importing that instead?

[Stever1]

That's how I am currently doing it. I was just trying to avoid using macros or going out and saving as a csv each time the workbook is updated. Thanks for the help!

[SASKiwi]

You have more options if you were to import your workbook using SAS and SAS/ACCESS to PC Files instead of Dataflux. Is this a possibility?

[Stever1]

I think the only SAS tool I have access to is DataFlux (I don't know for sure, I am somewhat new to the company and SAS). But I think I'm going to stick with your suggestion about creating the CSV and importing that way.

[SASKiwi]

OK. Perhaps you could ask a SAS / Dataflux Administrator what SAS products are available at your company in case you want to try other options in the future.