excel automation column problem 2

[borsker]

I am trying to use an existing excel sheet that comes in the same way every time. I usually import the data into foxpro but i believe the currency column is turning the data into a date even though it is imported as a character. To make a long story short, here is the code i am trying to use, however i am very new to this and i am sure i am doing something wrong. I am getting this error on the last line of the code and i am not sure why. "xlSheet is not an object"

tmpsheet = getOBJECT('c:\ftp_over\matrix.xls')
oExcel = tmpsheet.APPLICATION
oExcel.ReferenceStyle = 1 && Ensure Columns in A-B Format instead of 1-2 Format

mcStrtColRow = 'C1'
mcEndColRow = 'C30'

xlSheet = oExcel.activesheet


xlSheet.RANGE[mcStrtColRow,mcEndColRow].EntireColumn.NumberFormat = "@"


thanks for any help

 

[Mike Lewis]

Borsker,

Perhaps it was a typing mistake, but you need to put a dot after the word RANGE in the last line.

Mike


Mike Lewis
Edinburgh, Scotland

My Visual Foxpro web site:

http://www.ml-consult.demon.co.uk

My Crystal Reports web site:

http://www.ml-crystal.com

 

[Mike Gagnon]

Try a more "current" method of automating Excel.

Local oExcel,tmpsheet,xlSheet
oExcel = CREATEOBJECT("excel.application")
tmpsheet = oExcel.Workbooks.open('c:\ftp_over\matrix.xls')
xlSheet = oExcel.activesheet
xlSheet.Columns("C:C").NumberFormat = "@"
oExcel.visible = .t.

Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[borsker]

Both answers worked great, however during the same session if i try to import the excel sheet i get a fatal error and everything shuts down here is the new code provided by Mr. Gagnon.

Local oExcel,tmpsheet,xlSheet
oExcel = CREATEOBJECT("excel.application")
tmpsheet = oExcel.Workbooks.open('c:\ftp_over\matrix.xls')
xlSheet = oExcel.activesheet
xlSheet.Columns("C:C").NumberFormat = "@"
*oExcel.visible = .t.

xlSheet.SAVEAS("c:\ftp_over\thisstuff.xls")

* --- Close the Worksheet ---
oExcel.workbooks.CLOSE

* --- Quit Excel ---
oExcel.QUIT
RELEASE oExcel

RELEASE ALL
CLOSE ALL

IMPORT FROM THISSTUFF.XLS XLS

 

[Mike Gagnon]

Have you tried :
IMPORT FROM THISSTUFF.XLS type XLS

Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[borsker]

still getting the fatal error. could it be that i just installed the service pack for vfp 6. and it was a bad install?

 

[JRB-Bldr]

Assuming that you have followed the code execution step-by-step using the VFP Trace window, at what point (code line) do you get the Fatal Error?

Good Luck,
JRB-Bldr

 

[borsker]

on the import line

 

[borsker]

OK when i add a clear all line in there and change it to "IMPORT FROM THISSTUFF.XLS XL8" it works.

However now I am having the problem that originally started this procedure. I wanted to change the column to text hoping that it would take care of a date problem. The problem is onece the job is imported. a few cells will have a date rather than a number. but it is very random and in many cases, it does not happen every time (but i am using the same excel sheet and the same code.). does anyone know why this could be happening?

 

[JRB-Bldr]

One question, since you are already Opening the file with your Excel Automation, why are you closing the file and then performing an IMPORT?

Why not just do a SAVEAS... to a DBF4 format file within your Excel Automation?

That works well as long as your Excel Columns are formatted in a manner consistent with how you want your DBF fields.

Good Luck,
JRB-Bldr

 

[baltman]

Excel import is very tricky. I save all data as values with format general before import, and it would be even better to copy-paste-special-values into a fresh workbook first.

Dates come in as number values, which I then change back to dates by adding the number to {12/30/1899} (I think).

I do also have several hundered other lines of code to deal with various other import issues too..

Building a DBF table from from the Excel file cell by cell via automation will be MUCH slower, but have less errors.

Brian

 

[borsker]

jrb-bldr's answer looks good how do you write that. when i use this code "xlSheet.saveas("s:\mikebrack\thisstuff.dbf")"
the file will not open in vfp and any other sheet that is in there will get saved also. I am getting the "not a table error".

Brian, what is the correct code for changing the cells to general. also i can not figure out the code for alignment. I was thinking that if i can import everything left justified, maybe vfp will automatically see it as character and not try to make any adjustments.

thank you to everyone so far i am getting close and closer

 

[baltman]

To do a saveas of a different filetype, you need to pass the constant too. You'll still have data type conversions unless the data is formatted EXACTLY as you want it imported.

Here's some snips:

loExcel = CreateObject("Excel.Application")
WITH loExcel
     .visible=.f.
     .Workbooks.Open(filefullpath)
     .DisplayAlerts = .f.
     .Rows("1:65536").select
     .Selection.NumberFormat ="General"
     .ActiveSheet.saveas("&varuserlocation\XL_Values.xls")
     .quit
ENDWITH
loExcel=.null.

SET DEFAULT TO ("&varuserlocation")
ERASE temp_import.dbf
IMPORT FROM ("&varuserlocation\XL_Values.xls") TYPE XL8
use                                                                       
RENAME "XL_Values.DBF" to temp_import.dbf
SELECT * from temp_import INTO TABLE hold_temp_import
ENDIF

SET DEFAULT TO ("&varuserlocation")

**more stuff**

CALCULATE MIN(VAL(&varfield)) TO min_val FOR VAL(&varfield)>0 AND RECNO()>1
CALCULATE Max(VAL(&varfield)) TO max_val for RECNO()>1
	IF year({12/30/1899}+min_val)>=1950 AND (max_val)<70000 &&year 2094, then a date
		VarFieldType="D"
		GO top
		varnamelength=LEN(ALLTRIM(&varfield))
		ALTER TABLE DBF() alter COLUMN &varfield c(MAX(12,varnamelength)) &&make wide enough
		REPLACE ALL &varfield WITH DTOC({12/30/1899}+VAL(&varfield)) FOR VAL(&varfield)>0 AND RECNO()>1
	ENDIF

**more stuff**

 

[JRB-Bldr]

Try something like the following....

mcInFile = "C:\Temp\Input.xls" && Or whatever input File
mcInName = JUSTFNAME(mcInFile)
mcOutDBF = "C:\Temp\Target.dbf" && Or whatever output File

tmpsheet = CREATEOBJECT('excel.application')
oExcel = tmpsheet.APPLICATION
oExcel.Workbooks.CLOSE

oExcel.workbooks.OPEN(mcInFile)
oExcel.WINDOWS(mcInName).ACTIVATE

xlBook = oExcel.ActiveWorkbook.FULLNAME
oExcel.ReferenceStyle = 1 && Ensure Columns in A-B Format
xlSheet = oExcel.ActiveSheet

< do whatever via Excel Automation >

xlSheet.SAVEAS (mcOutDBF, 11, 0)

* --- Close the Worksheet ---
oExcel.workbooks.CLOSE

* --- Quit Excel ---
oExcel.QUIT
RELEASE oExcel


Good Luck,
JRB-Bldr

 

[JRB-Bldr]

Oops...

One this I neglected to show prior to the SaveAs...

You will need to select ONLY those Columns (Fields) and Rows (Records) which you want to include in your DBF file.

Additionally ensure that you have Row #1 cells contain the DBF field names (limited to 10 char).

Good Luck,
JRB-Bldr

 

[borsker]

that worked but two of my columns are coming over numeric and dropping any characters that are listed below them. is there a way i can import it in as all character?

 

[JRB-Bldr]

Format the columns as Text Cells (instead of General Cells) in Excel prior to the SaveAs...

Something like...
* --- Standard Text Field Cell Format ---
xlSheet.RANGE[mcFmtRange,mcFmtRange].EntireColumn.NumberFormat = "@"


Good Luck,
JRB-Bldr

 

[borsker]

I know i am starting to sound like a pain in the rear end, i am sorry. i used this code before the saveas code and it still did not work.

"xlSheet.range("A:I").NumberFormat ="@""

Is there a way i can insert a row and add charecters to the second line so that foxpro will think it is all character?

 

[JRB-Bldr]

You say "can insert a row...", but inserting a row will merely add a 'Record' to your DBF file, it will not add a new Field. The new record's fields will display the same problem.

Help me to better understand what you are getting and what you want. One or more examples might help.

Good Luck,
JRB-Bldr

 

[borsker]

Thank you for all of your help. I think i got it with this
code

xlsheet.rows("2:2").insert
xlsheet.range("A2:I2")="xxxx"

once it is in vfp i delete the first record in the table and i get what i am looking for. So i think i got it.

However, is there a way i can close everything without it asking me if i want to save. and then open the dbf file in vfp without asking me to choose a platform.