×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

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

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

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

(OP)
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

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

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/appendfrom.... I think it comes with sample code.

Tamar

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

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 Smileing

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.

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

(OP)
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

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

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

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

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

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

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

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

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 loExcel.Visible = .T. 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

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

(OP)
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

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

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

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

(OP)
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

CODE -->

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

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

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close