How to store a reference of a excel workbook in a variable in vbsc

[Store a reference of a excel workbook]

Hi,

I am trying to open a csv text file in excel using vbscript or vba. See the
code below

Const xlDelimited = 1
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True

set wb=objExcel.Workbooks.OpenText
"C:\Scripts\Test1.txt",,,xlDelimited,,,,,,,True,","

The above code is throwing an error. if i do not use the "set wb" to store
the reference to the opened workbook it works fine. But i wanted to store the
reference to this opened file so that i can use it later.

Please help me to solve this problem.

 

[Sheeloo]

Change

set wb=objExcel.Workbooks.OpenText
"C:\Scripts\Test1.txt",,,xlDelimited,,,,,,,True,","

to

wb=objExcel.Workbooks.OpenText
("C:\Scripts\Test1.txt",,,xlDelimited,,,,,,,True,",")

since
1. You are assigning the right hand side to wb...
2. objExcel.Workbooks.OpenText does not return an object... it is a BOOLEAN
method which returns True if the file is opened and False otherwise.

 

[Store a reference of a excel workbook]

Thnaks for your reply Sheeloo. But i my problem is not solved yet. I wanted
to store the reference to that opened file in a variable so that i can use it
later. Can you plz tell me the way?

 

[Sheeloo]

Use this
Const xlDelimited = 1
Set objExcel = CreateObject("Excel.Application")


fOpen=objExcel.Workbooks.OpenText
("C:\Scripts\Test1.txt",,,xlDelimited,,,,,,,True,",")


objExcel.Visible = True

if fOpen=true Then
set wb=objExcel.Workbooks("Test.txt")
wb.Sheets("Test").Range("A1").Value="Code OK"
end if

 

[Store a reference of a excel workbook]

Thank you very much Sheeloo. Its Working

Use this
Const xlDelimited = 1
Set objExcel = CreateObject("Excel.Application")


fOpen=objExcel.Workbooks.OpenText
("C:\Scripts\Test1.txt",,,xlDelimited,,,,,,,True,",")


objExcel.Visible = True

if fOpen=true Then
set wb=objExcel.Workbooks("Test.txt")
wb.Sheets("Test").Range("A1").Value="Code OK"
end if

 

[Sheeloo]

I am happy that it worked for you.

Essentially once you get a reference to Excel by
Set objExcel = CreateObject("Excel.Application")
you can get a reference to any Excel object (Workbooks, Worksheets,
Range,...) through objExcel...