Vbscript to extract string from an excel file

[kerbaule]

BOnjour,

Is it possible to extract in a text file the content of
one cell ( cells E7 for example) of an excel file with vb

Many thanks

Elisabeth

 

[BobRodes]

Sure. Open the excel file in VB. (Set a reference to the Excel Application object library.) Use the application object to grab the contents of a cell into a variable. Use the FileSystemObject (reference to Microsoft Scripting Runtime) to write the contents of the variable to a text file, or use the open and write commands.

HTH

Bob

 

[kerbaule]

MAny thanks

Have you an example of a script I can use ?
I don't kno vb scripting at all .

thanks
Regards

Elisabeth

 

[fredericofonseca]

search these forums for the keywords mentioned and you will find examples.

Then if you still have problems show us the code you've tried, along with any errors you may be encountering and we will try and help further.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[BobRodes]

I second that.

However, this will help you get started. In a VB project, put the following in, say, a command button click event handler:

dim xl as Excel.Application
set xl = New Excel.Application
xl.workbooks.open "MyPath\MyWorkbook"
xl.Visible = true

When you hit the button, you'll find that an excel application opens. You can use any VBA Excel command by referencing the xl object first, as in xl.DoThisExcelCommand. Note also that there is an object hierarchy to learn, for example there is an application object that has a workbooks collection that has a sheets collection and a charts collection.

HTH

Bob
HTH

Bob

 

[kerbaule]

I have start a small script but I think it's not correct
The output file is not created ...

Dim xlApp, xlBook, xlSht
Dim filename, value1

filename = "c:\ek\ek.xls"

Set xlApp = CreateObject("Excel.Application")
set xlBook = xlApp.WorkBooks.Open(filename)
set xlSht = xlApp.activesheet

value1 = xlSht.Cells(5,7)


Function WriteResultFile(value1)
Const ForReading=1, ForWriting=2, ForAppending=8
filename2 = "c:\ek\ek.TXT"
Set fso = CreateObject("Scripting.FileSystemObject")
Set ts = fspenTextFile(filename2, ForWriting, True)
ts.WriteLine(value1)
ts.Close
WriteResultFile=0

'always deallocate after use...
set ts = Nothing
set fso = Nothing
end function

xlBook.Close False
xlApp.Quit

Elisabeth

 

[BobRodes]

You're on the right track, Elisabeth. Assuming that you can get a value for value1 to go somewhere other than a text file, you need to troubleshoot the fso stuff, since there are several reasons why you might not be writing your text file. I can see that you borrowed the code from OpenTextFile, so look at the related commands and see if you can isolate where your code is failing to write to the place you want it to.

Good luck,

Bob

 

[fredericofonseca]

A few things.

First ALWAYS use "option explict" on all your modules. This way you avoid problems with missing/wrong declarations of variables.

Second, always define the type of variables you use.

On your code you should change
Function WriteResultFile(value1) to be a completely defined function such as

Function WriteResultFile(value1 as string) as boolean

third you do not assign a value to a function. you assign the return value form a function to a variable.

So on your code you should replace
value1 = xlSht.Cells(5,7)
by
dim retval as boolean
retval = writeresultfile(xlSht.Cells(5,7).value)



Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[BobRodes]

I think she's writing in vbScript, Frederico, in which case your very good advice about option explicit and variable typing will not apply. If she weren't, she probably wouldn't be declaring those constants.

Also, I'm not sure what you're saying about assigning values and functions. In a certain sense, you do both. What is clear is that her code is correct, as far as it goes, for she......aha!!! The light goes on! Elisabeth, you haven't called your function anywhere! Of course it won't work, if you don't use it.

Now then. In the first place, it doesn't appear that you need a function here for sure. As Frederico's suggestion implies, the only reason you'd need to do that is to determine whether the function worked properly or not. If you do, then yes, write a function. Otherwise, just remove your function definition and let the code inside it run. If you need to use this code in other places, consider using a sub, which doesn't return a value.

HTH

Bob

 

[fredericofonseca]

BobRodes,

Yes you are correct if we consider the title. I was just doing vb as this is the VB forum.

I missed the fact the function is not called. I saw

value1 = xlSht.Cells(5,7)

Function WriteResultFile(value1)

and just considered "value1" as being the call to the function. My mistake.


The function itself is correct (apart from not declaring what type of variable it is returning) and works fine on VB.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[BobRodes]

I missed the fact the function is not called.

Me too, but you put me on to it.

 

[kerbaule]

BOnjour,

I have done some modification but have still some problem could somebody help me ...

many thanks

Elisabeth

Dim xlApp, xlBook, xlSht
Dim filename, value1, retval

filename = "c:\ek\ek.xls"

Set xlApp = CreateObject("Excel.Application")
set xlBook = xlApp.WorkBooks.Open(filename)
set xlSht = xlApp.activesheet

Function WriteResultFile(value1)

retval = writeresultfile(xlSht.Cells(5,7))
Const ForReading=1, ForWriting=2, ForAppending=8
filename2 = "c:\ek\ek.TXT"
Set fso = CreateObject("Scripting.FileSystemObject")
Set ts = fspenTextFile(filename2, ForWriting, True)
ts.WriteLine(retval)
ts.Close
WriteResultFile=0

'always deallocate after use...
set ts = Nothing
set fso = Nothing
end function

xlBook.Close False
xlApp.Quit

 

[fredericofonseca]

You need to split you code according to what you are doing.

If using a sub and a function you do as follows.

sub my_sub()
Dim xlApp, xlBook, xlSht 
Dim filename, value1, retval

filename = "c:\ek\ek.xls"

Set xlApp = CreateObject("Excel.Application")
set xlBook = xlApp.WorkBooks.Open(filename)
set xlSht = xlApp.activesheet

retval = writeresultfile(xlSht.Cells(5,7).value)

xlBook.Close False
xlApp.Quit

end sub

Then create the function. This is a separate entity from the above code, so it needs to be OUTSIDE it.

Function WriteResultFile(value1) 
  Const ForReading=1, ForWriting=2, ForAppending=8
  filename2 = "c:\ek\ek.TXT"
  Set fso = CreateObject("Scripting.FileSystemObject")
  Set ts = fso.OpenTextFile(filename2, ForWriting, True)
  ts.WriteLine(value1)
  ts.Close
  WriteResultFile=0

  'always deallocate after use...
  set ts = Nothing
  set fso = Nothing
end function

otherwise you do

Dim xlApp, xlBook, xlSht 
Dim filename, value1

filename = "c:\ek\ek.xls"

Set xlApp = CreateObject("Excel.Application")
set xlBook = xlApp.WorkBooks.Open(filename)
set xlSht = xlApp.activesheet

value1 = xlSht.Cells(5,7).value

  Const ForReading=1, ForWriting=2, ForAppending=8
  filename2 = "c:\ek\ek.TXT"
  Set fso = CreateObject("Scripting.FileSystemObject")
  Set ts = fso.OpenTextFile(filename2, ForWriting, True)
  ts.WriteLine(value1)
  ts.Close
  WriteResultFile=0

  'always deallocate after use...
  set ts = Nothing
  set fso = Nothing

xlBook.Close False
xlApp.Quit

e.g. the last case has a sequence of VB code, without calling any function.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[BobRodes]

Yes, there are a couple of problems with your code, Elisabeth, but you're almost there.

First, your function is calling itself on its first line. A function calling itself is called a recursive procedure call, and if you don't write some sort of code to break out of the loop, you get an endless loop of procedure calls. It wouldn't surprise me if you got an out of stack space error, and if you look at the call stack in debug mode, you should see an endless loop of calls to writeresultfile. Now, Frederico's first example fixes the recursive procedure call for you.

Second, your function always returns 0. In VB, you return values from functions by setting the name of the function equal to the return value you want. I'm not clear on why you need a return value to accomplish what you want to do. If you always return the same value from a function, why you need to return a value at all? So, maybe you need a Sub, not a Function. I would do this:

Sub WriteResultFile(value1) 
  Const ForReading=1, ForWriting=2, ForAppending=8
  filename2 = "c:\ek\ek.TXT"
  Set fso = CreateObject("Scripting.FileSystemObject")
  Set ts = fso.OpenTextFile(filename2, ForWriting, True)
  ts.WriteLine(value1)
  ts.Close
  'WriteResultFile=0  Get rid of this line

  'always deallocate after use...
  set ts = Nothing
  set fso = Nothing
end Sub

And then, here's how I would call it:

WriteResultFile xlSht.Cells(5,7).value
[/cide]

Finally, you use a Sub typically when you need to reuse functionality in the context of your program, so you don't write the same code twice in two places.  It's not clear that you need to do this, so you can consider just not using a separate procedure at all, as Frederico's last piece of code demonstrates.  

It's important to understand how Subs and Functions work.  If you need to reuse code in your application, put it in a procedure (a procedure is a sub or a function).  If you need to return a value from the procedure, use a function, otherwise use a sub.  In other words, as a general rule, only add complexity when you need it.

HTH

Bob

 

[kerbaule]

Many thanks for you help.

The script is now working ...

Regards

Elisabeth

 

[BobRodes]

The beast has been tamed! Always a good feeling....

Bob