i am doing a database system in access and has certain function of creating and modifying an excel worksheet. i keep getting this "Run-time error '462' : Remote server machine doesn't exist or is unavailable" or sometimes "Run-time error '1004' : Method 'Range' of object '_Global' failed"..
it points to this line
when i click on one of my command button
as well as
in a different command button.
here's the code for the function that is called when some1 clicks the command button
Dim checker(5) As String Dim insertion As Integer OpenExcel Set xlApp = CreateObject("Excel.Application") Set xlBook = xlApp.Workbooks.Open("C:\Osman\ABP-FET Reference.xls") insertion = 0 Do MsgBox (insertion) 'i added this for trial runs only, to check if it ' follows the correct sequence Set xlSht = xlBook.Worksheets("Wafer Sort") Set xlRng = xlSht.Cells((insertion * 4) + 7, 3) checker(1) = xlRng.Value Set xlRng = xlSht.Cells((insertion * 4) + 7, 4) checker(2) = xlRng.Value Set xlSht = xlBook.Worksheets("Assembly") Set xlRng = xlSht.Cells((insertion * 4) + 7, 3) checker(3) = xlRng.Value Set xlRng = xlSht.Cells((insertion * 4) + 7, 4) checker(4) = xlRng.Value insertion = insertion + 1 Loop Until checker(1) = "" And checker(2) = "" And checker(3) = "" And checker(4) = "" ' checking for empty cells insertion = ((insertion - 1) * 4) + 7 MsgBox (insertion) 'trial run purposes, to check on which row product 'will be added DC = InputBox("Please insert D/C", "Add") PN = InputBox("Please insert P/N", "Add") Set xlSht = xlBook.Worksheets("Wafer Sort") xlSht.Activate dummy = WaferSortAddProduct(insertion, DC, PN) Set xlSht = xlBook.Worksheets("Assembly") xlSht.Activate dummy = AssemblyAddProduct(insertion, DC, PN) xlApp.Application.UserControl = True xlApp.Application.Visible = True ActiveWorkbook.Close True xlApp.Application.Visible = False xlApp.Quit Set xlRng = Nothing Set xlSht = Nothing Set xlBook = Nothing Set xlApp = Nothing
OpenExcel function has the declarations for xlApp etc2..
the pointed error "Range(checker1).Select" exists in the functions "WaferSortAddProduct" and "AssemblyAddProduct", which i stored in a module, which btw, this function also exists in the same module... i use the range and select thing to modify the property of the cells, some merging, fonts, formula, borders and default values. i copy and modify it from excel recorded macros.
weird thing is, it works on the first run, then on the second run it gives the run time error, which then it works again on the third run and fails on the fourth run.. it goes on and on in that sequence..
also i can't seem to end the excel process.. i tried using all the codes that i could get my hands on but none of them actually ends the process.. after each try i have to manually end the process in task manager..
xlApp.Quit Set xlRng = Nothing Set xlSht = Nothing Set xlBook = Nothing Set xlApp = Nothing
i'm getting really frustrated over this part.. i'd really appreciate any form of help/feedback.. thanks in advance