Closed. This question is off-topic . It is not currently accepting answers.
I experienced this same issue today and resolved it by closing all workbooks and relaunching Excel.
Using Excel 2016 on Office 365.
Dim curWS As Worksheet
Dim usrWB As Workbook
Dim usrWS As Worksheet
Set curWS = ActiveWorkbook.ActiveSheet
Set usrWB = Workbooks.Open("C:\Temp\user.xlsx")
Set usrWS = usrWB.Sheets("Users")
Was receiving a Run-Time error '9': Subscript out of range at the set usrWS line.
I found that usrWB was not referencing the user.xlsx file but instead was referencing the workbook from the curWS line which did not have a worksheet named "Users" explaining the RTE. I found this question and tried restarting Excel first before trying a reboot and the problem went away.
Workbooks.Open method (Excel), I experienced this same issue today and resolved it by closing all workbooks and relaunching Excel. Using Excel 2016 on Office 365. Dim curWS As Worksheet Workbooks.Open "ANALYSIS.XLS" ActiveWorkbook.RunAutoMacros xlAutoOpen The following code example imports a sheet from another workbook onto a new sheet in the current workbook. Sheet1 in the current workbook must contain the path name of the workbook to import in cell D3, the file name in cell D4, and the worksheet name in cell D5.
I am seeing this with an old application written in VB6 that has been working for years, and users who are upgrading to Excel 2016 are starting to report problems. In my case it seems to be happening pretty consistently (seems to return a reference to one of the .xla addins). As the program has multiple Excel files open, and the user may also have other files open, closing and re-opening Excel every time it opens a new workbook is not a practical solution.
Until Microsoft acknowledge this as a fault in Excel, or someone can figure out what conditions cause the problem to occur, I would definitely recommend that you ignore the return value from the Workbooks.Open method, and instead use the filename to get the Workbook object reference from the Workbooks collection afterwards.
Dim wbkHelper as Workbook
Set wbkHelper = Workbooks.Open(filePath, ReadOnly:=true)
Set wbkHelper = Workbooks(filename) ' note filename needs to be filepath without the path
Using Workbook Object in Excel VBA (Open, Close, Save, Set), Open. Dim oWorkbook As Excel.Workbook oWorkbook = Workbooks.Open (FileName:="", _ UpdateLink:=2, _ ReadOnly:=False, _ True is Excel language, false is VBA language. In the (Microsoft Excel Objects > ThisWorkbook) folder, select Workbook in the This function returns true if the workbook is currently open Question: Tag: excel,vba,excel-vba,copy,paste I currently have this code which will take files from a folder, open each one, print its name into the first column of my "Master file" close it and loop through the entire folder that way.
Excel Workbooks - Opening, I have a piece of code that has been working for quite a while, now I am getting an error "Object variable not set" When I step through the code, Tag: performance,vba,excel-vba I have two questions regarding VBA efficiency. My VBA code grabs a cell value from one sheet, places it in another excel workbook which then performs it's own calculations and returns a result, and I copy that result back into the original workbook.A simplified version of my code is:
set workbooks.open() not working - Excel General, If you want to use VBA to Open a Workbook then check out Open Workbook When we have the object we can use it to perform tasks with the workbook. If you cannot resolve the error then use either of the functions in the section Finding all open Workbooks. The function returns the full file name if a file was selected. Represents a Microsoft Excel workbook. Remarks. The Workbook object is a member of the Workbooks collection. The Workbooks collection contains all the Workbook objects currently open in Microsoft Excel. The ThisWorkbook property of the Application object returns the workbook where the Visual Basic code is running. In most cases, this is the same as the active workbook.
The Complete Guide To The VBA Workbook, I can't reference excelwrk as excelapp.workbooks.open("c:\test.xls") When I run application, there is no error. I am using Excel 8.0 object library. Only when I exit my VB project and return can the excel file be recalled. 10 ways to reference Excel workbooks and sheets using VBA by Susan Harkins in 10 Things , in After Hours on August 25, 2009, 2:08 AM PST
Excel Application.workbooks.open(filename) doesn't return Excel , This Excel VBA Open Workbook Tutorial is accompanied by an Excel workbook this approach is tedious and error-prone because the user must type the filename (with no Returns the full path/name/extension of the file chosen by the user. macro there's only one item in the Files of type drop-down list box (Excel Files):. I'm writing a function to load events (for a calendar) which should return data to be parsed by Mustache. The problem i got is that I can't find a way to return processed data inside request.done() Here's my function
Comments Does filePath contain just the path or does it also include the file name as well (including the correct extension, such as .xlsm or .xlsx)? I've never seen that behavior - I would have to guess there's something else going on. I have seen that behaviour before - I think it was if the Open fails but there is an On Error Resume Next in play, or it might have been in the case when the file was already open when the Open statement was executed. Can't replicate the issue at the moment though :( Can you break it down further? What happens if your macro is just those two lines, along with a debug.print statement? Does it still break? That's been my experience as well (ie, that restarting Excel fixes it). Must just be some sort of memory/corruption "thing" within Excel. I wish I knew what caused it in the first place. It seems to be a relatively uncommon issue, but causes some major headaches until you figure out why it's happening.
