TM1 Forum

Posted: **Tue Nov 29, 2011 3:16 am**

Good Day!

Hi Guys,

May I just ask if anyone knows a utility which can convert worksheets of an Excel file specifically .xlsx format? And a utility that can convert hidden worksheets into .txt files.

What I have now is a utility to convert Excel files of .xls format only. And it prompts an error if a workbook being converted has hidden sheets in it. Please help.

Anyone who has a utility?

Thanks so much guys! I will really appreciate your help.

Bunch

Posted: **Tue Nov 29, 2011 3:43 am**

bunchukokoy wrote:May I just ask if anyone knows a utility which can convert worksheets of an Excel file specifically .xlsx format? And a utility that can convert hidden worksheets into .txt files.

What I have now is a utility to convert Excel files of .xls format only. And it prompts an error if a workbook being converted has hidden sheets in it. Please help.

Anyone who has a utility?

I'd be extremely surprised if the utility that you have couldn't work with .xlsx files. It's very unlikely that any utility would be working directly with the data file itself; there's just no point in doing that. It would be far more likely to be loading the file into an Excel session (and if you aren't seeing this happen, it could just as easily be doing this via a hidden session launched via automation), then simply doing a Save As command to export each sheet into text format.

There's no reason why the Excel session couldn't load an .xlsx if it's 2007 or 2010, or even earlier versions if the converter has been installed. Once it's loaded, there's really no difference between the two types if you're staying within the 65536 row *256 column limitations of an .xls. (If the data in your .xlsx isn't, that could be where you have a problem if your utility isn't designed to deal with that.)

That aside, if it can load the workbook, it can iterate through the sheets unless... the .xlsx file has the workbook structure protected.

I'd suggest manually loading the workbook, then checking the protection state of the workbook (as well as whether it's an "oversized" one. (It's on the Review tab in Excel 2010, which of course is very intuitive.

) If it's protected, unprotect it. If you're prompted for a password, that will probably be why your utility is erroring out, not the fact that the file is in .xlsx format.

Posted: **Tue Nov 29, 2011 4:50 am**

Hi Sir Alan,

You're right, xlsx is not a problem. I tried it. There's a 2007 installed in the unit. It converted well, but that's because sheets were all not hidden. What seems to be the problem here whenever a file has hidden sheets, it errors. What I need to do now is to think how I can show these hidden sheets before converting the file.

Thanks.

Bunch

Posted: **Tue Nov 29, 2011 5:08 am**

bunchukokoy wrote:Hi Sir Alan,

You're right, xlsx is not a problem. I tried it. There's a 2007 installed in the unit. It converted well, but that's because sheets were all not hidden. What seems to be the problem here whenever a file has hidden sheets, it errors. What I need to do now is to think how I can show these hidden sheets before converting the file.

Put this in your Personal workbook and run it before you run your utility. (5 minute "off the top of my head" code, not extensively tested.)

Code: Select all

Sub MakeAllSheetsVisible()
'Alan Kirk, 29-Nov-11

Dim wbk As Excel.Workbook
Dim wks As Excel.Worksheet

On Error Resume Next
Set wbk = ActiveWorkbook
On Error GoTo ErrorHandler

If wbk Is Nothing Then
    Err.Raise vbObjectError + 20000, , _
     "There is no active workbook."
Else
    If wbk.ProtectStructure Then
        'Try without a password
        On Error Resume Next
        wbk.Unprotect
        On Error GoTo ErrorHandler
        If wbk.ProtectStructure Then
            Err.Raise vbObjectError + 20000, , _
             "The workbook structure is password protected. Please unprotect it."
        End If
    End If
End If

For Each wks In wbk.Worksheets
    wks.Visible = xlSheetVisible
Next

ExitPoint:

On Error Resume Next

Set wks = Nothing
Set wbk = Nothing

On Error GoTo 0

Exit Sub

ErrorHandler:

MsgBox "Error while unprotecting sheets. Numner " & Err.Number & vbCrLf & Err.Description

Resume ExitPoint

Posted: **Tue Nov 29, 2011 7:09 am**

Thank you Sir Alan. I'll try it.

That's my only problem now. The workbook and worksheets, including hidden ones, are not password-protected. But still my utility prompts an error because of these hidden sheets.

I'll inform you Sir.

Thank you so much for the script.

TM1 Forum

Utility to Convert .xlsx and .xls into .txt

Utility to Convert .xlsx and .xls into .txt

Re: Utility to Convert .xlsx and .xls into .txt

Re: Utility to Convert .xlsx and .xls into .txt

Re: Utility to Convert .xlsx and .xls into .txt

Re: Utility to Convert .xlsx and .xls into .txt