Import Dbase data Using VBA
 

Hi,

Im looking for some vba code that I could apply to a button, which when
clicked, will then import specific tables from a dbase file, starting at a
specific cell on Excel spreadsheet.

Appreciate any advice you can give €“ Thanks

Alan.

Import Dbase data Using VBA
 

Alan

I use VFP rather than dBase but solution might be similar
I do not think you can access the dbf directly with VB what you can do
though is
(A) Export the data to a text file using dBase + import from that using
windows scripting
(B) Create a server object in dBase (an exe file without a user interface
designed to be called as an object by VBA ... not trivial but works well)

I think the (A) option would work out easier

I doubt you can do this with a simple line or 2 of code
Can dig out some sample code for (A) if you want ... let me know


Hope this helps

Nick

"Alan" wrote:

Hi,

Im looking for some vba code that I could apply to a button, which when
clicked, will then import specific tables from a dbase file, starting at a
specific cell on Excel spreadsheet.

Appreciate any advice you can give €“ Thanks

Alan.

Import Dbase data Using VBA
 

Sorry

There's another option
Excel can read DBF's directly as long as there are less records than rows in
Excel
So you could open the file and read in the data then transfer it toi where
you want it

This is just iffy code not totally correct syntax ... just to give you
starting points

OriginalWB = ActiveWorkBook.Name
OriginalSht = ActiveSheet.Name

RowOffset = 10 ' Set to something that works for the location of your data
ColOffset = 10 ' Set to something that works for the location of your data
ListofCols = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"

Application.WorkBooks.Open("C:\YourDataFolder\Your DBF.DBF")
FOR EACH MyCell in Activesheet.Cells
Row2Write = Trim(CStr(MyCell.Row+RowOffset))
Col2Write = Trim(CStr(MyCell.Col+ColOffset))
Cell2Write = MID(ListofCols,Col2Write,1)+Trim(STR(Row2Write))

IF MyCell.Row1 then ' The field headers are in row 1
IF MyCell.Column = 1 then
WorkBooks(OriginalWB).Sheets(OriginalSht).Range(Ce ll2Write).Formula
= MyCell.Value
ENDIF
IF MyCell.Column = 2 then
WorkBooks(OriginalWB).Sheets(OriginalSht).Range(Ce ll2Write).Formula
= MyCell.Value
ENDIF
ENDIF
NEXT MyCell


Well that sort of approach !

Good luck

Nick

"Alan" wrote:

Hi,

Im looking for some vba code that I could apply to a button, which when
clicked, will then import specific tables from a dbase file, starting at a
specific cell on Excel spreadsheet.

Appreciate any advice you can give €“ Thanks

Alan.

Import Dbase data Using VBA
 

Hi,

It got to this line "Col2Write = Trim(CStr(MyCell.Col + ColOffset))" and
stalled with error "object doesnt support this method or property"

I'm relatively new to vba

Could you advise further ?

Alan

"Nicholas B" wrote:

Sorry

There's another option
Excel can read DBF's directly as long as there are less records than rows in
Excel
So you could open the file and read in the data then transfer it toi where
you want it

This is just iffy code not totally correct syntax ... just to give you
starting points

OriginalWB = ActiveWorkBook.Name
OriginalSht = ActiveSheet.Name

RowOffset = 10 ' Set to something that works for the location of your data
ColOffset = 10 ' Set to something that works for the location of your data
ListofCols = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"

Application.WorkBooks.Open("C:\YourDataFolder\Your DBF.DBF")
FOR EACH MyCell in Activesheet.Cells
Row2Write = Trim(CStr(MyCell.Row+RowOffset))
Col2Write = Trim(CStr(MyCell.Col+ColOffset))
Cell2Write = MID(ListofCols,Col2Write,1)+Trim(STR(Row2Write))

IF MyCell.Row1 then ' The field headers are in row 1
IF MyCell.Column = 1 then
WorkBooks(OriginalWB).Sheets(OriginalSht).Range(Ce ll2Write).Formula
= MyCell.Value
ENDIF
IF MyCell.Column = 2 then
WorkBooks(OriginalWB).Sheets(OriginalSht).Range(Ce ll2Write).Formula
= MyCell.Value
ENDIF
ENDIF
NEXT MyCell


Well that sort of approach !

Good luck

Nick

"Alan" wrote:

Hi,

Im looking for some vba code that I could apply to a button, which when
clicked, will then import specific tables from a dbase file, starting at a
specific cell on Excel spreadsheet.

Appreciate any advice you can give €“ Thanks

Alan.