3 Replies - 16380 Views - Last Post: 13 March 2011 - 05:53 PM Rate Topic: -----

#1 otomatis  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 9
  • Joined: 07-March 11

Find data on an Excel spreadsheet with VB

Posted 08 March 2011 - 08:40 PM

Hi all,

I am trying to manipulate an Excel spreadsheet. I want to search the A column for a string from a text box of vb6 then display it. I can't get it to work in my VB6 app. What am I doing wrong ?
Dim mailmwo As Excel.Application
Dim mwoBook As Excel.Workbook
mwonum = txtScan

Set mailmwo = New Excel.Application
mailmwo.Workbooks.Open "C:\mailmwo\mailmwo.xls", , , , "password", "password"
mailmwo.Sheets("MWOs - Do Not Sort!").Select
mailmwo.Range("A2").Select
mailmwo.Selection.Find(What:=mwonum), After:=ActiveCell, LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ ' <- line generated by Excel macro record



The error is :
'' gives runtime error '91' : Object variable or With block variable not set

Is This A Good Question/Topic? 0
  • +

Replies To: Find data on an Excel spreadsheet with VB

#2 Ionut  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 385
  • View blog
  • Posts: 1,057
  • Joined: 17-July 10

Re: Find data on an Excel spreadsheet with VB

Posted 09 March 2011 - 02:26 AM

Remove your brackets around What:=mwonum). In VBA(and I think it applies here) when you call a function, parameters should be enclosed between brackets. When calling a sub(procedure), you don't have to do this.
But, with this code, how can you see if you found something in that column?
Use this code:
Selection.Find(What:=value1, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
        True).Activate
'and then manipulate the value with ActiveCell


Was This Post Helpful? 0
  • +
  • -

#3 otomatis  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 9
  • Joined: 07-March 11

Re: Find data on an Excel spreadsheet with VB

Posted 09 March 2011 - 07:47 PM

Hi Ionut,

I still have runtime error '91' : Object variable or With block variable not set

This is my edit code :
dim value1
        value1 = txtScan.text

	Set mailmwo = New Excel.Application
	mailmwo.Workbooks.Open "C:\mailmwo\mailmwo.xls", , , , "password", "password"
	mailmwo.Sheets("MWOs - Do Not Sort!").Select
	mailmwo.Range("A2").Select
        mailmwo.Selection.Find(What:=value1, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
        True).Activate



This post has been edited by otomatis: 09 March 2011 - 07:49 PM

Was This Post Helpful? 0
  • +
  • -

#4 BobRodes  Icon User is offline

  • Your Friendly Local Curmudgeon
  • member icon

Reputation: 574
  • View blog
  • Posts: 2,989
  • Joined: 19-May 09

Re: Find data on an Excel spreadsheet with VB

Posted 13 March 2011 - 05:53 PM

Well, you have neglected to say which line of code causes your error, but I took a wild guess. Then, after looking here: http://msdn.microsof...fice.11%29.aspx it seems obvious to me that your syntax is wrong. Look at your .Activate and ask yourself how that can be resolved with the syntax you see in the documentation.

This post has been edited by BobRodes: 13 March 2011 - 05:54 PM

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1