VBA to VB6

[Madiya]

I have a working macro in VBA which I am now trying to re write in VB6 as an application.

I am getting error here.
With XLWBSR <<< This is my workbook
With .AutoFilter.Range <<< Getting Error

Error says Runtime error 438, Object doesn't supprot this property or method.

While it works perfectly in VBA, I thought it should work in VB6 also.

Pl help for any change required for vb6.

Regards,
Madiya

 

[Harald Staff]

Hi Madiya

VB6 does not recognize Excel things unless you set reference to the Excel
object library. This is done in the Project > References menu.

HTH. Best wishes Harald

 

[GS]

Adding to Herald's sage suggestion.., you should set your reference to
the earliest version of Excel that you expect users to be using. This
is not a rule but more of a 'best practice'. The VB runtime will, in
all likelyhood, reset the ref to whatever version of Excel is installed
on the user's machine.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[Madiya]

Adding to Herald's sage suggestion.., you should set your reference to the earliest version of Excel that you expect users to be using. This is not a rule but more of a 'best practice'. The VB runtime will, in all likelyhood, reset the ref to whatever version of Excel is installed on the user's machine. -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion

Thanks both of you.
I do have set referance to excel object.
I am opening my file via VB6 by using commondialog and filetitle property, and then i also created an arrey and removed all duplicates from the arrey using John's code.
Now I have filtered the list with 1st aarey element.
At this poing, I have to select only filtered cells.
I was trying to that by using autofilter, special cells visible only but getting error as above.

Is there any additional referances required?
Pl guide me as I am fairly biginer.


Regards,
Madiya

 

[GS]

It happens that Madiya formulated :

Thanks both of you.
I do have set referance to excel object.
I am opening my file via VB6 by using commondialog and filetitle
property, and then i also created an arrey and removed all duplicates
from the arrey using John's code. Now I have filtered the list with
1st aarey element. At this poing, I have to select only filtered
cells. I was trying to that by using autofilter, special cells
visible only but getting error as above.

Is there any additional referances required?
Pl guide me as I am fairly biginer.


Regards,
Madiya

There's a lot of automatic object refs in VBA that you need to
compensate for in VB6. For example, your line of VBA code...

With .AutoFilter.Range

...in VB6 is coded as...

With XLWBSR
With .AutoFilter.Range

...which implies that .AutoFilter.Range is a member of the Workbooks
object since XLWBSR refs a workbook. The .AutoFilter.Range object is a
member of the Worksheet object, and so the error raised!

What you need to do is identify all auto-refs in your VBA code and edit
to include fully-qualified refs. (The ObjectBrowser in the VBA IDE is a
good place to start)

Also use fully-qualified refs in your VB6 code.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[Madiya]

It happens that Madiya formulated :












There's a lot of automatic object refs in VBA that you need to

compensate for in VB6. For example, your line of VBA code...



With .AutoFilter.Range



..in VB6 is coded as...



With XLWBSR

With .AutoFilter.Range



..which implies that .AutoFilter.Range is a member of the Workbooks

object since XLWBSR refs a workbook. The .AutoFilter.Range object is a

member of the Worksheet object, and so the error raised!



What you need to do is identify all auto-refs in your VBA code and edit

to include fully-qualified refs. (The ObjectBrowser in the VBA IDE is a

good place to start)



Also use fully-qualified refs in your VB6 code.



--

Garry



Free usenet access at http://www.eternal-september.org

Classic VB Users Regroup!

comp.lang.basic.visual.misc

microsoft.public.vb.general.discussion

Hi GS,
Thats exactly what happened to me.
Its a perfact diagnoses.
As per your advise, I have changed all the ref to fully qualified ref and now it works exactly as intended except a small issue.

Thanks a lot.
Small issue is :
Here is my code in VB6 before end sub
XLAPP.DisplayAlerts = False
XLWBSR.Close savechanges:=True
WBEMAIL.Close savechanges:=False
XLAPP.DisplayAlerts = True
XLAPP.Quit

Changes to XLWBSR is saved and is closed.
Changes to WBEMAIL is not saved but while quitting excel application, I am prompted with a msgbox to save changes or not.

Any pointers pl.

Regards,
Madiya

 

[GS]

Hi GS,

Thats exactly what happened to me.
Its a perfact diagnoses.
As per your advise, I have changed all the ref to fully qualified ref
and now it works exactly as intended except a small issue.

Thanks a lot.

Glad you got it sorted!

Small issue is :
Here is my code in VB6 before end sub
XLAPP.DisplayAlerts = False
XLWBSR.Close savechanges:=True
WBEMAIL.Close savechanges:=False
XLAPP.DisplayAlerts = True
XLAPP.Quit

Changes to XLWBSR is saved and is closed.
Changes to WBEMAIL is not saved but while quitting excel application,
I am prompted with a msgbox to save changes or not.

Any pointers pl.

Is there any other unclosed files? Did your code make any changes to
Book1 (if there was one opened)? Did your code make any other changes
that weren't saved otherwise?

It could be, though, that Excel is still processing the close of
WBEMAIL when you Quit. Try switching the order the files are closed!

As an observation only...
Are you setting your object refs '= Nothing'?

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion