Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming,microsoft.public.sqlserver.programming
external usenet poster
 
Posts: 2
Default Excel crashes when VBA window is closed

I am facing a quite strange problem. I have written a VBA code which
is called using Command Button. It opens the SQL database and
retrieves some numbers which are entered into excel cells.
Surprisingly, the code works if the VBA window is open. If it is
closed, the code returns empty cells. All the Subs and Functions are
public. The code is very simple with a straight forward SQL sentence.
Does anybody have any ideas where might be the problem? Many thanks.

Justina

  #2   Report Post  
Posted to microsoft.public.excel.programming,microsoft.public.sqlserver.programming
external usenet poster
 
Posts: 64
Default Excel crashes when VBA window is closed

Let us see the code, or the critical portion so we can see what is happening.
--
Pops Jackson


" wrote:

I am facing a quite strange problem. I have written a VBA code which
is called using Command Button. It opens the SQL database and
retrieves some numbers which are entered into excel cells.
Surprisingly, the code works if the VBA window is open. If it is
closed, the code returns empty cells. All the Subs and Functions are
public. The code is very simple with a straight forward SQL sentence.
Does anybody have any ideas where might be the problem? Many thanks.

Justina


  #3   Report Post  
Posted to microsoft.public.excel.programming,microsoft.public.sqlserver.programming
external usenet poster
 
Posts: 2
Default Excel crashes when VBA window is closed

I added 3 subs where I suspect the problem might be. The first sub is
called by command button which calls the GetFund_ID function. The
latter connects to the DB and should retrieve the IDs. I also added
the EnsureConnection Sub, though it does not seem to be a problem
because it is used in other codes.
The worst thing is that I cannot trace the bug because if i open the
VBA window, the code works.

-------------------------
Public Sub ImportButton_Click()
Dim i As Integer
Dim Error As Integer
Dim Result As Variant

On Error GoTo Handler:
Error = 0

'retrieve fund_id's from DB

For i = 1 To 50
If Sheet9.Cells(9, i + 1) = "" Then Exit For
Call GetFund_ID(Sheet9.Cells(9, i + 1), Result)
Sheet9.Cells(10, i + 1).Value = Result
Next i

<..importing..

'restore connection with DB. I am using 2 DBs, therefore i have to
restore connection with the default one. The RestoreConnection sub is
almost the same as EnsureConnection, just with different parameters.

RestoreConnection

MsgBox ("The import is finished")

If Error = 1 Then
MsgBox ("Connection with DBfailed. Please try again.")

End If

Exit Sub

Handler:
Error = 1
Resume Next

End Sub


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


Public Sub GetFund_ID(RefCol As String, Result As Variant)
Dim strSQL As String

If Application.VBE.MainWindow.Visible Then
Debug.Print "System Report:" & strSQL


strSQL = strSQL & "SELECT Fund_id FROM Funds WHERE Fund_Name like
'%"
strSQL = strSQL & RefCol
strSQL = strSQL & "%'"

EnsureConnection

Dim rs As Recordset: Set rs = OpenRecordsetCache(Cache, Cn, strSQL)
Result = ADODBExcel.FormatRecordset(rs)

End If

End Sub


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

Public Sub EnsureConnection()
Dim SQLString As String

SQLString = "Provider=SQLOLEDB.1;Password=Password1;"
SQLString = SQLString & "Persist Security Info=True;User
ID=User1;"
SQLString = SQLString & "Initial Catalog="
SQLString = SQLString & CStr([DBNAME])
SQLString = SQLString & ";Data Source=ServerName
\DatabaseName;"
SQLString = SQLString & "Use Procedure for Prepare=1;Auto
Translate=True;"
SQLString = SQLString & "Packet Size=4096;Workstation
ID=PCID;Use Encryption for Data=False;"
SQLString = SQLString & "Tag with column collation when
possible=False"

Set Cn = New ADODB.Connection
Cn.ConnectionString = SQLString

Cn.Open

SaveSetting "DBName", "Database", "ConnectionString",
Cn.ConnectionString
End Sub

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Differente .xls files generated when VBE window closed vs open Mark Excel Programming 3 June 26th 07 02:15 PM
how to get alert message when window is closed in excel for blank Anitha Excel Discussion (Misc queries) 0 November 30th 06 07:08 AM
Excel window closed but process still running Jessica[_5_] Excel Programming 2 June 23rd 06 02:44 AM
How do I undo the Excel, Window Menu, New Window command OLDFANG Excel Discussion (Misc queries) 2 March 17th 06 06:31 PM
Macro that waits, then resumes after window is closed [email protected] Excel Programming 3 January 16th 05 04:32 PM


All times are GMT +1. The time now is 11:07 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"