This function will convert data to DBF format. In my experience, if there are multiple header lines, a balnk row and then data columns, the top row will be used as column headers and everything else will be data. The FromCell parameter might be used to adjust for this.
I don't show the p_DataPath() function here, but it just gets the path to the server ADB file even if the user is shadowed.
Bill.
Code:
Function p_Excel_to_Dbf as L(excel_file as C, dbf_file as C, FromCell="A1", ToCell="") 'DESCRIPTION:Save Excel as DBF format. Default From/To is all data, e.g. A1 to end. ABD path assumed if no path specified. ' Set defaults/validate ***************************************** p_Excel_to_Dbf = .f. if excel_file="" .or. dbf_file="" ui_msg_box("Excel to DBF", "File name not specified.",UI_NO_SYMBOL) exit function end if if file.filename_parse(excel_file,"d") = "" ' no path (drive) specified excel_file = p_DataPath() +excel_file end if if file.filename_parse(dbf_file,"d") = "" dbf_file = p_DataPath() +dbf_file end if if FromCell = "" FromCell = "A1" end if xlLastCell = 11 ' start Excel ************************************************** Dim xlApp as p xlApp = ole.create("Excel.Application") 'xlApp.Visible = .T. ' just use in testing 'debug(1) xlApp.Workbooks.Open(excel_file) xlApp.displayalerts = .f. if ToCell = "" lastrow = xlApp.Activecell.SpecialCells(xlLastCell).Row lastcol = xlApp.Activecell.SpecialCells(xlLastCell).Column ToCell = chr(lastcol+64)+lastrow end if 'xlApp.Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select() xlApp.Range(FromCell +":" +ToCell).Select() xlApp.Selection.Columns.AutoFit() ' make all text visible. export only gets what is visible. ' ***** save new table ***** xlDBF4 = 11 xlApp.ActiveWorkbook.SaveAs(dbf_file, xlDBF4, .f.) xlApp.ActiveWindow.close(.f.) ' so don't get prompt to save changes clipboard.Clear_Data() ' because large data can result in windows question to user xlApp.Quit() delete xlApp p_Excel_to_Dbf = .t. END FUNCTION
Bill.
Comment