When is a named range not a named range ?

[xlbo]

Ok - bit of an odd one to do with Named Ranges

The following code should
1: open up a .csv file
2: save it as an excel file
3: add a named range
4: save it again
5: close the new excel file
6: set up a query connection to this file
7: query that file using the range name set up in 3:

It all works fine except that I get a "General ODBC error" raised on the "Refresh" line

When I step through the process manually and examine what the code has done, it has created a named range if I look at the actual workbook but if I try to query that named range manually, the MSQuery GUI tells me that there are no named ranges in the workbook. :-(

I am sure this has something to do with setting range names programatically as if I set the range name manually and then run the query code it all works fine

Dim wb_BUSAR As Workbook 'Business Unit Setup Audit report
Dim TempPath As String
Dim TempName As String
[COLOR=green]'open csv file[/color]
Set wb_BUSAR = Workbooks.Open(ThisWorkbook.Path & "\" & "JDE_BUSetup.csv")

With wb_BUSAR
    [COLOR=green]'get path & name of file[/color]
    TempPath = .Path
    TempName = Left(.Name, Len(.Name) - 3)
    With .Sheets(1)
        lRow = .Cells(65536, 1).End(xlUp).Row
    End With
[COLOR=green]'save csv file as excel[/color]
    .SaveAs Filename:=TempPath & "\" & TempName & ".xls", FileFormat:=xlWorkbookNormal
[COLOR=green]'add the name[/color]
    .Names.Add Name:="sr_JDE_Data", RefersTo:="=" & .Sheets(1).Name & "!$A$3:$BB$" & lRow
    .Close (True)
End With

Set wb_BUSAR = Nothing

[COLOR=green]query string tested and works[/color]
mSQL = "SELECT sr_JDE_Data.`Business Unit`, sr_JDE_Data.Co " & _
    "FROM `W:\Knowledge\Outlet Nos\2005-2006\Period 1\JDE_BUSetup`.sr_JDE_Data sr_JDE_Data " & _
    "WHERE (sr_JDE_Data.`BU Ty`='PB') AND (sr_JDE_Data.`Property Status`='LIV') " & _
    "ORDER BY sr_JDE_Data.`Business Unit`"
[COLOR=green]'set the connection etc - as with the query string - this works if the named range is set manually[/color]
With sht_JDE.QueryTables(1)
    .Connection = "ODBC;DSN=Excel Files;DBQ=W:\Knowledge\Outlet Nos\2005-2006\Period 1\JDE_BUSetup.xls;DefaultDir=W:\Knowledge\Outlet Nos\2005-2006\Period 1;DriverId=790;MaxBufferSize=2048;PageTimeout=5;"
    .Destination = sht_JDE.Range("A1")
    .CommandText = mSQL
    .Name = "sr_JDE_Data"
[COLOR=green]'error occurs on next line]
    .Refresh BackgroundQuery:=False
End With

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[PHV]

if I set the range name manually
Have you macrorecorded that ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[xlbo]

Hi PHV - yes indeed. It does record slightly differently in that it uses R1C1 notation but it is exactly the same code and the above code fails on the last line whether using R1C1 or A1 style

The following is what is recorded when I set a named range manually:

ActiveWorkbook.Names.Add Name:="test_Range", RefersToR1C1:=
"=JDE_BUSetup!R3C1:R10805C54"

my code is:

with wb_BUSAR
   .Names.Add Name:="sr_JDE_Data", RefersTo:="=" & .Sheets(1).Name & "!$A$3:$BB$" & lRow

which converts to (based on watches)

with wb_BUSAR
   .Names.Add Name:="sr_JDE_Data", RefersTo:="=" & "JDE_BUSetup" & "!$A$3:$BB$" & "10805"

or

with wb_BUSAR
   .Names.Add Name:="sr_JDE_Data", RefersTo:="=JDE_BUSetup!$A$3:$BB$10805

any other thoughts ?


Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[xlbo]

If there was a great big fat D'OH symbol - now would be the time to use it !!

this line:
TempName = Left(.Name, Len(.Name) - 3)
was causing the error - returning a . at the end of the file name which was then transferred to connection settings and all over the place - worked when I set the name manually as I commented out thoselines as (stupidly) I thought that they couldn't possibly be causing the problem (plus an extra . in a long connection string is really hard to find)

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[rmikesmith]

Geoff,

It's comforting to know that even the Great Ones are fallible every now and then

(sorry, my shadenfreude is showing)

Regards,
Mike

 

[xlbo]

Mike - pretty sure you know much more about coding than me - at least it's a lesson for anyone that thinks they know where the bug in their code is - note to self - ALWAYS start from the start !!

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question