Excel to Text file Conversion issue

[vinidel]

Hey guys,

I am struggling with this issue for a long time, please help.

I have this code that coverts excel sheets to text file. This was working till my system was upgraded to office 2003.

Here is the code:

Private Function SaveXLasText(ByVal XLSourceFilename As String, ByVal TempTextFilename As String) As Array
Dim i As Integer
Dim txtFileName() As String

Dim XLApp As New Microsoft.Office.Interop.Excel.Application
Dim TextFormat As Microsoft.Office.Interop.Excel.XlFileFormat = Microsoft.Office.Interop.Excel.XlFileFormat.xlTextWindows


Try

'XLApp = New Excel.Application
Dim ws As Microsoft.Office.Interop.Excel.Worksheet

''Hide Excel
XLApp.Visible = False
''Open an Excel workbook
XLApp.Workbooks.Open(XLSourceFilename)
''Turn off annoying messageboxes
XLApp.AskToUpdateLinks = False
XLApp.DisplayAlerts = False

''Save the file as text
i = 0
ReDim txtFileName(i)
txtFileName(i) = "NULL" 'Since VB.NET arry starts from Zero, so defaulting zero location to NULL
For Each ws In XLApp.Worksheets
i = i + 1
ReDim Preserve txtFileName(i)
txtFileName(i) = TempTextFilename & i & ".txt"
ws.SaveAs(FileName:=txtFileName(i), FileFormat:=TextFormat, CreateBackup:=False)
Next

Catch ex As Exception
Console.WriteLine(ex.Message)

Finally
'Close the workbook
XLApp.ActiveWorkbook.Close(Savechanges:=False)
''Turn annoying messages back on
XLApp.DisplayAlerts = True
''Quit Excel
XLApp.Quit()
XLApp.kill()
''Kill the variable
XLApp = Nothing
End Try

Code works till this point:

ws.SaveAs(FileName:=txtFileName(i), FileFormat:=TextFormat, CreateBackup:=False)

This is the error message I get:


Message "The file could not be accessed. Try one of the following:

• Make sure the specified folder exists.
• Make sure the folder that contains the file is not read-only.
• Make sure the file name does not contain any of the following characters: < > ? [ ] : | or *
• Make sure the file/path name doesn't contain more than 218 characters." String


Please help:

Thanks

 

[RonRepp]

I believe your syntax is incorrect for the fileformat portion.

This is what I use:

XL.ActiveWorkbook.SaveAs(Filename:="FileName.txt", FileFormat:=Excel.XlPivotFieldDataType.xlText, CreateBackup:=False)

I hope this works.

Ron Repp

If gray hair is a sign of wisdom, then I'm a genius.

 

[vinidel]

Thanks for your input RonRepp.

But I am saving worksheet not workbook. I have .xls files that have multiple worksheets and I have to save them as defferent text files.

With workbook method it just saves the first sheet even though you try to iterate in for each wb as workbook next loop.

Anyway, I tried your code I an still getting the same message. My good guess is it has something to do with the version change from office 2002 to office 2003.

When you manullay save an excel sheet to text file using saveAs option from file menu it does some changes to a copy of excel file in memory. It prompts you when you try to close the excel file after saving as text file that would you like to retain those changes to excel file. I guess that's where the issue should be.

I am doing my investigative work to find out. But any suggestion or guidance from anyone out there will be a big help to me.

Thanks again RonRepp for your suggestion.

Please help.

 

[chrissie1]

I think your question is VBA/excell orientated and I think you will get faster and better help in those forums.

Christiaan Baes
Belgium

"My new site" - Me

 

[RonRepp]

Excel will only save the active sheet as a text file.

I hope this helps.



Ron Repp

If gray hair is a sign of wisdom, then I'm a genius.