CODEWITHSUNDEEP
excelms-accessvbaformat-conversion
mccdo
mccdo

Reputation: 55

Converting Excel files to Text using Access VBA

I am trying to save every .xls file in a folder location as text using Access VBA. I have patched together the code below. However, it only successfully saves the 1st excel file as text and then it seems to hang.

I have reviewed this post which I think achieves something similar to what I want: HERE .But I have been at this for so long that nothing is making sense!! Any ideas? Is it getting stuck on ActiveWorkbook.Close?

 Public Sub FormatAsText()
Dim myfile As String
Dim xlApp As Excel.Application
Dim xlWB1 As Excel.Workbook

Dim objFso As FileSystemObject
Dim strPath As String
Dim objFolder As Folder
Dim objFile As File

        Set xlApp = New Excel.Application
        strPath = "C:FolderLocation..."

        Set objFso = CreateObject("Scripting.FileSystemObject")
        Set objFolder = objFso.GetFolder(strPath)

        myfile = Dir(strPath & "\*.xls")

            For Each objFile In objFolder.Files
                If objFile.Name Like "*.xls" Then
                    Workbooks.Open objFile, ReadOnly:=False
                    ActiveWorkbook.SaveAs FileName:=strPath & "\" & Replace(myfile, ".xls", ".txt"), FileFormat:=xlTextWindows
                    ActiveWorkbook.Close
                End If
            Next objFile

        Debug.Print myfile

Set xlApp = Nothing
Set xlWB1 = Nothing

Set objFso = Nothing

Set objFolder = Nothing
Set objFile = Nothing

End Sub

Upvotes: 1

Views: 2649

Answers (1)

HansUp
HansUp

Reputation: 97101

By adding xlApp.Visible = True after Set xlApp = New Excel.Application you discovered Excel is waiting for you to tell it whether to overwrite an existing file which has the same name as the one you're trying to save.

That happens because you assign a value to myfile just once ...

myfile = Dir(strPath & "\*.xls")

... but then re-use that same name as the basis for generating the name of each .txt file you attempt to save ...

FileName:=strPath & "\" & Replace(myfile, ".xls", ".txt")

I think you can just substitute objFile.Name there because it should match the name of the current workbook file ... which means your .txt file name will be different each time through the For Each loop. 

FileName:=strPath & "\" & Replace(objFile.Name, ".xls", ".txt")

Upvotes: 2

Related Questions