Importing A Range Of Data From A Closed Workbook
Sep 9, 2009
Due to some cutbacks the company I work for has taken away the database system we used to use to record debts owed to us, so I'm building one in excel (no access or sql otherwise I'd use those). We have all the debt in seperate workbooks by financial period and I'm creating a new workbook that will upon entering of a specific financial period will pull information from the pertaining workbook and report on the information.
I have a Formula
=INDIRECT.EXT(CONCATENATE("'C:Documents and Settingshughel13My Documents[",Reports!$C$11,".xls]Sheet1'!R2:R138"))
Among others which pull other ranges
Which as I understand should work just fine on a closed workbook, however it only works when the target workbook is open.
When the workbook is closed the formula only pulls the first value in the range.
On a related note the above formula and it's counterparts are autofilled down the page from 2 to 1000 as some of the workbooks have 1000 records to pull, however the ones that dont have that many, cause errors when the data is pulled, is there a way to make it so that if the cell it's pulling from is empty it doesn't pull from it or pulls a value of 0?
View 14 Replies
ADVERTISEMENT
Dec 10, 2004
I found this code on the net, and it works, it DOES import the sheet specified from a closed status, but it adds 0 where there were blank cells.
The worksheets are static, the names will not change, but the information on them varies in # rows but the # of columns depends on the page..
The use of this import is to upgrade the program, by importing the sheets from the old version.
The first part browses for the file, which is good, because it could be stored anywhere and named anything.. I have it set up to "Click to Import"....
Private Sub CommandButton1_Click()
GetValuesFromAClosedWorkbook "C:", "Book1.xls", _
"Personnel", "A:H"
End Sub
This next part does the importing, but it fills all the columns and rows that were blank with 0's and fills all the way down to 65536 thru my column H that I specified that had data above.
Sub GetValuesFromAClosedWorkbook(fPath As String, _
FName As String, sName, cellRange As String)
how this code can be modifed to do the following..
Detect and copy ONLY the used range of the closed workbook.
View 9 Replies
View Related
Apr 26, 2008
Is there code that will take certain data from one Excel sheet to another named file in a different place on the network? Example Copy cell aa47 from "Recent Faxes.xls" that sits in "correspondence" folder. Then paste into cell B25 "Current Documentation.xls" in the "Sales Contacts" folder
View 2 Replies
View Related
May 24, 2006
I have created a workbook that imports data from another workbook which is used frequently on a network drive. After I import the data to my new workbook, it locks the original workbook for editing. Is there a property that will allow me to disable this 'locked for editing' read only mode or any other way to get around this?
View 6 Replies
View Related
Feb 27, 2014
I've been using the following code to bring in individual cell values from one closed workbook to an active one. I would like to modify this is possible to bring in multiple cells at once and also pull them into a different worksheet in the active workbook. Basically, my command button is on Sheet1 but I'd like the data to pull into a cell on Sheet2.
Private Sub CommandButton1_Click()
With Range("Q9")
.Formula = "='C:Users[Workbook Name.xlsm]Worksheet Name'! N27"
.Value = .Value
End With
View 6 Replies
View Related
Feb 6, 2013
I have some vba that opens a closed workbook, copies data from a named range and then pastes it to the active workbook.
However, what is happening is that the closed workbook is opened and only part of the data is pasted. What I would prefer to happen is this:
Open the closed workbook-->copy the named range-->paste(append) to next empty cell in column B.
Heres the code that I have got.
Sub Workbook_test()Dim wb As Workbook
Application.ScreenUpdating = False ' turn off the screen updating
Set wb = Workbooks.Open("G:WAREHOUSEPlanningSmartNew Training Plan raining plan.xls", True, True)
[Code]....
View 5 Replies
View Related
Jan 26, 2010
I'm using the following sub (thx Benvolio 2001) to do a vlookup (of A1 in Sheet2) and drop the resulting value ("found") in b1 of sheet2. Here's my question: I'm having trouble modifying the rng to use a closed workbook in another folder i:other folderotherfile.xls. The "Inventory" tab is in otherfile.xls
How can i do this? use the vlookup in a closed workbook somewhere else?
Sub Example_of_Vlookup()
Dim lookFor As Range
Dim rng As Range
Dim found As Variant
Set lookFor = Sheets("Sheet2").Range("A1")
Set rng = Sheets("Inventory").range("A4:D500")
On Error Resume Next
found = Application.Vlookup(lookFor.Value, rng, 4, 0)
Range("b1").value = found
End Sub
View 3 Replies
View Related
Aug 3, 2008
I put the following codes in the module of Workbook BS. The codes successfully opened the workbook of 2008. Unfortunately, nothing was pasted to Workbook BS of sheet2. let me know any things missing from my codes.
Sub COPY()
Dim wb1 As Workbook
Dim wb2 As Workbook
Dim rng As Range
Set wb1 = Workbooks.Open("C:Documents and SettingsuserMy Documents20082008.xls")
Set wb2 = Workbooks.Open("C:Documents and SettingsuserMy Documents2008BS.xls")
wb1.Worksheets("S").rng("B:B").COPY wb2.Worksheets("Sheet2").Range("A:A").End(xlToLeft).Offset(, 1)
End Sub
View 3 Replies
View Related
Dec 16, 2008
im looking for some code to transfer cells a3,d6,f9,i6,k10 and i18 from open workbook named "hello"
to
closed workbook named "goodbye" and input to next available row
a3 to a1
d6 to a2
f9 to a3
i6 to a4
k10 to a5
i18 to a6
View 14 Replies
View Related
Feb 12, 2010
I have a closed workbook with data in 12 columns. I have an other workbook where I choose a column and the formula needs to return the sum of the data from column 1 to the chosen column, in a given row. I can't use INDIRECT or SUMIF because of the closed workbook, so I'll probably need a formula using sumproduct, but I can't find a solution on the net.
View 2 Replies
View Related
Jul 28, 2007
Copy and paste the values only from one workbook and a set of named ranges (Name1, Name2, Name3) but only the rows within the range were TRUE is found in column A - To a CLOSED workbook named Data / Sheet1 - located at C:NetworkTemplate starting with cell B10.
View 9 Replies
View Related
Jul 28, 2007
I would like to be able to have a user execute a macro by clicking a command button that would copy some named ranges (Name1,Name2,Name3) BUT ONLY the rows/cells that have "True" in column A. The destination for the pasted data is a closed Workbook named Data – Sheet1 located on a network drive (\NetworkTemplate)
Example:
One named range are cells D10:D50, a second named range are cells F10:F50, and a third is G10:G50. – But ONLY rows 11, 13,14 have True in column A, so I only want to copy the 9 cells not the entire row from those select ranges and open a closed workbook and paste the values only starting with cell B10.
I can not find anything that looks for a value in one column and only copies select cells to another workbook.
View 4 Replies
View Related
Apr 24, 2014
I have one workbook for daily records of multiple data items.
These data are summed according to each item and exported to a specified cells in a monthly report.
Is there an alternative easier method to import data other than using "paste special" property.
View 1 Replies
View Related
Jan 22, 2010
I need to create an statement for my clients. Book A has all of the account balances. Still in book A, the account number is in column A, and balance is in column H. I just need to import from Book A into Book B, based on account number. Now on Book B, the account# is in column A and the balance needs to go to column B.
View 3 Replies
View Related
Apr 6, 2013
I have created a spreadsheet some time ago and have been asked to improve on it but I'm rusty with VBA.
I have an automated ordering system that saves each sent order as the date e.g "05-04-2013.xls" but the management team want a graph with the data for the last 4 weeks compared. I have created a seperate workbook called "consumables report.xls" which has a column with the products listed followed by columns "Quantity" and "cost" which is repeated for the 4 weeks of the month.
I want to add a button to prompt the user to choose the saved order e.g "05-04-2013.xls" (all orders saved in same directory) to copy and paste the quantity and cost columns (c8,D69) into "consumables report.xls". I got this to work earlier but it would only paste the formulas and not the values. So I need
A prompt to open workbook
Copy range (c8,d69)
Close work sheet
Paste special .value (c8,D69)
I dont care if it has to open the workbook to copy the data as this will only be used once a month so it dosnt matter how slow the code is.
week 1 week 2 week 3 week 4
Product
quantity
cost
quantity
cost
cost
quantity
cost
1
2
3
4
5
6
7
8
View 7 Replies
View Related
Feb 20, 2009
I am trying to use this code to pull data from a closed workbook. The code will be in workbook Book1 and I will be pulling information from closed workbook Book3. I need to pull data from cells A1:A4 from Book3 and place it into Book1 on Sheet2 in cells A1:A4. I receive an "Subscript out of range error" on this line of code
View 4 Replies
View Related
Dec 27, 2013
I'm trying to retrieve data from a workbook that is currently closed. The formula below works only if the workbook containing the data is open. Is there a way to use the following formula to retrieve the data from a closed workbook?
[Code] ......
View 4 Replies
View Related
Jul 14, 2014
I have created a excel program with two workbook. Workbook1 holds all the userform to add data Workbook2 is the database Basically I store all data to WorkBook2 through userforms in WorkBook1. So far to add data to database(WorkBook2) I open the workBook2 along with WorkBook1 and close it when I close the WorkBook1. and working perfectly.
Is there any way to put data into WorkBook2 without opening it. Application.Screenupdating is not not my choice.
View 3 Replies
View Related
Mar 21, 2014
I am looking for vba code to copy data from closed workbook. I searched below VBA code on google , but it copies all row from closed workbook. I need small modification in it. I want to copy only I,A,B,C,N,O,R,P,Q column from closed workbook to A,B,C,D,E,F,G,H,I column of active workbook respectively.
View 4 Replies
View Related
Oct 4, 2008
how do i copy the data from cell C1 in the active workbook "name1.xls"
to cell D10 in the closed workbook "name2.xls" in "sheet1" without opening the
closed workbook?
View 4 Replies
View Related
Apr 3, 2009
i get an excel file with dynamic data everyday. is there away i can use a macro on this file to store this dynamic data to a specfic closed workbook and add the data onto the old data in that workbook?
View 4 Replies
View Related
Jan 29, 2010
I have tried to move data from my active WB to a closed one with the following
View 3 Replies
View Related
Oct 24, 2012
I am using the following code to import data from a closed workbook;
Code:
Dim SaveDriveDir As String, MyPath As String
Dim FName As Variant
SaveDriveDir = CurDir
MyPath = Application.DefaultFilePath 'or use "C:Data"
ChDir MyPath
[Code] .....
I'd like to modify it slightly so that it only imports data according to the following criteria;
The used range from A3 onwards but only if the row in column G shows 'Never' or the figure is 30 or more. If column G passes, (so neither of the criteria apply), then column J is checked for the same criteria and if so imported.
View 5 Replies
View Related
Jan 29, 2008
I created a form and the values that it takes are Date, Issue, Name, Team Member and Cause.
I use a Production Support Report (workbook that I don't want open for people to see) and it will have tabs for each month ie; January
how can I without opening Production Support Report grab the values from the form then update the Production support report by finding the months sheet and sticking the values at the last row.
View 9 Replies
View Related
Jan 29, 2010
I want to add a button to my excel workbook that when pressed allows the user to browse to an excel file, once selected, the macro then looks for a specific sheet with a specific name and then copies some data (not entire sheet) into a sheet in the doc where the button was pressed.
View 9 Replies
View Related
Apr 2, 2004
I have 7 workbooks I would like to copy data from. The data in question is a sheet called "Accident Book" in each of the 7 workbooks.
I know how to copy the data, but how do I reference these 7 closed workbooks ? They are all in the same directory, but these are other files in there that I dont need, so I dont just want to point to the directory.
I have this code from Nimrod but can't get it working.
Sub GetValuesFromAClosedWorkbook(fPath As String, fName As String, sName, cellRange As String)
With ActiveSheet.Range("A65536").End(xlUp)
.FormulaArray = "='" & "\Bls36dc10WorkH&S" & "[" & Central 2004.xls & "]" _
& "Accident Book" & "'!" & range("A6")
.Value = .Value
End With
End Sub
I actually want to copy data from A6:N6 down to the last row entry, then paste this into the next available row in the activesheet. I need to do this from 7 different workbooks.
View 9 Replies
View Related
Oct 3, 2006
I'm currently facing problem with double for loop in the previous vba ,extracting data from closed book. however, it uses only one for loop to extract all data. I need to extract lots of data from the book and thats why i double looped it to extract more data. however, i can only get the first line of data i can't extract any more data from then on.
Sub RunCodeOnAllXLSFiles()
Dim lCount As Long
Dim wbResults As Workbook
Dim wbCodeBook As Workbook
Dim rCell As Range
Dim lCount2 As Long
Worksheets("Data").Select
Range("a2:ac65536").Clear
Application. ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False
View 3 Replies
View Related
Nov 13, 2006
What is the best way to import data from another closed workbook? I used the macro recorder and it gives me something like this
ActiveCell.FormulaR1C1 = "='[CASHFLOW.xls]Sheet1'!R66C5"
I don't think this is the best way, since it asks me to update links when I reopen the file.
View 8 Replies
View Related
Apr 10, 2007
I found many examples on this forum of how one can pull in data from closed workbooks, or copy it and have it stored in the active sheet. So far so good, but I need a solution which would do the opposite - I need the macro to take a range of data form the active sheet store it in a closed workbook and save any changes in the target workbook without opening it.
View 2 Replies
View Related
Oct 26, 2007
I have 1 excel workbook open. It is in a common directory with several other excel workbooks which are closed. In the open workbook, I have created a vba macro that completes various tasks - only within the open workbook. I want to modify the macro in the open workbook such that it will specify data to be displayed in the closed workbooks. Then, when the closed workbooks are opened next, the data will reflect what the macro had specified.
View 9 Replies
View Related
