VBA - Loading text file without getfileopen and without giving full file path

[siva1]

Hi,
I am trying to load a text file in Execl VB (which is there in the same excel location). I don't want to mention the full file path and I don't want to use GetOpenFilename (browse option). Since the excel and text file in samelocation , I would like to try other possibilities.
Could you please share ideas.

 

[vletm]

siva1​

Where do You try to load a text file?
... You didn't specific eg to sheet?
Here one link with many variations:

The VBA Guide To Interacting With Text (.Txt) Files

Text files can be a very fast and simple way to read and store information. I like to use them to save settings for my VBA add-ins and I have seen situations where databases have exported large amounts of data into .txt files instead of Excel files (especially back in the days with Excel 2003).

www.thespreadsheetguru.com

 

[siva1]

siva1​

Where do You try to load a text file?
... You didn't specific eg to sheet?
Here one link with many variations:

The VBA Guide To Interacting With Text (.Txt) Files

Text files can be a very fast and simple way to read and store information. I like to use them to save settings for my VBA add-ins and I have seen situations where databases have exported large amounts of data into .txt files instead of Excel files (especially back in the days with Excel 2003).

www.thespreadsheetguru.com

Hi Vletm.
I have gone through the shared link. I don't want to declare text file path. I am writing VB in excel. This excel and text file are in the same folder. So is this any chance to access from vb without mentioning the text file path. I will share this text file and excel sheet to my colleague. If I am declaring file path , then others need to modify the input file location. To avoid this am looking for other alternate ideas. TIA

 

[vletm]

siva1​

Did You answer to my the 1st question?
There were ideas, how to avoid declare the text file path.

 

[Marc L]

This excel and text file are in the same folder.
So is this any chance to access from vb without mentioning the text file path.

Hello, just using the workbook Path property, obviously … Like you can check within VBA help.​

​

 

[siva1]

@Marc L
I am new to VB and learning.
I am using below steps.

>>> use code - tags <<<

Dim folderpath as String
Dim txtfile as String

Folderpath = This workbook.path & "\"
'Getting excel path
Txtfile = folderpath & "\input.txt"
'Assigning excel path as input file text path

Is this the correct procedure

 

[Logit]

Try :

Sub AddTextFileToSpreadsheet()

Dim FilePath As String
Dim TxtLine As String
Dim StartCell As Range
Dim x As Long

'Text File Path
  FilePath = ThisWorkbook.Path & "\Excel.txt"

'Determine first cell you want data in
  Set StartCell = ActiveSheet.Range("A1")

'Open the text file in a Read State
  Open FilePath For Input As FreeFile
 
'Loop through each row in Text File and Write to Spreadsheet
  Do Until EOF(1)
    Line Input #1, TxtLine
    StartCell.Offset(x) = TxtLine
    x = x + 1
  Loop
    
'Close Text File
  Close FreeFile
 
End Sub

 

[siva1]

@Logit Thank you

 

[Logit]

Welcome