	[x] Posted via EE Mobile
	Search, ask, and monitor your questions on the go with EE Mobile. Visit Experts Exchange from your mobile device and never be out of touch again.

11/06/2009 at 03:23AM PST, ID: 24877515

	[x] Attachment Details

[x]

The Solution Rating System

With so many solutions, how can you tell which solutions are most likely to help you and which ones are not? To provide you with a tool to use, we rate our solutions based on various elements that most accurately determine if a solution is a quality solution. To explain what factors affect the solution rating, here are the elements we take into consideration when formulating our solution rating.

The Grade of the Solution
The Zone Rank of the Expert Providing the Solution
The Number of Author and Expert Comments
The Number of Experts Contributing
The Feedback of the Community

Your Input Matters
Because of the way the system is set up, the most important variable in this equation is you. As a member of Experts Exchange, you are able to cast your vote on the quality of the solutions in regard to how complete, accurate, helpful and easy to understand each solution is. When you provide your feedback, each rating is adjusted accordingly. So, if you see a solution that has a poor rating that you think is a good solution, let us know by rating it. As you do, the rating will be adjusted and will become more accurate for other members of our site.

If you have any suggestions that you would like to make for our rating system, please ask a question in the Suggestions Zone of Community Support.

Thank you!

9.0

How can VBA properly close down an Excel session it has started?

Asked by colinasad in Microsoft Excel Spreadsheet Software, Access Coding/Macros

Tags: Excel, VBA

I am developing an Access 2007 "project" (.adp) as a front-end to a SQL Server 2005 Express database.

One task my client has is to import daily data from an old DOS application running in various retail outlets and store this data in the Access / SQL Server database at HQ.

The data from the shops arrive as ".TXT" files and I import these into a "temporary" table, from which the data can be validated and printed, before updating the "master" tables via a Stored Procedure. I am attempting to do this "behind the scenes" using VBA code so that the user merely specifies a date, then my VBA code does the necessary work and eventually stores it in a SQL Server Table.

Problems with a "DoCmd.TransferText acImportDelim" strategy caused me to move to using "Workbooks.OpenText" to read the ".TXT" data into an Excel spreadsheet then into my SQL tables. I have attached the VBA Sub Procedure I am using to attempt this.

This works OK for the first import file in a session, but I get a run-time error when I try to repeat the process for a second file.
Error Number : 91
Desc : Object variable or With block variable not set

at the lines :
Set xlsBook = ActiveWorkbook
Set xlsSheet = xlsBook.Worksheets(1)

I think it is the second line that is actually crashing because a tracer message I placed between the 2 lines is being displayed OK.

I have made various attempts at "closing" the Excel session in the "Clean_Up" section of my procedure. The current sequence shown in my code snippet gets me caused in a "loop".
because the "xlsBook.Close" line in my "Clean_Up" section produces the same run-time error message, which takes me to my error handler, which returns me to the "Clean_Up" section ......

Can anyone tell me how I should be properly "cleaning up" my hidden Excel session, so that the whole process can be repeated without any run-time errors?

Many thanks.

View the Solution FREE for 30 Days

         
           
             1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:
67:
68:
69:
70:
71:
72:
73:
74:
75:
76:
77:
78:
79:
80:
81:
82:
83:
84:
85:
86:
87:
88:
89:
90:
91:
92:
93:
94:
95:
96:
97:
98:
99:
100:
101:
102:
103:
104:
105:

           
           
             Public Sub ImportShopData(strRawDataFile As String)
' ****************************************************************************
' A procedure for importing daily sales data from retail shops
'
' strRawDataFile - full path and file name of raw data
'
' Will open ".TXT" file into an Excel spreadsheet, 
' then read rows from the spreadsheet and insert them into a SQL Server Table via a Stored Procedure
' ****************************************************************************
 
 
On Error GoTo HandleError
 
 
    Dim xlsApp As Excel.Application
    Dim xlsBook As Excel.Workbook
    Dim xlsSheet As Excel.Worksheet
    
    'create new excel instance
    Set xlsApp = New Excel.Application
 
    'Don't open the excel application and don't show warnings
    xlsApp.Visible = False
    xlsApp.DisplayAlerts = False
    
    ' Try to open the text file as a "hidden" Excel spreadsheet
    ' File colmns will be comma-separated and 4th column contain stock codes, possibly with leading zeroes
    
    xlsApp.Workbooks.OpenText strRawDataFile, xlMSDOS, 1, xlDelimited, 1, False, False, False, True, False, False, , Array(Array(4, 2))
    Set xlsBook = ActiveWorkbook
    Set xlsSheet = xlsBook.Worksheets(1)
    
    ' Here are the variables for copying data from Excel worksheet into SQL Server Table
    
    Dim blnDataExists As Boolean
    Dim intRowIndex, intColIndex As Integer
    Dim strShopCode As String
    Dim datSalesDate As Date
    Dim strStockCode As String
    Dim intSalesQnty As Integer
    Dim curSalesValue As Currency
    Dim strSQLString As String
            
    blnDataExists = True
 
    'Row 1 has a header so start reading data from spreadsheet row 2
    intRowIndex = 2
    intColIndex = 1
    
    Do While blnDataExists = True
        
        ' There should be data in every column of every row
        If Trim(xlsSheet.Cells(intRowIndex, 1)) <> "" Then
            With xlsSheet
                ' Collect data from current row into my variables
		' Column 1 is a line counter - we don't need it
 
                strShopCode = Nz(.Cells(intRowIndex, 2), "")
                datSalesDate = Nz(.Cells(intRowIndex, 3), Date)
                strStockCode = Nz(.Cells(intRowIndex, 4), "")
                intSalesQnty = Nz(.Cells(intRowIndex, 5), 0)
                curSalesValue = Nz(.Cells(intRowIndex, 6), 0)
               
                If Not (genAllBlanks(strShopCode) Or genAllBlanks(strStockCode) Or _
                        (intSalesQnty = 0) Or (curSalesValue = 0)) Then
                
                    ' Prepare a parameter list to pass these variables into a Stored Procedure
                    ReDim mySPParamList(5)
                    Call FillSPParameter(mySPParamList(0), "@paramShopCode", adVarChar, adParamInput, strShopCode)
                    Call FillSPParameter(mySPParamList(1), "@paramSalesDate", adDate, adParamInput, datSalesDate)
                    Call FillSPParameter(mySPParamList(2), "@paramStockCode", adVarChar, adParamInput, strStockCode)
                    Call FillSPParameter(mySPParamList(3), "@paramSalesQnty", adInteger, adParamInput, intSalesQnty)
                    Call FillSPParameter(mySPParamList(4), "@paramSalesValue", adCurrency, adParamInput, curSalesValue)
                            
                    ' Execute the Stored Procedure to INSERT data into the SQL Server Table
                    Call Execute_SP_Params_NoRS("USP_InsertIntoTempShopDaySales", mySPParamList)
                    
                    
                End If
            End With
    
            'Move on to the next row
            intRowIndex = intRowIndex + 1
        Else
            blnDataExists = False
        End If
    Loop
    
    MsgBox ("Import Process Complete")
    
Clean_Up:
    ' Close Workbook and Quit Excel
    xlsBook.Close
    xlsApp.Quit
    Set xlsSheet = Nothing
    Set xlsBook = Nothing
    Set xlsApp = Nothing
 
    Exit Sub
    
HandleError:
    genErrorHandler Err.Number, Err.DESCRIPTION, "DB_LOGIC", "ImportShopData"
    
    Resume Clean_Up
End Sub ' ImportShopData

           
         

20091111-EE-VQP-91 - Hierarchy / EE_QW_3_20080625

1. rorya2,439,610
2. saurabh7262,408,891
3. brettdj1,553,794
4. barryhou...1,277,089
5. chris_bot...897,558
6. matthews...882,565
7. patrickab712,241
8. nutsch528,037
9. zorvek466,027
10. WarCrimes382,121
11. byundt377,266
12. RichardS...359,304
13. pari123346,965
14. jppinto274,933
15. webtubbs262,020
16. ProdOps231,615
17. Cbrine213,117
18. ahammar210,725
19. folderol203,400
20. spattewar200,958
21. riteshparakh193,444
22. bhwithun189,580
23. JONvdHe...169,379
24. tbsgadi154,818
25. epaclm137,431

1. zorvek10,679,647
2. rorya8,512,802
3. byundt8,211,782
4. saurabh7265,030,848
5. matthews...3,987,171
6. brettdj3,267,235
7. patrickab3,116,913
8. jeverist2,907,207
9. chris_bot...2,042,236
10. roos011,928,098
11. webtubbs1,854,710
12. mvidas1,537,253
13. barryhou...1,511,659
14. nutsch1,259,140
15. fanpages1,236,922
16. MalicUK1,060,396
17. ahammar916,414
18. spattewar748,603
19. mgh_mgh...649,110
20. LoNeRaV...602,049
21. ture572,551
22. pari123546,873
23. epaclm495,895
24. jpaulino485,973
25. gtgloner477,227

How can VBA properly close down an Excel session it has started?

Asked by colinasad in Microsoft Excel Spreadsheet Software, Access Coding/Macros

Tags: Excel, VBA

About this solution