Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > BOOK: Access 2010 VBA Programmer's Reference
Password Reminder
Register
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
BOOK: Access 2010 VBA Programmer's Reference
This is the forum to discuss the Wrox book Access 2010 Programmer's Reference by Teresa Hennig, Rob Cooper, Geoffrey L. Griffith, Jerry Dennison; ISBN: 978-0-470-59166-6
Welcome to the p2p.wrox.com Forums.

You are currently viewing the BOOK: Access 2010 VBA Programmer's Reference section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old October 16th, 2013, 01:40 PM
Registered User
Points: 5, Level: 1
Points: 5, Level: 1 Points: 5, Level: 1 Points: 5, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2013
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default Problem with Closeing Excel after it is called with VBA in Access 2010.

I am not a programmer but I have been trying to learn how to do some automation using VBA in Access 2010. I have a database that I am using to track my IRA and have some code that use's excel to go out to Yahoo Finance to get stock info. This works but it will only run once, because it leaves the excel process open and I have to go into VBA editor and hit the reset button to get it to run again. I'll attach the Function I have written below. If anyone could tell me what I need to change to get excel to terminate properly I would appreciate the help.

Thank you

Code:
Function OpenCloseExcel(AstrSql)

    Dim Parameters As String
    Dim Symbol As String
    Dim qurl As String
    Dim MyPath As String
    Dim I As Integer
    Dim QuerySheet As Worksheet
    Dim ExcelWorkBook   As Workbook
  
   Dim ExcelApp As Object
   
   On Error GoTo ErrorTrap

   Set ExcelApp = CreateObject("Excel.Application")
   Set ExcelWorkBook = ExcelApp.Workbooks.Add
   
   'MsgBox "open"
'*************************************************************
    ExcelApp.Visible = False
    
    'Set newWkSheet = newWbk.Worksheets(1)
    Excel.Application.ScreenUpdating = False
    Excel.Application.DisplayAlerts = False
    Excel.Application.Calculation = xlCalculationManual
    
    I = 0
    qurl = "http://download.finance.yahoo.com/d/quotes.csv?s=" + AstrSql(I)
    I = I + 1
    While AstrSql(I) <> ""
        qurl = qurl + "+" + AstrSql(I)
        I = I + 1
    Wend
    
    Parameters = "snl1ohgrs7a2vx"
    
    'Parameters List
    '   s = symbol
    '   n = Name
    '   l1 = Last Trade (Price Only)
    '   o = Open
    '   h = days high
    '   g = days low
    '   r = P/E Ratio
    '   s7 = Short Ratio
    '   a2 = Average daily volume
    '   v = Volume
    '   x = stock exchange
    
    
    qurl = qurl + "&f=" + Parameters
    
    MyPath = "C:\Temp\" & "StockData" & ".Xlsx"
    Range("A1").CurrentRegion.ClearContents
    
     
QueryQuote:
            With Excel.ActiveSheet.QueryTables.Add(Connection:="URL;" & qurl, Destination:=ActiveSheet.Range("A2"))
                .BackgroundQuery = True
                .TablesOnlyFromHTML = False
                .Refresh BackgroundQuery:=False
                .SaveData = True
            End With
            
            Range("A2").CurrentRegion.TextToColumns Destination:=Range("A2"), DataType:=xlDelimited, _
            TextQualifier:=xlTextQualifierDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
            Semicolon:=False, Comma:=True, Space:=False, other:=False
            
            
            
            Range(Range("A2"), Range("A1").End(xlDown)).NumberFormat = "0.00"
            Range(Range("B2"), Range("B2").End(xlDown)).NumberFormat = "0.00"
            Range(Range("C2"), Range("C2").End(xlDown)).NumberFormat = "0.00"
            Range(Range("D2"), Range("D2").End(xlDown)).NumberFormat = "0.00"
            Range(Range("E2"), Range("E2").End(xlDown)).NumberFormat = "0.00"
            Range(Range("F2"), Range("F2").End(xlDown)).NumberFormat = "0.00"
            Range(Range("G2"), Range("G2").End(xlDown)).NumberFormat = "0.00"
            Range(Range("H2"), Range("H2").End(xlDown)).NumberFormat = "0.00"
            Range(Range("I2"), Range("I2").End(xlDown)).NumberFormat = "0,000.00"
            Range(Range("J2"), Range("J2").End(xlDown)).NumberFormat = "0,000.00"
            Range(Range("K2"), Range("K2").End(xlDown)).NumberFormat = "0.00"
            ActiveSheet.Range("A1").Value = "Symbol"
            ActiveSheet.Range("B1").Value = "Name"
            ActiveSheet.Range("C1").Value = "Last Trade price"
            ActiveSheet.Range("D1").Value = "Open"
            ActiveSheet.Range("E1").Value = "Days High"
            ActiveSheet.Range("F1").Value = "Days Low"
            ActiveSheet.Range("G1").Value = "P/E Ratio"
            ActiveSheet.Range("H1").Value = "Short Ratio"
            ActiveSheet.Range("I1").Value = "Average Daily Volume"
            ActiveSheet.Range("J1").Value = "Volume"
            ActiveSheet.Range("K1").Value = "Stock Exchange"
            
    Columns("A:A").EntireColumn.AutoFit
    Excel.Application.Calculation = xlCalcultaionAutomatic
    
    ExcelApp.Workbooks(1).SaveAs MyPath
       Excel.Application.DisplayAlerts = True

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "StockData", MyPath, True
  
'*************************************************************
   ExcelWorkBook.Close
   Set ExcelWorkBook = Nothing
   ExcelApp.Quit 'this line terminates the process
   Set ExcelApp = Nothing
   MsgBox "Done"
   Exit Function

ErrorTrap:
   Debug.Print "(" & Err.Number & ") " & Err.Description
End Function

Last edited by Rick48; October 16th, 2013 at 02:36 PM..
Reply With Quote
  #2 (permalink)  
Old March 25th, 2014, 04:09 PM
Registered User
Points: 9, Level: 1
Points: 9, Level: 1 Points: 9, Level: 1 Points: 9, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Nov 2012
Posts: 3
Thanks: 0
Thanked 1 Time in 1 Post
Default

Try the answers given here:

http://social.msdn.microsoft.com/For...-excel-problem

Basically, you may be doing something that leaves an object unclosed and when you set the ExcelApp to Nothing, Excel does not go away. Since you declared ExcelWorkBook also, you must set it to Nothing also.

Maybe try something like:

'DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "StockData", MyPath, True

'************************************************* ************
Debug.Print ExcelWorkBook.Name
If Not (ExcelWorkBook Is Nothing) Then
ExcelWorkBook.Close
Set ExcelWorkBook = Nothing
End If

If Not (ExcelApp Is Nothing) Then
ExcelApp.Quit 'this line terminates the process
Set ExcelApp = Nothing
End If
MsgBox "Done"
Exit function

ErrorTrap:
Debug.Print "(" & Err.Number & ") " & Err.Description
If Not (ExcelWorkBook Is Nothing) Then
ExcelWorkBook.Close
Set ExcelWorkBook = Nothing
End If

If Not (ExcelApp Is Nothing) Then
ExcelApp.Quit 'this line terminates the process
Set ExcelApp = Nothing
End If
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

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


Similar Threads
Thread Thread Starter Forum Replies Last Post
Debugging in VBA Excel 2010 i want to step through the program line by line to see curious2 Excel VBA 1 June 6th, 2013 08:20 AM
transfer excel data in a range to Access 2010 smaNavg Excel VBA 0 May 3rd, 2013 09:47 AM
2010 excel VBA changing links to external excel files andrewpage500 BOOK: Excel 2007 VBA Programmer's Reference ISBN: 978-0-470-04643-2 0 September 20th, 2012 08:42 PM
Pop-ups and arguments descriptions in VBA Function (Excel 2010) sektor Excel VBA 2 May 28th, 2010 04:52 AM
Code works in Excel VBA but not Access VBA fossx Access VBA 2 May 21st, 2007 08:00 AM



All times are GMT -4. The time now is 09:57 PM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.