Reading dbf files

[Luis Verme]

Hi:
In Excel 2003 in Spanish version, when I open a file with "dbf"
extension, Excel assigns by default a name to the range which is "Base de
Datos". When I try to select this name, a message appears saying there´s an
error with the name. Investigating, I saw that the range names must not have
blank apaces between the words, so the default name must be "Base_de_Datos"
I think this is a bug in the Spanish version of Excel. In the Portuguese
version the default name is "Banco_de_dados".
I use dbf files in Excel to work with Pivot Tables.

Any suggestions

Thanks in advance

Luis Verme

 

[keepITcool]

yep:
that looks like a bug as range names cannot have spaces.

can you select it from the dropdown?

if so you have a good chance that
the english name can be used in code....and only the "local" name is a
problem..

Try
?Range("Database").address
?Names("Database").namelocal


--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Luis Verme wrote :

 

[Luis Verme]

Thank you for your reply.
The name "Base de Datos" appears in the dropdown but when I select it an
error appears saying it's not a valid name.
The results in the Inmediate Window:

?Range("Database").address
$A$1:$G$3300
?Names("Database").namelocal
Base de datos

The range is correct.

Can I change the namelocal via VBA?

Thanks

Luis Verme

 

[keepITcool]

no you cant "change" namelocal as it's part
of the index of the names collection.
BUT you can remove it and replace it with a new name.

dim s$
s=Names("database").referstor1c1
Names("database").delete
Names.add "database", referstor1c1:=s

the local name is now equal to english name: "database"



--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Luis Verme wrote :

 

[Luis Verme]

Thanks for your reply.
I tried the code you sent me but a 1004 error when it executes
Names("database").Delete

Private Sub Workbook_Open()
Dim s$
s = Names("database").RefersToR1C1
Names("database").Delete
Names.Add "database", RefersToR1C1:=s
End Sub

Any suggestions

Luis Verme

 

[keepITcool]

Luis..
WHY in workbook_open?
you only need to do it once. then save the dbf as an xls.






--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Luis Verme wrote :

 

[Luis Verme]

I have to keep updated the dbf file so that other people use it. I take
information from the dbf to make some reports and update it via Excel with
the current month orders.