Get Open File and Text to Columns Issue

[rex759]

Hello,

I used a version of the macro below to open a “Text” like file and format it Text To Columns. I thought I could use the same kind of code with some minor changes to format a .WRI file but It fails at the text to column portion. It looks like there is nothing on the clipboard to paste. When I record a macro, it works fine. Maybe I have been looking at it too long but the solution is not coming to me. Any help is appreciated.

Macro Recorder

Workbooks.OpenText Filename:= _
"https://onedrive.company.com/personal/John_Smith_company_com/Documents/Desktop/207/BR207.WRI" _
        , Origin:=437, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array( _
        Array(0, 1), Array(12, 2), Array(20, 1), Array(28, 2), Array(48, 2), Array(92, 1), Array( _
        123, 1), Array(126, 1), Array(149, 1), Array(160, 1), Array(172, 1), Array(182, 1), Array( _
        203, 1), Array(214, 1)), TrailingMinusNumbers:=True

Version 1

Sub Pullfile()

  Application.ScreenUpdating = False
    Application.DisplayAlerts = False

'Select a file
sourcebk = Application.GetOpenFilename

If sourcebk = False Then
        ' They pressed Cancel
        MsgBox "Stopping because you did not select a file"
      Application.StatusBar = "Done"
 Exit Sub
    
    Else

'Save to desktop
Dim Path As String

Path = CreateObject("WScript.Shell").SpecialFolders("Desktop") & "\"
ActiveWorkbook.SaveAs Path & "Text File.xlsx"

tempfile = ActiveWorkbook.Name

   Set sourcebk2 = Workbooks.Open(sourcebk)
        sourcebk2.Worksheets(1).UsedRange.Copy
        Workbooks(tempfile).Worksheets("Sheet1").Range("A1").PasteSpecial Paste:=xlPasteValues
        sourcebk2.Close False
    End If
 
 Application.CutCopyMode = False

'Text to columns, define range
 Set Rng = [A1]
    Set Rng = Range(Rng, Cells(Rows.Count, Rng.Column).End(xlUp))
      
        Rng.TextToColumns Destination:=Rng, DataType:=xlFixedWidth, OtherChar:="|", _
        FieldInfo:=Array(Array(0, 1), Array(12, 2), Array(20, 1), Array(28, 2), Array(48, 3), _
        Array(92, 1), Array(123, 1), Array(126, 1), Array(149, 1), Array(160, 1), Array(172, 1), _
        Array(182, 1), Array(203, 1), Array(213, 1)), TrailingMinusNumbers:=True

End Sub

 

[Marc L]

Hi,​

​

without the source file neither a complete elaboration it's just a thread for some mind readers forum ! …​

 

[rex759]

The issue has been resolved.

 

[smozgur]

Glad to hear it has been resolved.

Please post your solution. Then it is perfectly fine to mark your post as the solution to help future readers.

 

[rex759]

The issue was the sync and One Drive. I applied these two lines of code, one at the beginning and at the end.

Turn Off One Drive

Call Shell("cmd.exe /S /C" & "%LOCALAPPDATA%\Microsoft\OneDrive\OneDrive.exe /shutdown")

Turn On One Drive

call Shell("cmd.exe /S /C" & "start %LOCALAPPDATA%\Microsoft\OneDrive\OneDrive.exe /background")