Hi Terence,

Here's one way to solve the problem:

Worksheets("Sheet1").Copy
ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\TEST.DBF", _
FileFormat:=xlDBF3, CreateBackup:=False

This copies Sheet1 into a new workbook and then saves the new workbook to a
..dbf format.

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *


"Terence" wrote in message
...
Hi,

There are 3 worksheets (Sheet1,Sheet2,Sheet3) in my excel
file and this file contains an Excel macro program. I am
trying to write some codes to save the contents of Sheet1
to a DBF format file. (This DBF file will be imported to
another system for other purposes).

Here comes the problem, if I use the code:
'*******
Sheets("Sheet1").Select
Range("A1").Select
ActiveWorkbook.SaveCopyAs (ThisWorkbook.Path & "\TEST.DBF")
'*******
Then the file "TEST.DBF" contains all 3 sheets. But I need
the data on Sheet1 only. This method also save all the
macro code to the file "TEST.DBF".

If I use the code:
'********
Sheets("Sheet1").Select
Range("A1").Select
ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path
& "\TEST.DBF", _
FileFormat:=xlDBF3, CreateBackup:=False
'********
This method just save the data of Sheet1 to TEST.DBF but
it also changes the name my original Excel to "TEST.DBF".
I am afraid that the users will save the file carelessly
when they close the file.

Therefore, is there anyone knows any better way to do the
task? Thanks in advance!

Terence