workbooks.open

[geographer]

I am trying to do something that I think should be fairly easy, but I am not
able to accomplish it. I am trying to open two dbase files in one workbook.
The first one opens up just fine and adds a new worksheet for the next dbase
file to go to. But the second dbase file opens up into a new workbook of its
own.

i have about ten dbase files output from ArcView GIS that I wanted to open
into one Excel workbook for analytical purposes.
Here is my code:

Public Sub OpenDBF()
'The next two lines open listings.dbf and add a new sheet. It works fine.
Workbooks.Open Filename:="c:\temp\listings.dbf"
Worksheets.Add

'this next line opens a new workbook and opens housing.dbf.
'I want both dbase files in one workbook. But it opens a new workbook
instead of putting it into one singular workbook with "listings.dbf"
Workbooks.Open Filename:="c:\temp\housing.dbf"

End Sub


Please help
Thanks

 

[Dave Peterson]

Option Explicit
Public Sub OpenDBF()
dim CombWkbk as workbook
dim TempWkbk as workbook
dim DBFNames as variant
dim iCtr as long

dbfnames = array("listings", "housing", "something", "anotherthing")

set combWkbk = workbooks.open("c:\temp\" & dbfnames(lbound(dbfnames)) & ".dbf")

for ictr = lbound(dbfnames)+1 to ubound(dbfnames)
set tempwkbk = workbooks.open("c:\temp\" & dbfnames(ictr) & ".dbf")
tempwkbk.worksheets(1).copy _
after:=combwkbk.worksheets(combwkbk.worksheets.count)
tempwkbk.close savechanges:=false
next ictr

End Sub

(untested, uncompiled!)

 

[geographer]

Hi:

Thanks for getting back to me so fast. I input the code into VBA exactly as
you directed and it worked. I then pulled out one of my old programming books
from school and dug into arrays. I am not a programmer but I would really
like to learn to solve problems on my own. The way you approached the problem
gave me some food for thought as well.
Thanks,

 

[Dave Peterson]

Debra Dalgleish has a list of books at her site:
http://www.contextures.com/xlbooks.html

John Walkenbach's is a nice one to start with.