Link to home
Start Free TrialLog in
Avatar of morinia
moriniaFlag for United States of America

asked on

Saving Excel (.xls) Spreadsheet to dBase IV (.dbf)

I am having a problem saving an Excel Spreadsheet  (.xls) to a dBase IV (.dbf) file within a VB Script.
The script appears to run successfully, but the new data is not saved.

This is the script I am using.
set oExcel = createobject("Excel.Application")
Set wb1 = oExcel.workbooks.open("L:\Information Systems\Forest Hills-Syosset Conversion\Forest Hills Data Manipulations\Workhistory\WORKHIST2.xls")
Set wb2 = oExcel.workbooks.open("L:\Information Systems\Forest Hills-Syosset Conversion\Forest Hills Data Manipulations\Workhistory\WORKHIST2.dbf")
'wb2.Sheets("WORKHIST2").Cells.Clear
wb1.Application.CutCopyMode = False
wb1.Sheets("WORKHIST2").Cells.Copy
wb2.Sheets("WORKHIST2").Range("A1").Select
wb2.Sheets("WORKHIST2").Cells.PasteSpecial -4163
wb1.Save
wb2.Save
wb2.Save
wb2.Save
wb1.Close (True)
wb2.Close (True)
oExcel.quit
Avatar of Saurabh Singh Teotia
Saurabh Singh Teotia
Flag of India image

Well Use this....

set oExcel = createobject("Excel.Application")
Set wb1 = oExcel.workbooks.open("L:\Information Systems\Forest Hills-Syosset Conversion\Forest Hills Data Manipulations\Workhistory\WORKHIST2.xls")
Set wb2 = oExcel.workbooks.open("L:\Information Systems\Forest Hills-Syosset Conversion\Forest Hills Data Manipulations\Workhistory\WORKHIST2.dbf")
'wb2.Sheets("WORKHIST2").Cells.Clear
wb1.Application.CutCopyMode = False
wb1.Sheets("WORKHIST2").Cells.Copy
wb2.Sheets("WORKHIST2").Range("A1").Select
wb2.Sheets("WORKHIST2").Selection.PasteSpecial Paste:=xlPasteValues
wb1.Save
wb2.Save
wb1.Close (True)
wb2.Close (True)
oExcel.quit

This will do the trick for you...

Saurabh...
Avatar of morinia

ASKER

saurabh726

A get a compile error on  Paste:=xlPasteValues
Well one query in your workhist2.xls ..You got some formulas or its all values...coz if its all values...then you can use this command to paste it...

Sorry some changes in the code...try this...

set oExcel = createobject("Excel.Application")
Set wb1 = oExcel.workbooks.open("L:\Information Systems\Forest Hills-Syosset Conversion\Forest Hills Data Manipulations\Workhistory\WORKHIST2.xls")
Set wb2 = oExcel.workbooks.open("L:\Information Systems\Forest Hills-Syosset Conversion\Forest Hills Data Manipulations\Workhistory\WORKHIST2.dbf")
'wb2.Sheets("WORKHIST2").Cells.Clear
wb1.Application.CutCopyMode = False
wb1.Sheets("WORKHIST2").Range("A1").Select
wb1.Sheets("WORKHIST2").Cells.Copy
wb2.Sheets("WORKHIST2").Range("A1").Select
wb2.Sheets("WORKHIST2").Cells.PasteSpecial -4163
wb1.Save
wb2.Save
wb1.Close (True)
wb2.Close (True)
oExcel.quit

Saurabh...

Avatar of morinia

ASKER

Saurabh726:

The compiler did not like this line.
wb1.Sheets("WORKHIST2").Range("A1").Select
Well it will give you an error only once its not able to find that sheet name in your workbook...so do you have that sheet name in your workbook..or incase if you want to select from active sheet only..then use this...

wb1.Activesheet.Range("A1").Select
This works for me
Regards,
Rory
set oExcel = CreateObject("Excel.Application")
strPath = "L:\Information Systems\Forest Hills-Syosset Conversion\Forest Hills Data Manipulations\Workhistory\WORKHIST2"
Set wb1 = oExcel.workbooks.open(strPath & ".xls")
Set wb2 = oExcel.workbooks.open(strPath & ".dbf")
'wb2.Sheets("WORKHIST2").Cells.Clear
wb1.Application.CutCopyMode = False
wb1.Sheets("WORKHIST2").Cells.Copy
wb2.Sheets("WORKHIST2").Range("A1").PasteSpecial -4163
 
wb1.Close True
wb2.Close True
oExcel.cutcopymode = false
oExcel.quit

Open in new window

Avatar of morinia

ASKER

rorya  or saurabh726,

The problem seems to be an incompatibility between saving an Excel file to dBase IV.  
Rory there is one thing which i observed....like if some other cells is highlighted when he give cells select command..so thats why gave him the comand to select a1 before then that..so the whole data gets selected..
Avatar of morinia

ASKER

saurabh726  rorya

When I copy to another Excel spreadsheet all of the data is selected with no problem.  The problem is when trying to copy to a dBase IV (.dbf) file that I have the problem
Saurabh,
You shouldn't need to select in order to copy.

morinia,
What do you mean by that? That code worked for me copying from a workbook to a dbaseIV file. Can you post a sample of the files that don't work for you?

Regards,
Rory
Avatar of morinia

ASKER

rorya,

Here is the Excel spreadsheet.  It appears I cannot attach a DBF file.
Temp-Workhist2.XLS
Can you rename it as an xls and post it - we can then download and rename it as dbf.
Regards,
Rory
Avatar of morinia

ASKER

rorya,

Here is the DBF file renamed.  When I run the VB script it should look like the Excel Spreadsheet.  However, the current contents is not being replaced.
WORKHIST2-dbf.xls
Yes - I've noticed the same thing (I was testing with a much smaller sample, which worked OK). I will see if I can figure out why the data gets truncated when the file is saved.
Avatar of morinia

ASKER

rorya

Do you know how to turn of the prompts in Excel.  ie.  
Attached is the messages I get when I do a manual save which does give me all of the data.
Excel-Messages.doc
ASKER CERTIFIED SOLUTION
Avatar of Rory Archibald
Rory Archibald
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
You might get a bit of text truncation with that so see if this is better:

set oExcel = CreateObject("Excel.Application")
'oexcel.visible = true
'strPath = "C:\Test\WORKHIST2"
strPath = "L:\Information Systems\Forest Hills-Syosset Conversion\Forest Hills Data Manipulations\Workhistory\WORKHIST2"
Set wb1 = oExcel.workbooks.open(strPath & ".xls")
oExcel.CutCopyMode = False
With wb1.Sheets("WORKHIST2").UsedRange
    .EntireColumn.Autofit
    .Entirerow.autofit
    for n = 1 to .Columns.Count
        .Columns(n).ColumnWidth = .columns(n).Columnwidth + 5
    next
    .Select
End with

oExcel.DisplayAlerts = False
wb1.saveas strpath & ".dbf", 11 ' 11 is xlDBF4
oExcel.Activeworkbook.Close False
oExcel.DisplayAlerts = True

oExcel.quit


Regards,
Rory
Avatar of morinia

ASKER

rorya,

I noticed that I lose my formatted dates.  Is there a way I can put the formatting back in.  I would like the date format to be 'm/dd/yyy'?

morinia