Can an Excel spreadsheet by accessed by VB6?

[Garratt]

This whole question is probably stupid but:

Can an Excel spreadsheet by accessed by VB6?

I mean without using Shell and Sendkeys. This is stupid but - "SAY" ...... String = (C:\myspreadsheet.xls.Cells(1,1).value)

If true - can 255 access an Excel spreadsheet at once like a data-base (for read only)?

Just curious.... I know nothing about databases and VB6... I'm really only trying to do something that seems simple (my other question in the db forum):

------------------------------------------------------------------------

I've never played with VB6 and database files.... I've been browsing through the tutorials by Karl, but I'm missing specifically what I'm looking for.

Basically, I have an Access97 database at work with 5 tables.

I just need to be able to "read" and search the information. Really, just like you would use an array....

I want to access one table at a time and load all the column 1 cells in to listboxs. Then, if the data in the listbox is selected, I want to search through the table until the "data" is found and then read different cells in that row/record depending on what's going on (completly transparent to the user).

This is for a stand alone VB6 app, but (as an example) if it was for an Excel VB Script I would be doing something like:

Sub_LoadListBox_click()
 
LastCol = Cells.Find(What:="*", _
      SearchDirection:=xlPrevious, _
      SearchOrder:=xlByColumns).Column
 
For X = 2 to LastCol
  MyListbox.additem Cells(X,1).value
Next X
 
End Sub
 
'----------------------------------------
 
Sub Button_click()
For X = 0 To MyListbox.ListCount - 1
    If MyListbox.Selected(X) = True Then TempStr = OfficListBox.List(X)
Next X
 
Worksheets("MyWorksheet").activate
 
LastCol = Cells.Find(What:="*", _
      SearchDirection:=xlPrevious, _
      SearchOrder:=xlByColumns).Column
 
TempNumber = 0
For X = 2 to LastCol
  If Cells(X,1) = TempStr Then 
     TempNumber = X
      Exit For
  End If
Next X
 
If TempNumber = 0 Then Exit Sub
 
MyNameStr = Cells(X,1).value
MyNumberStr = Cells(X,2).value
MyAddressStr = Cells(X,3).Value
'(blah blah)
End Sub

Thanks for any help!

[phinds]

here's some stuff on excel

one sample app that manipulates an excel chart's data from vb then brings the chart into vb --- it's pretty slick

3 docs that I think are thread captures off this forum (I squirrel stuff away then forget what it is exactly)

[TheBionicOrange]

Draft in the MS Excel library in your references, then define a variable as an Excel object.

Now you have all the flexibility of Excel (almost!) to play with.

[Garratt]

Questions:

phinds - was there suppose to be a link or file in there?


TheBionicOrange -

"Draft in the MS Excel library in your references, then define a variable as an Excel object.

Now you have all the flexibility of Excel (almost!) to play with."


OK I am so much an amature, but I don't understand what you mean ... If I was looking for an example on what your talking about, what search string might I use.

I'll try searching the forumns for Excel+Library and Excel+Object to see if I get anything!

(thanks - much appreciated!)

The forums didn't really have anything useful - but I did find this:
http://home.netcom.com/~wburfine/CS_ExcelObject.html
http://www.freevbcode.com/ShowCode.Asp?ID=2156

But, can an Excel object be accessed by more than one VB application on a network ??

I still want to learn the DB stuff (later)

[TheBionicOrange]

Sorry Garratt, I will try to be a little clearer.

1. In VB, under the 'Project' pull down menu, there is an entry called 'References'. Go here and look for a reference called 'Microsoft Excel 9.0 Object Library'. Click it so its included in your project. Now you have access to Excels functionality.

2. Here is some sample code to give you an idea of what you can do ....

Private Sub Build_Spreadsheet()
    Dim objExcel As Excel.Application
    Dim Row As String
    
   
    Set objExcel = CreateObject("Excel.Application")
    objExcel.Workbooks.Open "C:\ORDER_" & frmMain.lblOrder & ".XLS"
 
    objExcel.Visible = False
    objExcel.DisplayAlerts = False
    
    With objExcel
        .Range("A1").Value = "Header Details for Order " & frmMain.lblOrder
        
        .Range("D3").Value = frmMain.lblSuppNo
        .Range("D4").Value = frmMain.lblSuppDes
        .Range("D5").Value = frmMain.lblSuppRef
        .Range("D6").Value = frmMain.lblBuyersName
        .Range("D7").Value = frmMain.lblCountry
        .Range("D9").Value = frmMain.lblCurrencyCode
        .Range("D10").Value = frmMain.lblCurrencyValue
 
 
etc ....

Be careful at the end of your code. Excel is notorious for leaving processes open. Any Excel objects you define you must set to nothing at the end to avoid this happening, e.g.

set objExcel = Nothing

Any more questions/problems feel free to email me ....

[CarlaKay]

You may already know this or you may not, but just in case you don't I thought that I would get a little more specific for you ( I know I like it when someone is specific when they answer my posts).

First you need to click the project option on your Vb menu then click references. Scroll down until you see Microsoft Excel X.X Library and put a check by it.

Then you can open an excel file like this:

using early binding

Dim objexcel As Excel.Application
Set objexcel = New Excel.Application
objexcel.Workbooks.Open ("c:\yourfile.xls")
objexcel.Visible = True (or False if you don't want it visible)

Or This:

using late binding

Dim objexcel As Object
Set objexcel = createobject("excel.application")

objexcel.Workbooks.Open ("c:\yourfile.xls")
objexcel.Visible = True (or False)


You seem to know some vba code, so after you have your object you can use straight vba code.
Example:

with objexcel
.Range("A1").Select

Remember when you are done to set all of your objects = to nothing or
you will have an open instances of excel.


Hope this helps

CK

[CarlaKay]

Whoops, looks like somebody beat me to the punch .

[TheBionicOrange]

But Carla yours is worded so much better than mine

[Garratt]

Hey thanks... that one was good too.


My only question left would be - What if 5 people were using a VB application at the same time that was trying to Access the data? It almost seems like it wouldn't work. (I will try it right now with two systems).


Oh wait - one more question.

Let's say I call the Excel object "EO".

So could I use most Excel commands with the object:

TempA = EO.Cells(1,3).Value

With EO.Activecells.font
color = blue
End with

Oh yeah - how would I do this:

LastCol = Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByColumns).Column


LastCol = EO.Cells.Find (etc.)



Ok I've got plenty to go play with - thanks!

[TheBionicOrange]

Assuming Carla doesn't beat me to it .......

Yes you can do "TempA = EO.Cells(1,3).Value"

Yes you can do :

"With EO.Activecells.font
color = blue
End with"

To find a string withing your spreadsheet use something like this :

intY = 1
    intX = 0
    With objSheet
        ' Boldface the Total Lines
            .Cells.Find(What:="Total", After:=objExcel.ActiveCell, LookIn:=xlFormulas, LookAt _
                :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
                False).Activate
        Do Until intX >= intY
            intX = objExcel.ActiveCell.Row
            objExcel.Selection.EntireRow.Font.Bold = True
            objExcel.Cells.FindNext(After:=objExcel.ActiveCell).Activate
            intY = objExcel.ActiveCell.Row
        Loop
        
    End With

objSheet is defined as an Excel.Worksheet
The above code looks for all occurences of the word "Total", and boldfaces the entire line its sat on.

Is that kind of what you were after ?

[CarlaKay]

Nah, I knew that you would answer, so I just sat back and waited (and got some lunch).

[TheBionicOrange]

I'm so predictable

[Garratt]

OK - Read only notifications do get ignored (COOL)

Problem - I can't get the sucker to close!

I've tried:

objExcel.Close
Set objExcel = Nothing
(no object error)
'------------------------
With objExcel
Close
End With
(no error - but no work)
'-------------------------
objExcel.Workbooks.Close "C:\test.xls"
Set objExcel = Nothing
(no object error)
'-------------------------
objExcel.Workbooks.Close
Set objExcel = Nothing
(no object error)


Oh - how can I make sure if user that kills the form/application - that the spreadsheet will get closed?

[TheBionicOrange]

Problem - I can't get the sucker to close!

What exactly do you mean ?

Do you mean there is a process called 'Excel' left over, which you can see from Task Manager ?

Does your application try to close Excel automatically, or do you leave it open for the user to close ?

I presume its from your application.

[Garratt]

Sorry - I can close the spreadsheet by hand, but I can not get the VB to close it when done.

So Set objExcel = Nothing

check

But, before that - how do I close Excel?

objExcel.close didn't work (tried a few other things).

Really, I wanted to leave it like:
objExcel.Visible = False

[TheBionicOrange]

Try this :

objExcel.Workbooks("Workbook.XLS").Close False
        objExcel.Quit
        Set objExcel = Nothing

The first line will close your workbook. Relpace 'Workbook.XLS' with your workbook name. The 'False' othe end is a quick way of saying NO to any changes. Replace with 'True' if you want to save your changes.


The second line quits Excel.

The third line flushes your Excel object, freeing it from memory.

[Garratt]

AHHH!!!!

(how do you fix this though)

I could not close EXCEL because the OBJECT was defined in a sub-routine and I was trying to close EXCEL in another sub routine!

How the heck to I get around that? Maybe a global variable to tell the primary sub-routine to just close Excel?

Anyway - objExcel.quit !

Man - this is some great source code and info I just found!

http://www.thescarms.com/Downloads/ExcelExport.zip

http://www.thescarms.com/vbasic/ExcelExport.asp


Also - this worked good:
With objExcel
Application.WindowState = xlMinimized
End With

That way it would still be visible if for some reason it was not closed, but it would minimize immediatley to get it out of the way.


Thanks for all the help!

[TheBionicOrange]

Define the Excel object in a module as a PUBLIC variable, and then you can open/play with/close it from anywhere.

Glad to see your making some headway

[Garratt]

I just saw my stupid error there - I accidentaly copied in

Set objExcel = CreateObject("Excel.Application")

to the Global/General area.


Hey man, I really appreciate your help - my brain has expanded. I really need to get up on the DB stuff next. Seems like that would be the much better way to do things so the "file" (spreadsheet/database) application doesn't actually open.


You'd probably laugh if you knew what I was doing this for - I'll email you.