Visual Basic - AppActivate

Author

AppActivate

Vince

2005-12-15, 6:55 pm

I am using VB6 to transfer a recordset from my database into an excel file.
The data is transfered and saved sucessfully.

However, before closing excel, I would like to view the excel sheet. I
performed a similar operation in a function (in a module) which used a
recordset to modify an excel spreadsheet and then to view, simply called out:
AppActivate "Microsoft Excel"
It works fine.
However when I use the same line: AppActivate "Microsoft Excel" in my
PrivateSub, I get a "Run-time error '5': Invalid procedure call or argument"

Im assuming that I need to define something outside of the sub, or something
similar, but I am lost as to what and where something needs to be defined....

Any help would be greatly appreciated!
a shortened version of my code is below:

Private Sub HELP_Click()
Dim lngrow As Long
Dim dbs As DAO.Database
Dim xlx11 As Object, xlw11 As Object, xls11 As Object, xlc11 As Object
Dim rst11 As DAO.Recordset
Dim V1, S1 As String

Set xlx11 = CreateObject("Excel.Application")
Set dbs = CurrentDb()

Set xlw11 = xlx11.Workbooks.Open("C:\...\Desktop\BLANK.xls")

V1 = Forms![Form]![ID#]

Set rst11 = dbs.OpenRecordset("SELECT [Field#1],[Field#2],[Field#3] FROM
Table Where [Field#3] = '" & V1 & "';", dbOpenDynaset)
Set xls11 = xlw11.Worksheets("SHEET1")
Set xlc11 = xls11.Range("C6")
Do While rst11.EOF = False
rst11.MoveFirst
For lngrow = 0 To rst11.Fields.Count - 1
xlc11.Offset(lngrow, 0).Value = rst11.Fields(lngrow).Value
Next lngrow
rst11.MoveNext
Set xlc11 = xlc11.Offset(1, 0) 'testing
Loop

AppActivate "Microsoft Excel"

S1 = "C:\...\Desktop\" & V1
xlw11.SaveAs (S1)

Set xlw11 = Nothing
xlx.Quit
Set xlx1 = Nothing
Set rst11 = Nothing
dbs.Close
Set dbs = Nothing
Set xlc11 = Nothing
Set xls11 = Nothing

End Sub

Karl E. Peterson

2005-12-15, 6:55 pm

Vince wrote:
> I am using VB6 to transfer a recordset from my database into an excel
> file. The data is transfered and saved sucessfully.
>
> However, before closing excel, I would like to view the excel sheet.
> I performed a similar operation in a function (in a module) which
> used a recordset to modify an excel spreadsheet and then to view,
> simply called out: AppActivate "Microsoft Excel"
> It works fine.
> However when I use the same line: AppActivate "Microsoft Excel" in my
> PrivateSub, I get a "Run-time error '5': Invalid procedure call or
> argument"

The problem is, VB isn't finding a window with that *exact* title. My hunch
is there's a document name following the string you're looking for, right?

A long time ago, I wrote up some routines to find partial window titles.
That's what you'll need to use. You'd want to call the AppActivatePartial
function, looking for a window whose caption begins with "Microsoft Excel".

See: http://vb.mvps.org/samples/FindPart
--
Working without a .NET?
http://classicvb.org/

Ken Halter

2005-12-15, 6:55 pm

"Vince" <Vince@discussions.microsoft.com> wrote in message
news:6D9295C7-391C-4E83-8ED1-1DD84EC2C13C@microsoft.com...
>I am using VB6 to transfer a recordset from my database into an excel file.
> The data is transfered and saved sucessfully.
>
> However, before closing excel, I would like to view the excel sheet. I
> performed a similar operation in a function (in a module) which used a
> recordset to modify an excel spreadsheet and then to view, simply called
> out:
> AppActivate "Microsoft Excel"

Here's "another way" <g>
'=========
Private Sub Command1_Click()
Dim lngrow As Long
'Dim dbs As DAO.Database <--commented out for testing purposes
Dim xlx11 As Object, xlw11 As Object, xls11 As Object, xlc11 As Object
'Dim rst11 As DAO.Recordset <--commented out for testing purposes
Dim V1, S1 As String
'
Set xlx11 = CreateObject("Excel.Application")
'Set dbs = CurrentDb() <--commented out for testing purposes

Set xlw11 = xlx11.Workbooks.Open("C:\Temp\Test.xls")

'do your work....

'when ready to view the sheet,...
xlx11.Visible = True

'code to shutdown excel goes here
End Sub
'=========

--
Ken Halter - MS-MVP-VB - Please keep all discussions in the groups..
DLL Hell problems? Try ComGuard - http://www.vbsight.com/ComGuard.htm
Freeware 4 color Gradient Frame? http://www.vbsight.com/GradFrameCTL.htm

Vince

2005-12-15, 6:55 pm

Ken,
Thank you so much! i knew it was like one easy line!
not sure why I didnt try that.... durr... leave it to an engineer to
overcomplicate things he doesnt totally understand... ;-)

all of the DAO.objects worked fine when left in the code- didnt need to
comment out. just had to add the xlx11.visible and take out the xlx11.Quit
code in closing comments ;-)

thanks again!!!!
~Vince

"Ken Halter" wrote:

> "Vince" <Vince@discussions.microsoft.com> wrote in message
> news:6D9295C7-391C-4E83-8ED1-1DD84EC2C13C@microsoft.com...
>
> Here's "another way" <g>
> '=========
> Private Sub Command1_Click()
> Dim lngrow As Long
> 'Dim dbs As DAO.Database <--commented out for testing purposes
> Dim xlx11 As Object, xlw11 As Object, xls11 As Object, xlc11 As Object
> 'Dim rst11 As DAO.Recordset <--commented out for testing purposes
> Dim V1, S1 As String
> '
> Set xlx11 = CreateObject("Excel.Application")
> 'Set dbs = CurrentDb() <--commented out for testing purposes
>
> Set xlw11 = xlx11.Workbooks.Open("C:\Temp\Test.xls")
>
> 'do your work....
>
> 'when ready to view the sheet,...
> xlx11.Visible = True
>
> 'code to shutdown excel goes here
> End Sub
> '=========
>
> --
> Ken Halter - MS-MVP-VB - Please keep all discussions in the groups..
> DLL Hell problems? Try ComGuard - http://www.vbsight.com/ComGuard.htm
> Freeware 4 color Gradient Frame? http://www.vbsight.com/GradFrameCTL.htm
>
>
>

Ken Halter

2005-12-15, 6:55 pm

"Vince" <Vince@discussions.microsoft.com> wrote in message
news:36ACCA5E-3719-4A6A-AF92-BB182C60039D@microsoft.com...
> Ken,
> Thank you so much! i knew it was like one easy line!
> not sure why I didnt try that.... durr... leave it to an engineer to
> overcomplicate things he doesnt totally understand... ;-)

It's easy to go overboard <g>

> all of the DAO.objects worked fine when left in the code- didnt need to
> comment out. just had to add the xlx11.visible and take out the
> xlx11.Quit
> code in closing comments ;-)

Those commented lines were for testing here, without your DB (and setting
DAO references)... I knew I should've removed them before posting <g>

> thanks again!!!!
> ~Vince

--
Ken Halter - MS-MVP-VB - Please keep all discussions in the groups..
DLL Hell problems? Try ComGuard - http://www.vbsight.com/ComGuard.htm
Freeware 4 color Gradient Frame? http://www.vbsight.com/GradFrameCTL.htm

Sponsored Links