Read/Import a txt file (MUST WORK FUNCTION SUB)

XLS Padlock
1

Works flawlessly in excel need it to be working on your compiled exe file too

This line an error too -Input #intFNumber, DrawDate, P1, sP2, sP3, Draw, dDate

Sub ReadTextFile

Application.ScreenUpdating = False

'Read data from a text file


Dim DrawDate As String
Dim dDate As Date
Dim Draw As String
Dim P1 As String
Dim sP2 As String
Dim sP3 As String
Dim intSize As Integer
Dim Sheet1 As Worksheet
Set Sht = ThisWorkbook.Worksheets("Import")
ActiveSheet.Range("A1").Select
Dim sFName As String
Dim intFNumber As Integer
Dim lRow As Long

Sheets(“Import”).Protect Password:=“Test123”, UserInterFaceOnly:=True
Sheets(“HistoryD”).Protect Password:=“Test123”, UserInterFaceOnly:=True

ChDir "C:\Program Files\Test"
Dim fpath: fpath = Application.GetOpenFilename(“Text Files (.txt),.txt”)
If fpath = False Then Exit Sub
With ActiveSheet.QueryTables.Add(Connection:=“TEXT;” & fpath, Destination:=Range(“A1”))

End With

'The full path of the text file that will be opened
sFName = ThisWorkbook.Path & "\Import1.txt"

'Get an unused file number
intFNumber = FreeFile
                                     
On Error Resume Next

'Prepare text file for reading
Open sFName For Input As #intFNumber

'Check if the text file was found
If Err.Number <> 0 Then
    MsgBox "Text file not found!", vbCritical, "Error!"
    Exit Sub
End If

On Error GoTo 0


'Clearing the sheet
Sht.Activate

Sht.Cells.Clear

lRow = 1

'Loop until the end of file
Do While Not EOF(intFNumber)
    
    'Read data from the text file
    
    Input #intFNumber, DrawDate, P1, sP2, sP3, Draw, dDate

'Input #intFNumber, (DrawDate, “yy/mm/dd”), sP1, sP2, sP3, Draw
’Write selected data to the worksheet
With Sht
.Cells(lRow, 1) = DrawDate
.Cells(lRow, 2) = P1
.Cells(lRow, 3) = sP2
.Cells(lRow, 4) = sP3
.Cells(lRow, 6) = Draw
End With

    'Address next row of worksheet
    lRow = lRow + 1

Loop
  
'Close the text file
Close #intFNumber
    
'Fitting column width
Sht.Cells.EntireColumn.AutoFit

'Just showing where the output data are
Sht.Activate
Range("A2").Select


'Inform the user about the process
MsgBox "Values from file '" & sFName & "' were imported to sheet '" & Sht.Name & "'!", , "GOLFER65 TEST APP"
Sheets("HistoryD").Activate



Application.ScreenUpdating = True
ThisWorkbook.Save
Application.Quit

End Sub

2

The problem is the same as in some other topics you posted, you’re working with ThisWorkbook.Path. But, once in the EXE file, the workbook file is stored in a virtual folder so you must use VBA extensions to access files in the same folder as in the EXE file.
See the user guide:

image
image699×383 36.8 KB

So your code here:
'The full path of the text file that will be opened
sFName = ThisWorkbook.Path & “\Import1.txt”
must be replaced with:
'The full path of the text file that will be opened
sFName = PathToFile("Import1.txt")

provided that you placed the
Public Function PathToFile(Filename As String)
in a module of your workbook for instance.

3

Doesnt work
Object required

I have changed the directory now to c:\Test
Public Function PathToFile(filename As String)
Dim XLSPadlock As Object
On Error GoTo Err
Set XLSPadlock = Application.COMAddIns(“GXLSForm.GXLSFormula”).Object
PathToFile = XLSPadlock.PLEvalVar(“EXEPath”) & filename
Exit Function
Err:
PathToFile = “C:\Test\Import1.txt”
End Function

Sub ReadTextFile()
Application.ScreenUpdating = False

'Read data from a text file


Dim DrawDate As String
Dim dDate As Date
Dim Draw As String
Dim P1 As String
Dim sP2 As String
Dim sP3 As String
Dim intSize As Integer
Dim Sheet1 As Worksheet
Set Sht = ThisWorkbook.Worksheets("Import")
ActiveSheet.Range("A1").Select
Dim sFName As String
Dim intFNumber As Integer
Dim lRow As Long

Sheets(“Import”).Protect Password:=“Test123”, UserInterFaceOnly:=True
Sheets(“HistoryD”).Protect Password:=“Test123”, UserInterFaceOnly:=True

ChDir "C:\Test"
Dim fpath: fpath = Application.GetOpenFilename(“Text Files (.txt),.txt”)
If fpath = False Then Exit Sub
With ActiveSheet.QueryTables.Add(Connection:=“TEXT;” & fpath, Destination:=Range(“A1”))

End With

'The full path of the text file that will be opened

sFName = PathToFile(Import1.txt)
'Get an unused file number
intFNumber = FreeFile

On Error Resume Next

'Prepare text file for reading
Open sFName For Input As #intFNumber

'Check if the text file was found
If Err.Number <> 0 Then
    MsgBox "Text file not found!", vbCritical, "Error!"
    Exit Function
End If

On Error GoTo 0


'Clearing the sheet
Sht.Activate

Sht.Cells.Clear

lRow = 1

'Loop until the end of file
Do While Not EOF(intFNumber)
    
    'Read data from the text file
    
    Input #intFNumber, DrawDate, P1, sP2, sP3, Draw, dDate

'Input #intFNumber, (DrawDate, “yy/mm/dd”), sP1, sP2, sP3, Draw
'Write selected data to the worksheet
With Sht
.Cells(lRow, 1) = DrawDate
.Cells(lRow, 2) = P1
.Cells(lRow, 3) = sP2
.Cells(lRow, 4) = sP3
.Cells(lRow, 6) = Draw
End With

    'Address next row of worksheet
    lRow = lRow + 1

Loop
  
'Close the text file
Close #intFNumber
    
'Fitting column width
Sht.Cells.EntireColumn.AutoFit

'Just showing where the output data are
Sht.Activate
Range("A2").Select







Call Sheet4.CopyHis
Application.ScreenUpdating = True
ThisWorkbook.Save
Application.Quit

End Function

End Sub

Object required error on this line sFName = PathToFile(Import1.txt)

4

I am sending my excel workbook with the import function to your email support…Its working in every version of excel except the compiled exe file. I need this solved urgently if your support team can look at it please Thank you

Golfer65

5

There was an obvious typo error in the code. Correct one is:
sFName = PathToFile("Import1.txt")
Note the missing quotes. Please retry.

6

Sure, we can also take a look at your workbook if you agree to share it with us.

7

Thank you I have no problem to share it with you in a dummy …one version will demonstrate it work in every excel version -second copy is the compiled version thats not working

Thank you support

8

If yes, you can zip them and upload them to https://uploadfiles.io/
This is a free file hosting service: after upload, they will give you a URL. Please send us this URL so that we can download the Zip archive.

Have you also tried the fix mentioned:
sFName = PathToFile("Import1.txt")

9

Support some great news I managed to sort it out both my functions work now…so now my only baby is the Api…Thank you for all your help -Awesome software just need to try and figure out APi’s now

Thank you

Golfer65

10

Good to know. Thanks for reporting :ok_hand: