Automating Making a DBF File

[Bob Barnes]

Got great code from Joel last week, but now looking at...

Column L in the DBF is what makes the Row (Record) Unique.

Column L in the Daily excel file is a "mixture" of Entries w/ 9 or 10
characters. I know when making a DBF Manually from Excel, that Excel looks
at the first few rows to "guess" what DataType it is, and number of
characters.

Working w/ this particular file, Excel has to be "guessing" it's 9
characters instead of the actual (example) 10 characters below.

MSR0332476
MSR0332489
MSR0332488
MSR0332486

So...the DBF results in...
MSR033247
MSR033248 --> Can't be a Primary Key
MSR033248 --> Can't be a Primary Key
MSR033248 --> Can't be a Primary Key

Is there a way to use VBA to set the number of characters
in the DBF's Column L to 10-characters?

TIA - Bob

 

[Joel]

for people who haven't read your previous posting all we are doing is a
"SAVEAS" an excel file into DB4 format. The Excel worksheet has 10
characters and when the Dbase4 reads the data is is only finding 9 characters.

The problem is either excel is dropping the last character or Dbase4 isn't
reading the last character. Excel shoud be treating the key as a string and
I don't believe excel is trucating a trailing character, but you never know
with excel.

I would think the problem is that the dbase4 database really only has a 9
digit key. You may just have to remove the last character in the excel
worksheet before saving. I suspect the last character isn't an ID but a
sequence number that just need to be removed. Open the dbase4 database and
make sure it wants only nine characters and not ten characters.

 

[Bob Barnes]

Joel - thank you for answering.

The Excel file provided comes from SQL Server in this format (BELOW), where
it has 10-characters. The problem is the automated code is trunacting to 9
characters (because some entries DO have only 9-characters). Is there any
way, in the VBA, to tell Excel that the 12th Column (Column L) has
10-characters? TIA - Bob

Provide Excel file for Column L...which I make the Primary Key in the Table
receiving the Import.
MSR0332476
MSR0332489
MSR0332488
MSR0332486

 

[Joel]

Excel is very bad at giving you options for reading and saving files in
different formats. Excel may only be looking at the first row to determine
the field size. Make sure first row contains 10 characters and this may
solve the problem.

Sometimes you are better of outputing in a diffferent format such as CSV and
then import CSV into dbase4.

 

[Bob Barnes]

Tried (but didn't worK)..see below please...where "Tried this...fails"

With bk.Sheets(2)
LastRow = .Range("B" & Rows.Count).End(xlUp).Row
.Columns("B:B").AutoFilter
.Columns("B:B").AutoFilter Field:=1, Criteria1:="Plastics"

.Columns("L:L").MaxLength = 10 <---------- Tried this...fails
.Columns("L:L").Length = 10 <---------- Tried this...fails
.Columns("L:L").Width = 10 <---------- Tried this...fails

'copy only filtered rows
.Rows("1:" & LastRow).SpecialCells(xlCellTypeVisible).Copy _
bk2.Sheets(1).Rows(1)
End With

 

[Bob Barnes]

Joel - thank you.

I'm going to play with this some more. I'll Post back w/ another Topic w/
your name in its Title...hope to have taht done in the next day or two.

"Make sure first row contains 10 characters and this may solve the problem."
---> I thought that might work, and tried that, but it didn't work..at least
yesterday.