How do I correctly copy info from a USFM text document?

[MPW]

Hi All,

I am importing lines of text into a workbook using:

        Set oFso = CreateObject("Scripting.FileSystemObject")
        Set oFolder = oFso.GetFolder(ExcelFilesFolder) 'Path containing .sfm files.

        For Each oFile In oFolder.Files
            If UCase(Right(oFile, 4)) = ".SFM" Then
                Open oFile For Input As #1
                Do Until EOF(1)
                    Line Input #1, lineFromFile
                    If lineFromFile <> "" Then
                        r1 = r1 + 1
                        wb1.Sheets("Sheet1").Cells(r1, 1)= lineFromFile
                    End If
                Loop
                Close #1
            End If
        Next oFile

This works great except that it is not being converted correctly because of the usfm formatting of the original doc. Here is an example:

 “‘And you, O Bethlehem, in the land of Judah, are by no means least among the rulers of Judah; for from you shall come a ruler who will shepherd my people Israel.’”
 ---------------------------------------------------------------------becomes ---------------------------------------------------------------------
 “‘And you, O Bethlehem, in the land of Judah, are by no means least among the rulers of Judah; for from you shall come a ruler who will shepherd my people Israel.’â€"

As you can see, it is not converting the quotes correctly. There are other inconsistences, but you get the idea.

As a test I manually copied this from the .sfm and pasted into the workbook. It was perfect. My question is "How do I copy and paste my info instead of the this = that method?"

Thanks

 

[Alex Blakenburg]

Its a bit tricky looking at this without a sample file but have you tried to see what using this looks like in Excel.
Data > Get Data > Legacy Wizards > From Text (Legacy)

If its workable, the macro recorder will give you the info you need to use it in VBA although I did have to removed the line ".CommandType = 0" to make it work.

If you can't see those options you may need to turn them on first under Excel Options

 

[Alex Blakenburg]

If you are only using VBA to load everything from a folder, then Power Query > Get Data > From folder might also work for you if on the preview screen you set the File Origin to 65001: Unicode (UTF-8)

 

[MPW]

Thanks Alex,
I took a look at the Get Data option. While the Legacy option did not display quite the same as what you had, I was able to get the correct results.
I think I can get what I need from this method. However, when I recorded this process and tried to rerun it I got;

Any ideas how I messed it up?

Follow up question: Can I cut this query down or do I need all of these fields?
In addition I have to break any query connections to the workbook after it loads the data into the sheet. My goal is to process one file at a time. Clear the sheet and then load the next file.

    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;C:\Users\mwright\Desktop\GraphQL_spun\esversion_ESVENG_Full\41MATNE07.sfm", Destination:=Range("$A$1"))
        .CommandType = 0
        .Name = "41MATNE07"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 65001
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With

 

[MPW]

A quick follow up on this. I got everything working that I mentioned earlier. The debug went away when I deleted

        .CommandType = 0

from the With statement. Thank you again Alex, you saved me quite a bit of time with this idea. I only hope that the "Legacy" option stays around for a while.

 

[Alex Blakenburg]

Thank you for keeping me updated. I am in Australia and its already 10:30am Saturday morning here.
One of your comments made me think about the basic File > Open > Text Import Wizard and with the File Origin set to 65001 : Unicode (UTF-8), it performs in the same manner as the Legacy Wizard with the exception that it will open in a new Workbook so you would then need to copy and paste it into your main workbook.
This is close to one of your initial alternate options of:

"How do I copy and paste my info instead of the this = that method?"

File > Open

Recorded Macro

Sub FileOpen()
'
' Recorded Macro
'
    Your_Full_FileName = oFile ' Just to highlight the need for the FullFileName
    
    Workbooks.OpenText filename:= _
        Your_Full_FileName, Origin:=65001, _
        StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
        ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False _
        , Space:=False, Other:=False, FieldInfo:=Array(1, 1), _
        TrailingMinusNumbers:=True

End Sub

 

[MPW]

Hey Alex down under!

I appreciate the alternate method and might employ that in another project, but the initial answer you provided is working quite well. The VBA in the workbook pulls in the .sfm files formatted correctly. Don't know if you are interested but I then parse out the narrator parts from the character parts and then align that output with another language. It will produce a bilingual version of a New Testament which then can be used to record.

Thank you again for getting me out of the rut of futility!

 

[Alex Blakenburg]

Glad I could help.

 

[MPW]

Amazing help considering you are on the most dangerous content Writing code while fending off koalas and all that. Stay free!