Excel Multi Tabs Extracted To Text Files

[jbl1167]

I have a large number of Excel Files that need to be converted into text. Most of them are multi-tab and I wrote (learning from this forum) this little piece of code to print every page of every Excel file into text files. It work for a few of them but when the Excel have External links it failed. What I could do to fix this? I am calling every extracted text file as the workbook from which it came from with the page name. Here is the code and thank you for any idea

Sub Export_Excel_Workbooks()
Dim DB As Database
Dim RS As Recordset
Dim LC_STRING As String

Set DB = CurrentDb
Set RS = DB.OpenRecordset("WORKBOOKS")

Set objXL = CreateObject("Excel.Application")
On Error GoTo Err_
mdir = "C:\TEMP\excel3"
RS.MoveFirst
While RS.EOF = False

With objXL
LC_STRING = RS![Filename]
Set OBJWB = .Workbooks.Open(LC_STRING)
.Visible = True

For Each ws In ActiveWorkbook.Worksheets
mname = OBJWB.Name & "_" & ws.Name & ".txt"
Sheets(ws.Name).Select
ActiveWorkbook.SaveAs Filename:=mdir & "\" & mname, FileFormat:= _
xlText, CreateBackup:=False
Next ws


' objWB.SendMail strTo, strSubject
OBJWB.Close False
.Quit
End With
Set OBJWB = Nothing

RS.MoveNext
Wend
Set objXL = Nothing
MsgBox "Done!"
End Sub

 

[SkipVought]

Hi,

First, use the Workbook object rather than activeworkbook.

            For Each ws In OBJWB.Worksheets
                mname = OBJWB.Name & "_" & ws.Name & ".txt"
                ws.Activate[b]
                ws.Cells.Copy
                ws.[A1].PasteSpecial xlValues[/b]
                OBJWB.SaveAs Filename:=mdir & "\" & mname, FileFormat:= _
                xlText, CreateBackup:=False
            Next ws

try copy/paste the VALUES, prior to saving.

Skip,
[sub]
Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty![/sub]

 

[SkipVought]

...and you dont' want to QUIT Excel until everything's done.

Skip,
[sub]
Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty![/sub]