search

ggreen86 / XLSX-Workbook-Class

VFP Class to Create an XLSX Workbook without Excel Automation or Installed
45 stars 16 forks source link

WorkbookSaveAs - error when attempt to open the new workbook #109

Closed rawilliamsjr closed 5 months ago

rawilliamsjr commented 7 months ago

When running the following code

Do system.app lcAppendToFileName = "Availability_Template.xlsx" lnWb = goExcel.OpenXLSXWorkBook(lcAppendToFileName) goExcel.SaveWorkbookAs(lnWb, "Availability.xlsx")

Attempted to open the new workbook in Excel and received the following message.

image

Click Yes

image

rawilliamsjr commented 7 months ago

Do you have an ETA for this correction? We are in the middle of a large project and this issue is holding us up from completing the project.

DougHennig commented 7 months ago

@rawilliamsjr Just a reminder that this isn't a commercial product but volunteer work.

ggreen86 commented 7 months ago

Unfortunately no I do not have an ETA. I have not had the time to work on this issue in the past few weeks due to my regular job requirements.

From: Rob Williams @.> Sent: Monday, February 5, 2024 6:57 PM To: ggreen86/XLSX-Workbook-Class @.> Cc: Subscribed @.***> Subject: Re: [ggreen86/XLSX-Workbook-Class] WorkbookSaveAs - error when attempt to open the new workbook (Issue #109)

Do you have an ETA for this correction? We are in the middle of a large project and this issue is holding us up from completing the project.

— Reply to this email directly, view it on GitHubhttps://github.com/ggreen86/XLSX-Workbook-Class/issues/109#issuecomment-1928528131, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AGWB33OO6U2VONRZEA2HGO3YSFWT5AVCNFSM6AAAAABCQC4WQCVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMYTSMRYGUZDQMJTGE. You are receiving this because you are subscribed to this thread.Message ID: @.**@.>>

rawilliamsjr commented 7 months ago

I apologize for the brevity of my previous message. I did not intend to cause any offense. I will ensure that our ongoing project runs smoothly until you address the reported issues. Additionally, I wanted to express my gratitude for the class you provided, as it has enabled us to implement a variety of new features in our applications. Thank you.

ggreen86 commented 7 months ago

Rob-

No problem nor any offense taken. I have had some time to work on this issue. I have solved the insert row and correctly adjusting the merged cells. However, I am having problems in the images that are part of the workbook and still debugging this problem which causes Excel to reject the graphic definitions.

Greg

From: Rob Williams @.> Sent: Tuesday, February 6, 2024 4:41 PM To: ggreen86/XLSX-Workbook-Class @.> Cc: ggreen86 @.>; Comment @.> Subject: Re: [ggreen86/XLSX-Workbook-Class] WorkbookSaveAs - error when attempt to open the new workbook (Issue #109)

I apologize for the brevity of my previous message. I did not intend to cause any offense. I will ensure that our ongoing project runs smoothly until you address the reported issues. Additionally, I wanted to express my gratitude for the class you provided, as it has enabled us to implement a variety of new features in our applications. Thank you.

— Reply to this email directly, view it on GitHubhttps://github.com/ggreen86/XLSX-Workbook-Class/issues/109#issuecomment-1930795969, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AGWB33IGWULFYPOGOKYXXV3YSKPM7AVCNFSM6AAAAABCQC4WQCVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMYTSMZQG44TKOJWHE. You are receiving this because you commented.Message ID: @.**@.>>

rawilliamsjr commented 7 months ago

Greg,

Thank you for the update.

ggreen86 commented 6 months ago

Hello--

I now have a solution for the graphic problem. Please try the attached version (preliminary R41) of the class library and let me know it is working for you. If you can try on multiple spreadsheets that will be great. BTW -- you have two Facebook images in the spreadsheet that you gave me (one directly on top of the other).

I will be uploading R41 as beta when I can update the documentation.

Greg

WorkbookXLSX R41 Beta 1.zip

ggreen86 commented 6 months ago

Hello--

I still have issues with the InsertRow for handling merged rows/columns (probably same with DeleteRow, InsertColumn, etc.). I will continue with debugging the problem as I have the time.

Greg

ggreen86 commented 6 months ago

Hello--

I believe I have now corrected the issues with the InsertRow, InsertColumn, DeleteRow, and DeleteColumn methods when there are merged cells that are affected by the change (similar to how Excel would handle the change). I am attaching Release 41 Beta 2 for you to test with. Please let me know if you have any issues.

I am still working on the InsertCell and DeleteCell methods.

Greg

WorkbookXLSX R41 Beta 2.zip

rawilliamsjr commented 6 months ago

After using WorkbookXLSX beta 2, the images no longer present an error when opening the newly created workbook, and the InsertRow method works as expected in my case.

Thank you for your time, effort, and communication.

ggreen86 commented 6 months ago

Rob—

Thank you for the feedback. I am glad this is now working for your project. I am still working on the InsertCell and DeleteCell methods and will release these changes to the general community when I have these completed and tested by me as a beta release.

Greg

rawilliamsjr commented 6 months ago

I found an issue with the Beta 2.

Method GetLastRowNumber returned an error that CellRow was not found.

ggreen86 commented 6 months ago

Corrected the problem. This was caused by renaming of the cursor columns from cellrow and cellcol to row and col. I missed the occurrence on a search.

Attached is R41 B3.

WorkbookXLSX R41 Beta 3.zip

rawilliamsjr commented 6 months ago

While testing the Beta saving the Workbook with no changes is setting all cells as Locked. I have included my basic test code the Excel_Availability_Template.xlsx saving as Excel_Availability.xlsx with no modifications.

PUBLIC goXLSX

DO C:\GrowPoint\SYSTEM.APP

goXLSX = NEWOBJECT('vfpxworkbookxlsx', 'C:\Users\RobW\Downloads\WorkbookXSLX_Beta\vfpxworkbookxlsx.vcx')

lnWb = goXLSX.OpenXLSXWorkBook("C:\GrowPoint\Excel_Availability_Template.xlsx")

goXLSX.SaveWorkbookAs(lnWb, "C:\Users\RobW\Downloads\Excel_Availability.xlsx")

Excel_Availability_Template.xlsx

Excel_Availability.xlsx

ggreen86 commented 6 months ago

Rob—

I checked the file “Excel_Availability_Template.xlsx” random cell properties and the cells that I checked were set to Locked=True. I checked cells in the prefilled text areas and in cells that have no text entered. All checked had the Locked=True set. When checking the file after being opened and then re-saved to a new file name, the cells still had the setting Locked=True. See below screen-shot.

image

Can you provide the specific cells that do not have this setting set? Right now I cannot duplicate or find any problem in the protection setting.

Greg

rawilliamsjr commented 6 months ago

Greg,

Sorry for wasting your time. I thought I had specified the cells. The cells not locked are the Qty., Retail, and SKU column header labels.

Robert Williams

Phone 971.217.0850

www.advancedgrowersolutions.comhttp://www.advancedgrower.com/

@.**@.> [cid:b8f6251a-221f-45f7-a858-bde7054bc176]

From: ggreen86 @.> Sent: Thursday, February 15, 2024 6:15 AM To: ggreen86/XLSX-Workbook-Class @.> Cc: Robert Williams @.>; Mention @.> Subject: Re: [ggreen86/XLSX-Workbook-Class] WorkbookSaveAs - error when attempt to open the new workbook (Issue #109)

Rob—

I checked the file “Excel_Availability_Template.xlsx” random cell properties and the cells that I checked were set to Locked=True. I checked cells in the prefilled text areas and in cells that have no text entered. All checked had the Locked=True set. When checking the file after being opened and then re-saved to a new file name, the cells still had the setting Locked=True. See below screen-shot.

@.***

Can you provide the specific cells that do not have this setting set? Right now I cannot duplicate or find any problem in the protection setting.

Greg

From: Rob Williams @.> Sent: Wednesday, February 14, 2024 7:28 PM To: ggreen86/XLSX-Workbook-Class @.> Cc: ggreen86 @.>; Comment @.> Subject: Re: [ggreen86/XLSX-Workbook-Class] WorkbookSaveAs - error when attempt to open the new workbook (Issue #109)

While testing the Beta saving the Workbook with no changes is setting all cells as Locked. I have included my basic test code the Excel_Availability_Template.xlsx saving as Excel_Availability.xlsx with no modifications.

PUBLIC goXLSX

DO C:\GrowPoint\SYSTEM.APP

goXLSX = NEWOBJECT('vfpxworkbookxlsx', 'C:\Users\RobW\Downloads\WorkbookXSLX_Beta\vfpxworkbookxlsx.vcx')

lnWb = goXLSX.OpenXLSXWorkBook("C:\GrowPoint\Excel_Availability_Template.xlsx")

goXLSX.SaveWorkbookAs(lnWb, "C:\Users\RobW\Downloads\Excel_Availability.xlsx")

Excel_Availability_Template.xlsxhttps://github.com/ggreen86/XLSX-Workbook-Class/files/14289845/Excel_Availability_Template.xlsx

Excel_Availability.xlsxhttps://github.com/ggreen86/XLSX-Workbook-Class/files/14289847/Excel_Availability.xlsx

— Reply to this email directly, view it on GitHubhttps://github.com/ggreen86/XLSX-Workbook-Class/issues/109#issuecomment-1945159578, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AGWB33NJSZ6OQ6T3IPWLX63YTVJCPAVCNFSM6AAAAABCQC4WQCVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMYTSNBVGE2TSNJXHA. You are receiving this because you commented.Message ID: @.**@.>>

— Reply to this email directly, view it on GitHubhttps://github.com/ggreen86/XLSX-Workbook-Class/issues/109#issuecomment-1946182645, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AOXHACB67XVPMRFI2IG3RRDYTYKAJAVCNFSM6AAAAABCQC4WQCVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMYTSNBWGE4DENRUGU. You are receiving this because you were mentioned.Message ID: @.***>

ggreen86 commented 6 months ago

Rob--

I believe I now have the class reading the sheet protection settings correctly and then writing back to the new workbook. Please try the attached R41 Beta 4 and let me know how it works.

I still have to work on the retaining/setting of the password.

Thank you.

Greg

WorkbookXLSX R41 Beta 4.zip

rawilliamsjr commented 6 months ago

All other issues are now working. I know you are still working on the password and the sheet protection. Just wanted to pass this along. I received an error message when I opened a workbook that had sheet protection enabled after saving it under a different name.

[cid:3f46b49f-c79e-42a4-9624-abdf03b3b61e]

Robert Williams

Phone 971.217.0850

www.advancedgrowersolutions.comhttp://www.advancedgrower.com/

@.**@.> [cid:07665086-c69b-456b-abd1-4f934f9ca2f7]

From: ggreen86 @.> Sent: Saturday, February 17, 2024 8:03 AM To: ggreen86/XLSX-Workbook-Class @.> Cc: Robert Williams @.>; Mention @.> Subject: Re: [ggreen86/XLSX-Workbook-Class] WorkbookSaveAs - error when attempt to open the new workbook (Issue #109)

Rob--

I believe I now have the class reading the sheet protection settings correctly and then writing back to the new workbook. Please try the attached R41 Beta 4 and let me know how it works.

I still have to work on the retaining/setting of the password.

Thank you.

Greg

WorkbookXLSX R41 Beta 4.ziphttps://github.com/ggreen86/XLSX-Workbook-Class/files/14319399/WorkbookXLSX.R41.Beta.4.zip

— Reply to this email directly, view it on GitHubhttps://github.com/ggreen86/XLSX-Workbook-Class/issues/109#issuecomment-1950243901, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AOXHACDB2RD6ERWU6NBE34DYUDIGJAVCNFSM6AAAAABCQC4WQCVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMYTSNJQGI2DGOJQGE. You are receiving this because you were mentioned.Message ID: @.***>

ggreen86 commented 5 months ago

Closing issue; corrected in R41