Reading dbf files
 

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

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 :

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

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" escribió en el mensaje
ft.com...


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 :

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

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 :

Can I change the namelocal via VBA?

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" escribió en el mensaje
ft.com...

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 :

Can I change the namelocal via VBA?

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 :

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" escribió en el mensaje
ft.com...

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 :

Can I change the namelocal via VBA?

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.


"keepITcool" escribió en el mensaje
ft.com...
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 :

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" escribió en el mensaje
ft.com...

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 :

Can I change the namelocal via VBA?