Can Excel reference a closed workbook?
Sometimes Excel will open a workbook and then have the option to reference another copy of that same workbook.
It could be that you closed it, or opened it but didn't save it, etc. Is there any way to tell if this is happening (if so, what would be the command to stop it)?
I would like to avoid opening a workbook in a new window just to close it if Excel won't let me reference it. Thanks. Open File->Open Recent. This will list recent workbooks for you to reopen. I doubt they will appear in this list if Excel couldn't access them properly.exe, run it with your favourite text editor and go through the following directory and find the offending workbook to kill it. It might help you to save the file when doing so.
How do I pull data from a closed Excel file VBA?
I have a very large database that is generated from an existing closed Excel file.
The user can only input certain ranges in the main worksheet and the generated data is output to different sheets. There are lots of sheets so I am using a separate Workbook to generate this output.
How can I pull the data from each cell from the closed Excel file into my new VBA Worksheet? This means it's not possible to add the data manually because all the data is already there. This is the first time I've had to use macros so any help would be greatly appreciated! A simple way would be to read each row of the workbook. The VB Code below will open the excel file, read it and insert the data into your destination worksheet (based on the user settings). Then save. You may want to run it under debug mode to double check if everything went OK. For further explanation read the code comments.
Sub ImportDataFromExcel(). Application.Workbooks.Open ("C:UsersTestPCDesktopExcelFile.xlsx")
' Read file. Dim DataRange As Range. Set DataRange = ThisWorkbook.Sheets(1).Range("B10:V" & 10 & "").Cells(1, 1)
' Create destination sheet with the same name of the file. ' you want to read and insert the content. With Sheets("DestinationSheet"). .Cells(1, 1) = .Name
.Cells(1, 2) = DataRange.Address
.Cells(1, 3) = "Data source" .Range("A1") = .Range("A1") ' To avoid error, copy the first row
.Range("B1") = .Range("B1") ' Copy the title
End With. ' Save the file. Application.ActiveWorkbook.Save
ThisWorkbook.Close
Related Answers
How can I open a PDF file in Excel for free?
How to Convert PDF to Excel for Free. Convert PDF to Exce...
How do I create a query in Excel Online?
In Excel Online, you can create queries using the new Excel Query...
What does enable content mean Excel?
I'm working with a colleague that is an excellent Excel user and also a v...