how can import an excel 2016 sheet with a vba project into a new table or existing table 4

[Ernesto_Paez]

Hi Guys,
here is my situation:
1-i had created a 2016 excel file with the same columns as fields i have in a particular table, by the way i used Export in vfp 9.0, to create the excel file.
2- i only have one excel sheet, one of the columns is to input date, so what i did was adding the microsoft datepicker, so on each row under column "day" the cells always will allow to pick a date from the
datepicker.
3- now i need to import the content of the whole sheet into a new vpf 9.0 table or into the an existing one either way.
4- what happened is that the excel file has been saved as xlms because it contain the vba code i added for having the datepicker on each cell under the column "DAY"

It is possible to import this excel file into a new table or existing one(vfp 9.0 spk2) ?
i am very novice, you know that thanks in advance

 

[TamarGranor]

You have a number of choices:

1) Use Excel to save the file in an earlier format that VFP's APPEND FROM and IMPORT commands can use. If you go this way, your best bet is to save as CSV.

2) Open Excel via automation and open the file that way to grab the data.

3) Use this project:

https://github.com/ggreen86/XLXS-Workbook-Class.

There's documentation in the zip file.

4) Use this project (or one of its siblings):

http://praisachion.blogspot.com/2018/03/appendfromxlsx-31.html.

I think it comes with sample code.

Tamar

 

[GriffMG]

Tamar is correct, in every detail.

I would think the CSV is the safest approach as well, there is no way anything malicious can get in that way.

You MIGHT need to be careful of dates which are ambiguous internationally speaking (03/08/2019 might be 3rd August or 8th March)
but I *think* that is mostly an Excel problem reading your export rather than VFP reading a CSV

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​

 

[Ernesto_Paez]

Hi everyone,
using Ms office 2016 32 bit on windows 7 32 bit
i created this little code to see if can add data to a table from the excel file but i am getting the error as shown, can anyone please help on what is wrong ?

here is the error see attach please.
here is the code i have, it always fail in the line ".ActiveWorkbook.SaveAs(m.lcTemp, 39, "", "", .F., .F.)"
i am running a prg file with the below code

Local lcFile, lcTemp, loExcel As Excel.Application
lcFile = Getfile("xls,xlsx")
If !Empty(m.lcFile)   && check if not cancelled
	lcTemp = Addbs(Getenv("TEMP"))+Sys(3)+".xls"
	loExcel = Createobject("excel.application")	
	With loExcel
		.DisplayAlerts=.F.  && somewhat similar to SET SAFETY OFF of VFP
		.Workbooks.Open(m.lcFile)		
		.ActiveWorkbook.SaveAs(m.lcTemp, 39, "", "", .F., .F.)			
		 && Place it in the temp folder
		.ActiveWindow.Close(.T.)
		.Quit
	Endwith
	*Select yourtable
	USE Nengtime_arefin
	Append From (m.lcTemp) Type Xls
    * Clean it up, delete temp xl5 file used
	Delete File (lcTemp)
Else
	Messagebox("Aborted by user!",0+64,"Oppppssss!")
Endif

Thanks a lot

 

[Olaf Doschke]

please add pictures as pßictures, there is that icon here for that:

Your version of Excel seems to be insufficient, the error says what it says, it doesn't see SaveAs available.
You might easily have a timing problem, Workbook isn't yet loaded. Don't assume OLE code runs synchronous. When you are past loExcel.Open() that doesn't mean Excel has opened the file already and created the workbook, Excel is a parallel process and the open method can and does return before it finishes opening a file.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[SkipVought]

What if you tried True vs .T. and False vs .F.?

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[Mike Lewis]

If, as Olaf suggested, there is a timing problem, you can easily check for that by executing the code line by line in the command window. Better still, execute [tt]loExcel.Visible = .T.[/tt] at a suitable point so that you can see exactly what it happening.

I'm not sure I agree with SkipVought. You should be able to use VFP syntax when passing parameters to OLE objects.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Ernesto_Paez]

Hi Mike,
i am doing this from the command window, i can see the excel file, that is opened and shown after executing
loExcel.visible=.T.

but then after that the next line is

loExcel.ActiveWorkbook.SaveAs(m.lcTemp, 39, "", "", .F., .F.) and after executing that line of code
i get this error and i don't know how to resolve or troubleshoot this issue.

so i am running MS OFFICE 2016, the excel file is and XLS and running it from a win 7 pro 32 bit machine
if anyone can help thanks so much

 

[Mike Lewis]

Have you tried saving the file in another format? In other words, instead of putting 39 as the second parameter, try some other settings. I know that 39 is the value you need for an XL5 file, but by trying other values it will at least help narrow down the problem.

Also, what happens if you try to execute the Save As interactively, from within Excel (to the same format)?

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Ernesto_Paez]

Hi Mike, you have a point as i already tried actually saving it as Microsoft excel 5.0/95 workbook, then i get a pop window saying this

so i know i have a vba project in there because i added a datepicker to one of the columns but actually i want to save it as 5.0/95. so i can i use then USE

Append From (m.lcTemp) Type Xls
so my new code is

Local lcFile, lcTemp, loExcel As Excel.Application
#Define xlExcel8  56
#Define xlExcel5  39
lcFile = Getfile("xls,xlsx")
If !Empty(m.lcFile)   && check if not cancelled
	lcTemp = Addbs(Getenv("TEMP"))+Sys(3)+".xls"
	loExcel = Createobject("excel.application")
	lnVersion = Int(Val(loExcel.Version))  && this value =16 so greater than 12
	With loExcel
		.DisplayAlerts=.F.  && somewhat similar to SET SAFETY OFF of VFP
		.Workbooks.Open(m.lcFile)
*.ActiveWorkbook.SaveAs(m.lcTemp,39)
*.ActiveWorkbook.SaveAs(m.lcTemp, 39, "", "", .F., .F.)
		.Visible=.T.
		.ActiveWorkbook.SaveAs(m.lcFile,Iif(m.lnVersion >= 12,xlExcel8,xlExcel5))
        .ActiveWorkbook.Close
		.Quit
  *Place it in the temp folder
		.ActiveWindow.Close(.T.)
		.Quit
	Endwith
*Select yourtable
	Use Nengtime_arefin
	Append From (m.lcTemp) Type Xls
* Clean it up, delete temp xl5 file used
	Delete File (lcTemp)
Else
	Messagebox("Aborted by user!",0+64,"Oppppssss!")
Endif

but even when i was able to save it as 5.0/95, with the code i have still getting error, now this new error

Thanks

 

[Olaf Doschke]

Your office simply doesn't know the younger excel types. Your Office version is too new.

So the overall plan to use Excel as converter simply fails. Excel 2016 with no add ons seems only to be able to read xl5, but not resave as it.

So the best option will be to save as CSV instead and append from type csv. Unfortunately VFP is not fully compatible with what Excel creates as CSV, the problem will mainly occur, if you have longer text cell contents.

Anyway, the alternative solutions recommended will surely help, as they can handle the xlsx format Excel prefers to save.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name