Plateforme Level Extreme
Abonnement
Profil corporatif
Produits & Services
Support
Légal
English
Excel Automation Delete Column Causing Problems
Message
De
08/06/2015 16:44:49
 
 
À
06/06/2015 07:46:49
Information générale
Forum:
Visual FoxPro
Catégorie:
COM/DCOM et OLE Automation
Versions des environnements
Visual FoxPro:
VFP 9 SP1
OS:
Windows Server 2003
Network:
Windows 2008 Server
Database:
MS SQL Server
Application:
Web
Divers
Thread ID:
01620683
Message ID:
01620747
Vues:
38
If I bracket the call with DOEVENTS FORCE, it works:
DOEVENTS FORCE
m.loWorkSheet.Columns( 3 ).Delete( )
DOEVENTS FORCE
I've put this solution in comments in the code, in case the idea Sergey suggested turns out to not work in the client's test environment.

Thanks for your help!

UPDATE: as it happens, a judiciously placed DOEVENTS FORCE (after the creation of the Excel automation object, but before it's used) was the solution to similar unreliability problems in another very closely related Excel automation method. In the interest of maximum reliability I've added it to this method as well.

The closely related method could be called either with a filename as a passed parameter, or without a filename. If it was called without a filename it would bring up a getfile dialog. After inputting a file name, the method was 100% reliable.

If invoked with a passed filename there was no such interactive dialog and the method was unreliable.

So it looks like the interactive getfile dialog was partially or completely acting as a DOEVENTS FORCE and masking the underlying issue.

As an aside, I also tested with just DOEVENTS (i.e. no FORCE clause) and that does not address the issue. In my case DOEVENTS FORCE is required.

>* Update column headers with previously saved values:
>loWorkSheet = m.loWorkbook.WorkSheets( 1 )
>lnSelect = SELECT( 0 )
>lnIx = 1
>
>SELECT _XLHeaders
>SCAN ALL
> loCell = m.loWorkSheet.Cells( 1, m.lnIx ) && first gut reaction, probably unrelated to error...
> loCell.NumberFormat = "@"
> loCell.Value = ALLTRIM( _XLHeaders.Hdr )
>
> m.lnIx = m.lnIx + 1
>
>>ENDSCAN
>
>DoEvents && Force ?
>Wait Wind "Just fooling around 1" Timeout 0.1 && another way to introduce Doevents / Wait state
>
>loCol = loWorkSheet.Columns( 3 )
>DoEvents && Force ?
>Wait Wind "Just fooling around 2" Timeout 0.1 && another way to introduce Doevents / Wait state
>
>>* BZ5952 - 2015.04.13 - Al Doman
>>* Post-processing 2: remove unneeded Referral PK column:
>m.loColl.Delete( )
>
>DoEvents && Force ?
>Wait Wind "Just fooling around 3" Timeout 0.1 && another way to introduce Doevents / Wait state
>
>m.loWorkbook.Save( )
>
>* Cleanup:
>m.loExcel.Quit( )
>m.loExcel = .NULL.
>RELEASE m.loExcel
>
>
>Or to be on the safe side, just remove the PK column from the table to import ;-)
>
>>I have two very similar Excel automation methods. One is 100% reliable. The other one almost always works the first time it's run, but fails with an OLE error on a subsequent run: OLE error code 0x80004005: Unspecified error. The code of the problem method is:
>>
>>* Open the FOX2X DBF in Excel, save to .XLSX format:
>>m.loExcel.DisplayAlerts = .F.
>>m.loWorkbook = m.loExcel.Workbooks.Open( m.lcDBF2X )
>>m.loWorkbook.SaveAs( m.lcFileName )
>>
>>* Update column headers with previously saved values:
>>m.loWorkSheet = m.loWorkbook.WorkSheets( 1 )
>>m.lnSelect = SELECT( 0 )
>>m.lnIx = 1
>>
>>SELECT _XLHeaders
>>SCAN ALL
>>	m.loWorkSheet.Cells( 1, m.lnIx ).NumberFormat = "@"
>>	m.loWorkSheet.Cells( 1, m.lnIx ).Value = ALLTRIM( _XLHeaders.Hdr )
>>	
>>	m.lnIx = m.lnIx + 1
>>
>>ENDSCAN
>>
>>* BZ5952 - 2015.04.13 - Al Doman
>>* Post-processing 2: remove unneeded Referral PK column:
>>m.loWorkSheet.Columns( 3 ).Delete( )
>>
>>m.loWorkbook.Save( )
>>
>>* Cleanup:
>>m.loExcel.Quit( )
>>m.loExcel = .NULL.
>>RELEASE m.loExcel
>>
If I comment out the .Delete( ) call, this method is also 100% reliable. Also, if I get the OLE error and click on Ignore, the spreadsheet is created normally except that Column3 is not deleted. So, even though the error message doesn't say which command is causing the problem, that's more evidence it's the .Delete( ) call.
>>
>>Anyone have any ideas why that call could be causing problems, and how to mitigate?
Regards. Al

"Violence is the last refuge of the incompetent." -- Isaac Asimov
"Never let your sense of morals prevent you from doing what is right." -- Isaac Asimov

Neither a despot, nor a doormat, be

Every app wants to be a database app when it grows up
Précédent
Répondre
Fil
Voir

Click here to load this message in the networking platform