Reading in data from a text file into excel macro

[Shalih]

Hi,

I need to read a data into an excel macro. I have the macro ready which can process once if the data is read in. the text file will have only one line of data (of 4 characters). The location of this text file is also hard coded. All i want is to read the data from the text file to be provided as input to the other macro's.

 

[Andrew Poulsom]

Welcome to MrExcel.

Look here:

http://www.excelforum.com/excel-programming/332741-getting-text-file-into-a-vba-string-variable.html

 

[Shalih]

Hello Andrew,
Thanks for the quick response! But i still face error 52 and it points to [Open "F:\AAA\BBB\CCC\DDD\EEE\FFF\shalih.txt" For Input As #myName3]

My Code looks like:
[
Dim myFile As String
Dim myDir As String
Dim myName0 As String
Dim myName1 As String
Dim myName2 As String
Dim myName3 As Integer
Dim myName4 As String
Open "F:\AAA\BBB\CCC\DDD\EEE\FFF\shalih.txt" For Input As #myName3
Input #myName3, ShortText
Close #myName3
myName3 = ShortText
myDir = "TEXT;F:\AAA\BBB\CCC\DDD\EEE\FFF\"
myName0 = "D3_I_AAA-500-BCKUPTIME-"
myName1 = Year(Date)
myName2 = myName0 & myName1
'myName3 = Application.InputBox("Enter MMDD Filename", Type:=2)
myName4 = ".txt"
myName5 = myName3 & myName4
myName6 = myName2 & myName5
myFile = myDir & myName6


All i wanted is to pass the value from shalih.txt to the variable myName3

 

[Shalih]

woohooo.... it worked fine now... thanks Andrew...

 

[Andrew Poulsom]

You haven't assigned a value to myName3 and you can only use that variable once. Try replacing:

Open "F:\AAA\BBB\CCC\DDD\EEE\FFF\shalih.txt" For Input As #myName3
Input #myName3, ShortText
Close #myName3

with:

Dim FileNum As Integer
FileNum = FreeFile
Open "F:\AAA\BBB\CCC\DDD\EEE\FFF\shalih.txt" For Input As #FileNum
Line Input #FileNum, ShortText
Close #FileNum