Ask Question Forum:
Model Library:2025-02-08 Updated:A.I. model is online for auto reply question page
C
O
M
P
U
T
E
R
2
8
Show
#
ASK
RECENT
←
- Underline
- Bold
- Italic
- Indent
- Step
- Bullet
- Quote
- Cut
- Copy
- Paste
- Table
- Spelling
- Find & Replace
- Undo
- Redo
- Link
- Attach
- Clear
- Code
Below area will not be traslated by Google,you can input code or other languages
Hint:If find spelling error, You need to correct it,1 by 1 or ignore it (code area won't be checked).
X-position of the mouse cursor
Y-position of the mouse cursor
Y-position of the mouse cursor
Testcursor
caretPos
Attachment:===
Asked by Fritz Paul
at 2024-07-24 08:33:45
ModifyPoint:0 Replies:6 POST_ID:829089USER_ID:11990
Topic:
Microsoft Access Database;;Microsoft Excel Spreadsheet Software
I have the code below to open an Excel spreadsheet from a form in Access. I use Access 2010 and Excel 2010.
Some times it works, say 1 in 5 tries. Other times it gives different error messages. The line where it breakes is indicated such.
Some times it works, say 1 in 5 tries. Other times it gives different error messages. The line where it breakes is indicated such.
'>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>CODE FAILS ON NEXT LINE 1:
The file that is supposed to open is named
Agreement Terms 2014 Q1.xls
and as I say sometimes it opens.The Locals Window just before the error occurs is shown at the bottom.
Private Sub OpenExcelWorkbook(WorkbookToRead As String)' Open Excel WorkbookDim excelApp As Excel.ApplicationDim wb As Excel.WorkbookDim Flag As String' Set wb = Nothing' Set excelApp = NothingOn Error Resume NextSet excelApp = GetObject(, "Excel.Application") 'http://support.microsoft.com/kb/288902 - If Excel is not open, an error occurs and Err.Number >" 0 Flag = 1If Err.Number <">" 0 Then 'There is an error if Err.Number <">" 0 Set excelApp = CreateObject("Excel.Application") 'Create Excel.ApplicationEnd IfOn Error GoTo Err_Handler Flag = 2' WorkbookToRead = Me.txtOrigATD 'A textbox on my form With excelApp .Visible = True 'Excel becomes visible. Confirmed.'>">">">">">">">">">">">">">">">">">">">">">">">">">">">">">">">">">">">">">">">">">">">">">">">">">">">">">">">">">">">">">">">">">">"CODE FAILS ON NEXT LINE Set wb = .Workbooks.Open(WorkbookToRead, ReadOnly:=True) End With ' wb.ActiveSheet.Range("d1").Value = 100 'Passes a value to a cell on the spreadsheet. Flag = 3 Set wb = Nothing Set excelApp = NothingExit_Sub: Exit SubErr_Handler: Set wb = Nothing Set excelApp = Nothing MsgBox Err.Description MsgBox "An error occurred while in Private Sub OpenExcelWorkbook(), called from cmdOpenExcelWorkbook_Click() | Flag = " &" Flag Me.SetFocus Resume Exit_SubEnd Sub 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:
Author: Fritz Paul replied at 2024-07-29 02:15:49
I have tested this code now over and over and it does not break
I sincerely appreciate the suggestions that I received, but cannot accept anyone as a solution.
I sincerely appreciate the suggestions that I received, but cannot accept anyone as a solution.
Accepted Solution
Author: Fritz Paul replied at 2024-07-24 11:17:20
Thanks,
I found it worked once and the second time a whole new error came up: "Unable to get the Open property of the Workbooks class" and then thereafter all the regular errors, depending whether I first close Excel etc.
I really appreciate your help. I have now found similar code in one of my apps and it works every time except when the workbook is already open. I will just have to find code to check whether the workbook is already open and then return a message and terminate. The only difference is that all the code is in the On Click event and is not in a called subroutine. I can't think why.
As below
"
I found it worked once and the second time a whole new error came up: "Unable to get the Open property of the Workbooks class" and then thereafter all the regular errors, depending whether I first close Excel etc.
I really appreciate your help. I have now found similar code in one of my apps and it works every time except when the workbook is already open. I will just have to find code to check whether the workbook is already open and then return a message and terminate. The only difference is that all the code is in the On Click event and is not in a called subroutine. I can't think why.
As below
"
Private Sub cmdOrigATD_Click()' Browse for single filesDim sPath As StringDim sFile As StringDim WorkbookToRead As StringDim excelApp As Excel.ApplicationDim wb As Excel.WorkbookDim Flag As String priBrowseForFile sPath, sFile ' Refers to Public Function priBrowseForFile_Click(sPath As String, sFile As String) ' http://stackoverflow.com/questions/14915179/ms-access-browse-for-file-and-get-file-name-and-path Me.txtOrigATD = sPath & sFile WorkbookToRead = Me.txtOrigATDOn Error Resume NextSet excelApp = GetObject(, "Excel.Application") 'http://support.microsoft.com/kb/288902 - If Excel is not open, an error occurs and Err.Number > 0 Flag = 1If Err.Number <> 0 Then Set excelApp = CreateObject("Excel.Application") 'There is an error if Err.Number <> 0 Create Excel.ApplicationOn Error GoTo Err_Handler Flag = 2 With excelApp .Visible = True 'Excel becomes visible. Confirmed. Set wb = .Workbooks.Open(WorkbookToRead, ReadOnly:=True) End With Flag = 3 Set wb = Nothing Set excelApp = NothingExit_Sub: Exit SubErr_Handler: Set wb = Nothing Set excelApp = Nothing MsgBox Err.Description MsgBox "An error occurred while in Private Sub OpenExcelWorkbook(), called from cmdOpenExcelWorkbook_Click() | Flag = " & Flag Me.SetFocus Resume Exit_SubEnd Sub 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:
Expert: Jerry_Justice replied at 2024-07-24 10:12:09
Dim excelApp As Object
Dim wb As Object
You need to declare these as Object rather than to the excel.application
When you create an object with "late binding":
Set excelApp = GetObject(, "Excel.Application")
You always Dim the variable as "Object"
Change those two lines and it works fine.
Dim wb As Object
You need to declare these as Object rather than to the excel.application
When you create an object with "late binding":
Set excelApp = GetObject(, "Excel.Application")
You always Dim the variable as "Object"
Change those two lines and it works fine.
Author: Fritz Paul replied at 2024-07-24 09:13:47
Hi Rey,
I get the same problem except that no, when the workbook opens, it closes immediately again.
It seems I will have to prepare a small database and submit it or consult someone directly.
Thanks for your help.
by the way errors that I get are
Method 'Open' of object 'Worksheets' failed
or
Automation error
The remote procedure call failed.
or
Excel cannot complete this task with available resources. Choose less data or close other applications.
I get the same problem except that no, when the workbook opens, it closes immediately again.
It seems I will have to prepare a small database and submit it or consult someone directly.
Thanks for your help.
by the way errors that I get are
Method 'Open' of object 'Worksheets' failed
or
Automation error
The remote procedure call failed.
or
Excel cannot complete this task with available resources. Choose less data or close other applications.
Expert: Rey Obrero (Microsoft Access MVP) replied at 2024-07-24 08:50:51
try changing this two lines
Set wb = Nothing
Set excelApp = Nothing
with
excelApp.Quit
before you run the codes,
close Access application
close all Excel application
open access application..
Set wb = Nothing
Set excelApp = Nothing
with
excelApp.Quit
before you run the codes,
close Access application
close all Excel application
open access application..
Expert: duncanb7 replied at 2024-07-24 08:40:15
Could you send us the whole file for understanding your question easier ?
Duncan
Duncan
