You are automating Excel from Access. r/vba: A place for questions and discussion on Visual Basic for Applications (VBA) and its associated integrated development environment (IDE). Hi, Thanks for your reply.

Register here so that you can post in the forums or comment on the articles. Worksheets(intSheetIndex - 1) does not refer directly or indirectly to the Excel.Application object that you created, so it causes problems. The above code says to select the cells A1 to A5 in the worksheet “Sheet1”. Option Explicit; Sub ExtractLocations() Dim ws1 As Worksheet; Dim wsNew As Worksheet; Dim Rng As Range; Dim r As Integer; Dim c As Range; Set ws1 = Sheets("Sheet1")

Code: Sub Sample1() End Sub Press J to jump to the feed. AccessのVBAからExcelを開いて指定行を固定する操作をしたいです。 Dim ExcelApp As Object Dim ExcelBook As Object Set ExcelApp = CreateObject("excel.application") ExcelApp.Visible = True Set ExcelBook = ExcelApp.Workbooks.Open(StrFile) ExcelBook.Worksheets("Sheet1").Activate range("C2").Select ActiveWindow.FreezePanes = True ExcelBook.Save ExcelBook.Close … Visual Basic has established a reference to Excel because of a line of code that calls an Excel object, method, or property without qualifying the element with an Excel object variable. I think the issue belongs to this line: Set rngDestin = Sheets("Prepared_Expense").Range("R6") Because you have set the range to include the sheet, you do not use the sheet to use the range Press question mark to learn the rest of the keyboard shortcuts Joined Feb 7, 2012 Messages 11,794 Office Version 2013 Platform Windows To experiment my present active sheet is “Sheet2”, not “Sheet1”.

Be sure to check out the FAQ by clicking the link above. If this is your first visit, then welcome! Declare a sub-function to start writing the code. Re: Method 'Range' of object_global failed Mr. hgchas, It would help if you would post the worksheet you are trying to use this macro on so we can see what values are in column A, though ensure that all private information is replaced. If you enable both Add-ins and Protected View, what's the result of debugging? Base on your description, we would suggest to modify the VBA code. The following code is in the This WorkBook VBA Module: Option Explicit Private Sub Workbook_Open() 'ONE PROBLEM LINE OF CODE IN THIS PROCEDURE - SEE BELOW.

Go to the Developer tab click on Visual Basic to Open VB Editor.

Resolved: Method ‘Sheets’ of object ‘_Global’ failed September 27, 2016 Category: MS Excel Tags: excel , vba We have a complex macro enabled spreadsheet.