Export from Access to Excel Runtime Error 462

[Hemish]

Hi,

I have written some code which works kind of.

Basically i am exporting data from Access 2000 to Excel 2000 onto a template in excel then making a copy of the worsheet and saving the file. The code works 1st time but second time when i try opeing up excel it does not open up and when i try runnig my code again i get a debug message come up

Run-time error "462":

The remote server machine does not exist or is unavailable

What i have to do is close my database and end process excel under taks manager for it to work.

I think in the code its not closing the application and its in memory.

Below is the code

Thanks in advance

Option Compare Database
Option Explicit

Public xlApp As Excel.Application 'Variable to create a public instance of Excel
Public xlBook As Excel.Workbook 'Variable to create an Excel Workbook
Public xlSheet As Excel.Worksheet 'Variable to create an Excel Worsheet
Public Function fn_ExtractCustomerInfo() As Boolean
Dim db As Database
Dim rst As Recordset
Dim sOutPut As String
Dim sTemplate As String
Dim x As Integer
Dim strFormat As String
Dim intFormat As Integer
Dim sTitle As String
Dim sCustomer As String
Dim dStart As Date
Dim dEnd As Date
Dim sAccount As String

'="Unit Sales by Date Range for " & [Forms]![frmSalesUnitPrice]![cboAccount].Column(2) & " from " & [Forms]![frmSalesUnitPrice]![txtStart] & " and " & [Forms]![frmSalesUnitPrice]![txtEnd]

sTitle = "Unit Sales by Date Range for "
sCustomer = [Forms]![frmSalesUnitPrice]![cboAccount].Column(2)
dStart = [Forms]![frmSalesUnitPrice]![txtStart]
dEnd = [Forms]![frmSalesUnitPrice]![txtEnd]
sAccount = [Forms]![frmSalesUnitPrice]![cboAccount].Column(1)

sTitle = sTitle & sCustomer & " from " & dStart & " and " & dEnd

x = 1
On Error GoTo Error_Handler:


sOutPut = "C:\StockOfferLog\CustomerHistoryExportTemplate.xls"
'FileCopy sTemplate, sOutPut

Set xlApp = New Excel.Application 'Open excel Application
Set xlBook = xlApp.Workbooks.Add 'Open excel book with the application
Set xlBook = xlApp.Workbooks.Open(sOutPut)
Set xlSheet = xlBook.Worksheets("Sheet1")
xlSheet.Name = "Extract"
DoCmd.Hourglass True

Set db = CurrentDb 'Sets database object as the current database

Set rst = db.OpenRecordset("tblCustomerHistoryLastPriceExportTemp") 'opens query from where the data comes
xlSheet.Range("a1").Value = sTitle
xlSheet.Range("a2").Value = "Stock Code"
xlSheet.Range("b2").Value = "Stock Name"
xlSheet.Range("c2").Value = "FreeStock"
xlSheet.Range("d2").Value = "QTY Sold"
xlSheet.Range("e2").Value = "CurrencyCode"
xlSheet.Range("f2").Value = "Unit Price"
xlSheet.Range("g2").Value = "LastDate"


Do Until rst.EOF
' With rst

x = x + 1
' If Not rst.EOF Then 'If not end of file then execute the following

xlSheet.Cells((x + 1), 1).Value = x
xlSheet.Cells((x + 1), 1).Value = rst("Stock Code")
xlSheet.Cells((x + 1), 2).Value = rst("Stock Name")
xlSheet.Cells((x + 1), 3).Value = rst("FreeStock")
xlSheet.Cells((x + 1), 4).Value = rst("QTY Sold")
xlSheet.Cells((x + 1), 5).Value = rst("CurrencyCode")
xlSheet.Cells((x + 1), 6).Value = rst("Unit Price")
xlSheet.Cells((x + 1), 7).Value = rst("LastDate")


rst.MoveNext



Loop

' End With
rst.Close 'Close recordset once program loop has finished.
DoCmd.SetWarnings False

db.Close
Set rst = Nothing 'Destroy the recordset object to free up memory space.
Set db = Nothing
Call FormatExcelExport
xlBook.SaveAs "C:\StockOfferLog\" & sAccount & Format(Date, "dd_mm_yyyy") & ".xls" 'Save the Excel file

xlBook.Close 'Close the Excel file.
xlApp.Quit
Set xlApp = Nothing 'Destroy the Excel object created by the program to free up memory space.

Exit_Line:
DoCmd.SetWarnings True
DoCmd.Hourglass False
Exit Function

Error_Handler:
MsgBox Err.Number & ": " & Err.Description
Resume Exit_Line

End Function

 

[jal]

Sounds like a problem I had in VB.Net a while back. For some reason Windows wasn't releaasing the filelock after the first access to the file. A guy eventually gave me some VB.Net code that solved the problem (involving the Garbage Collector called here GC) - but that won't do you any good.

GC.Collect()
GC.WaitForPendingFinalizers()

But before he gave me that solution, I found a nonprofessional solution. I theorized that I could distract Windows from locking the file by issuing a keystroke to the keyboard using the SendKeys command. I can't recall the specifics, except that I popped up a blank form, issued a keystroke to the form, and then closed the form - to the user it looked like a quick flash and then it was gone, so it wasn't intrusive.

 

[Hemish]

But before he gave me that solution, I found a nonprofessional solution. I theorized that I could distract Windows from locking the file by issuing a keystroke to the keyboard using the SendKeys command. I can't recall the specifics, except that I popped up a blank form, issued a keystroke to the form, and then closed the form - to the user it looked like a quick flash and then it was gone, so it wasn't intrusive.

Hi thanks for your help. Just a quick one regarding using a keystroke to the keyboard using the SendKeys command, do you know how that works? Because I am not familiar with Keystroke and SendKey commands.

Thanks

 

[jal]

These are my notes from my VB.Net notebook. Much of it is probably the same for VBA. (You can probably do the sendkeys in the blank form's Activate event). I think you can generally - if it's just a letter or number - use quotes:

SendKeys "p"

There are two SendKey libraries
System.Windows.Forms.SendKeys (well, VBA.Sendkeys)
Windows Scripting Host SendKeys
The second one, WSH, is more likely to work on all operating systems.


- ' Send Ctrl S
SendKeys.SendWait("^S")
' Send Alt S
SendKeys.SendWait("%S")
'send the f10 key
SendKeys.SendWait("{f10}")
'send shift f10
SendKeys.SendWait("+{f10}") 'Plus sign means shift
'To specify repeating keys, use the form {key number}. You must put a space between key and number. For example, {LEFT 42} means press the LEFT ARROW key 42 times; {h 10} means press H 10 times.
The following can be used with sendkeys: (e.g. SendKeys.SendWait("{ESC}")
Key Code
Keypad add {ADD}
Keypad subtract {SUBTRACT}
Keypad multiply {MULTIPLY}
Keypad divide {DIVIDE}
BACKSPACE {BACKSPACE}, {BS}, or {B-KSP} (no hyphen)
BREAK {BREAK}
CAPS LOCK {CAPSLOCK}
DEL or DELETE {DELETE} or {DEL}
DOWN ARROW {DOWN}
END {END}
ENTER {ENTER}or ~
ESC {ESC}
HELP {HELP}
HOME {HOME}
INS or INSERT {INSERT} or {INS}
LEFT ARROW {LEFT}
NUM LOCK {NUMLOCK}
PAGE DOWN {PGDN}
PAGE UP {PGUP}
PRINT SCREEN {PRTSC} (reserved for future use)
RIGHT ARROW {RIGHT}
SCROLL LOCK {SCROLLLOCK}
TAB {TAB}
UP ARROW {UP}
F1 {F1}
F2 {F2}
F3 {F3}
F4 {F4}
F5 {F5}
F6 {F6}
F7 {F7}
F8 {F8}
F9 {F9}
F10 {F10}
F11 {F11}
F12 {F12}
F13 {F13}
F14 {F14}
F15 {F15}
F16 {F16}

 

[jal]

By the way, if you want to do a pretest of my solution, just pop up a msgbox. This will serve as a pseudo-form which lets you know if my suggestion might be able to solve the problem.

 

[Kavitha]

Hi All,

Can you please tell me how to solve the runtime error 462.I am new to access.Please tell me how to open the blank form and send keys to it in little more descriptive way.

Thanks in advance

 

[jal]

My first suggestion was probably wrong (typically is) and, as I said, you could test it by popping up a regular MsgBox after doing the Save. You apparently didn't test it.

I suspect the problem lies in a sub you don't seem to have displayed:

Call FormatExcelExport


Care to share that code?

 

[Kavitha]

Hi Jal,

Thanks for your immediate reply.Finally,I am able to solve that problem.I have taken a temporary selection variable and done modifications on that temp variable.That solves me the problem.

Thanks anyway