Extracting A Value From Another (non-active) Workbook Using A Path Dynamically Formed
Sep 15, 2009
I can’t get Excel 2002 to resolve an external reference to a value in another workbook.
If I have the following literal expression in a cell in my active worksheet, it works:
='C:Documents and SettingsMy DocumentsHCSSAC123.xls'!NumSAC
In this case, the contents of the variable NumSAC is copied into the active workbook from another workbook named SAC123. This works fine without the SAC123.xls workbook being active.
But what I really need to do is have Excel reference an expression that I form by concatenating together strings and a cell value (from the active worksheet).
The following expression evaluates to be exactly the literal expression above (which worked), but it doesn’t act as a valid reference.
=”'C:Documents and SettingsMy DocumentsHCS" & $B12 & ".xls'!NumSAC"
The result displayed in that cell is
'C:Documents and SettingsMy DocumentsHCSSAC123.xls'!NumSAC
and not what I want.
If I try to use the INDIRECT function with the above character string, like this,
=INDIRECT(”'C:Documents and SettingsMy DocumentsHCS" & $B12 & ".xls'!NumSAC")
then I get
#REF!
However, if I open the SAC123 workbook (so that it is in the ring of active workbooks), then I get the correct value put into the cell.
The INDIRECT function only works if the referenced workbook is active.
Is there a way of having Excel evaluate a character string as a valid reference without having to have the referenced workbook(s) active?
In my case, I want to have a master active workbook that references over 200 other workbooks.
View 4 Replies
ADVERTISEMENT
Sep 30, 2009
I tend to stumble when trying to pull certain characters from strings, not sure how to stop and start my search.
View 4 Replies
View Related
Feb 1, 2010
1) I need to add an edit check
2) have a copy of Active Workbook Path stamped onto spreadsheet with date and time to create a visual record of where the file has been saved (described after the code below).
1) I need to verify that two cells (S7 and S9) are not blank before running my code below (=IF(OR(S7<>"",S9<>""),RUN CODE,"You must select your Provider or Division before you can save this document")).
- If both of these cells are blank a message box should notify the user that they must select the provider and/or division before they can continue with the save.
- If one or more of these cells are not blank the code below should run.
View 3 Replies
View Related
Nov 3, 2009
I am trying to import some csv files so I can combine them, but am having probs with the filename and location.
Sub test()
Dim wsName As String
wsName = ActiveCell
Sheets("Data").Select
With ActiveSheet.UsedRange
LastRow = .SpecialCells(11).Row
End With
With ActiveSheet.QueryTables.Add(Connection:="TEXT; &thisWorkbook.Path &" " & wsName &", Destination:= Range("A" & LastRow))
.Name = wsName
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells.....................
View 5 Replies
View Related
Mar 6, 2014
Using Dropbox, a lot of people will fill in formulas in personal workbooks from using data from another, central workbook. Since I cannot control where they install Dropbox or where they keep their personal workbooks, I must account for that in functions.
Now, my idea is to make them define the directory of the source in their personal workbook in a specific cell and then using that as a basis for the rest of the functions.
Example
Cell A1: C:Usersuser1Dropboxfolder (copy paste from path in Windows Explorer)
Cell B1: A1&[CentralWorkbook.xlsx]SheetName'!$H$1
Basically I want the unknowing user to copy-paste the path of the central workbook into a specific cell in their own workbook and then build my functions bsed on that. How can this be done?
View 2 Replies
View Related
May 4, 2009
I'm reading data, from specific cells off a closed workbook. When the sheet that needs said cell data is activated, it automatically opens the workbook and references the sheet nessecary. The issue I've come across, is I now need to access another workbook (Easy to open) with 12 sheets 1 for each month, and only read from the worksheet of the actual Month...
Kind of lost on how to possibly make this work. I basically need something like:
=location/[workbook.xls]Month(Today())!cell
View 9 Replies
View Related
Jun 9, 2006
There is a folder which contains some CSV files. These CSV files are updated say every 5 mins. By updations, I mean new data is appended to these CSV files, keeping old ones. Desire:
1. I want to have one master workbook which will have all of the CSV files in the folder as different sheets in the master workbook.
2. The master workbook sheet should be updated as soon as the corresponding CSV is updated.
My Approach:
Get the list of the CSV files from the directory. Open the CSV files, one after one and copy the newer data, by comparing to a marker that is updated after the new data is read.
View 2 Replies
View Related
Feb 21, 2014
i have a workbook blank template for my colleagues to fill in, they fill in this blank template and save it under the serial number of the item they are creating. However a lot of errors exist when they input the serial number inside the workbook where the serial number is a duplicate of a former serial number.
Anyhow i was wondering is there any way when they input the serial number into the worksheet that i could get it to save in a separate workbook and then have conditional formatting to check those serial numbers in the workbook to see if they match?
View 4 Replies
View Related
Mar 27, 2014
In order to do the tests, I'm using 2 simplified workbooks (test_copy and test_paste).
What I'd like to be able to do, once I click a button UPLOAD (creating the button isn't the problem), is to go to the first cell from workbook test_copy, check if that code is already on test_paste and if not add it to the bottom of the list, repeating the same cycle for each code on the workbook test_copy.
The orders of the codes might change on both workbooks differently depending on the way the columns are sorted. That is why I believe that for each code in test_copy I need to check the entire column in test_paste to check if the code already exists.
At the end of the routine, on the example below, we would have the codes 2670, 2676 and 2626 added to the end of the list on test_paste.
test_copy
Excel 2012
A
1
2684
2
2783
3
2669
[Code]...
View 9 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
Jun 30, 2014
I run a model in Excel that automatically saves my file every xx iterations. After saving the file I want to make a backup of the file. Tried
[Code] .....
but get a permission denied error message.
I don't want to use .SaveAs as it is a huge file that takes a while to save and SaveAs has a tendency to break links that should not be broken..
View 13 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
Jun 20, 2008
I'm trying to figure out a way to find a specific sheet in a workbook that does not contain the macro. Within the macro I have a cell which holds the name of the specific sheet I would like to find but I can't get it to work for some reason...
'Dim officen As Integer
'Dim thiswb As Workbook
officen = Range("A2").Value
Set thiswb = ActiveWorkbook
' Open the Active Info file
Workbooks.Open "C:My DcoumentsActive 20080616.xls", , , , "xxxxxx"
' Dim sourcewb As Workbook
Set sourcewb = Workbooks.Open"Active 20080616.xls"
Sheets("officen").Select
RowCount = ActiveSheet.UsedRange.Rows.Count
Range("B2").Select.............................
View 8 Replies
View Related
Aug 4, 2006
I have a very basic cut copy and paste macro in a workbook (ThisWorkbook) that fails when it reaches the line :
Workbooks.Open Filename:=ThisWorkbook.Path & "" & DataWbk, UpdateLinks:=3
When i hover over this line it states the actual (and correct) path of the workbooks that the macro is relevant to, but the error says the path is unrecognisable.
However, in other similar macros that i have that work ok, when i hover over this line it states : <Object variable or With block variable not set>.
Is there any way that i can set the 'ThisWorkbook.Path' to read this? To my knowledge i have not recorded these two macros differently, yet they are producing these contrasting results.
View 7 Replies
View Related
Mar 21, 2013
I have got a master workbook and I have written macro to copy and paste data on another workbook. write a macro to save the new workbook to a file path with a file name where both file name and path are stored in master workbook sheet...
View 5 Replies
View Related
Mar 6, 2014
My requirement is as follows......
I want to get a copy of worksheet from specific workbook to active work book in which i want a copy of sheet get moved.
View 2 Replies
View Related
Oct 31, 2008
how can i open a different path of the workbook in the listbox. if my listbox there only show the file name.
View 2 Replies
View Related
Jun 21, 2013
Want to Copy a Module(s) to another workbook in a different path and also password protect it at the same time using VBA.
I need to copy some Module(s) from One WorkBook to another using VBA..
I have used the following code to good effect, however my TargetWb is not at the same path, so I need some modification in this code if that's possible and also this VBA Module which is Copied and Pasted in the Target WOrkbook needs to be Password Protected so that it cannot be tampered.
The code which I'm using currently is:
Code:
Sub CopyModule(SourceWB As Workbook, strModuleName As String, _
TargetWB As Workbook)
' copies a module from one workbook to another
' example:
' CopyModule Workbooks("Book1.xls"), "Module1", _
[Code]...
Code:
Sub Try()
Call CopyModule(Activeworkbook, "Module1", TargetWb)
on this to pass the TargetWb as a variable which can be generated in the code used for generating the File using a Macro..
End Sub
In the above code The SourceWB would be the ActiveWorkbook and the Target Workbook is the file created using another Macro from several sheets of the Active Workbook and its created in a folder with a specific name and therefore the Path is one more level inside than the Active Workbook.
if the Transferred Module can be Passworrd Protected and Locked for editing..for the end user.
View 9 Replies
View Related
Jul 23, 2014
here I have a few good and perfect codes, the issue is my systems changed so I need it to look in some different places for data.
basically I need to create a file path that includes the data from a cell. this cell is found be searching a table full of data.
see the code below
Code:
Sub saveandprint()
Dim MyPath As String
MyPath = MyPathstart & "Invoice" & "" & Format(Date, "dd-mm-yy")
MyPath1 = "Z:Invoices Temp GKL " & Range("B16").Value & Format(Now, "hhmm")
[Code]....
View 4 Replies
View Related
Jan 10, 2008
Is there a way to display the location of an active workbook on my local drive and save it into a varible. I'm new to Excel and VBA but I'm trying to create a template to use for multiple vehicles. When the macro is run...
1) the workbook will have to identify what Workbook and worksheet it is in and save it as a varible so that it can access it later. Each workbook is specific to a vehicle.
*(The reason is because I will need to refer to the address of the original workbook when pasting the copied data from the "OilChart" Workbook.)
2) Copy the vehicle identification # and save as a varible. (Dim vehicleNumber As Variant)
3) Open another workbook on the local drive (OilChart.xls)
4) Run the macro in the new workbook (OilChart.xls) to update the data
5) Search Column B in (OilChart.xls) for matches of the vehicleNumber, and for all rows that match, select and copy all rows and paste it back into the orig workbook (Vehicle Specific Workbook).
This is the code I have so far.
Private Sub btnFind_Click()
Dim vehicleNumber As Variant
Dim sourceLocation As String
Dim sourceFileName As String
Dim fileName As String
Application. ScreenUpdating = False
I have'nt figured out how to do step 5 yet so if anybody has any ideas I'd be open to them. I tried looking at Aaron Bloods Kickbutt VBA Find Function but I got a little lost on how his code would relate to my code. It also looks like it doesn't copy or paste anything to another workbook.
View 8 Replies
View Related
Feb 16, 2008
I work on multiple workbooks. They all calculate a lot of functions and I am forced to create multiple workbooks to do these calculations. Linking to other workbooks basically displays the results of workbook1 and works from there.
But...
I need to keep an overview of these files, so I store them in different folders all within the same 'master' folder. So let's say: master folder is FolderMaster, and this contains folderA, folderB, folderC. FolderA contains workbookA1, workbookA2; folderB contains workbookB1, workbookC2 and finally folderC contains workbookC1 and workbookC2.
Suppose that this hierarchy might change. Moving eg workbook A2 to folderB. This would cause a #REF if it is linked in another workbook.
So I thought: organize your hierarchy in a 'master workbook' and store all paths to the individual files in a worksheet. If I link to a specific folder I do not directly link to the actual folder, but would use INDIRECT and grab the filepath from the masterworkbook. If the path to a workbook change I do not have to update all individual links, but only change the path in the masterworkbook. Right?
But 'how' is the big one here. How do you formulate your path to a file? It can not be the entire file path since that's variable... (sometimes 'hd:User1:....'; sometimes 'HD:User2'..."). So I think I need something as mentioned in this office help thing. But I have no clue. I do not want to use VBA, I hoped for another solution to this.
View 4 Replies
View Related
Mar 21, 2014
I am trying to add a vertical line dynamically to a chart. What I mean is I have a chart that will have a line type chart on it and I want to add a vertical line to it based on the output of one cell in that same workbook. I am making this workbook for use by end-users so I can't ask the end-users to make a bunch of modifications to the sheet or or chart. What I do has to just work when they use the workbook.
The line chart has an X axis of time and a Y axis of items sorted. (This is the part of the chart I have done already.) I want to add a vertical line fed from a cell which will have a time fed from a cell on the workbook. For example the line chart goes from 9:00 to 16:00 and the vertical line might be at 13:00. The cell feeding the vertical line is subject to change based on the other inputs on the workbook so that's why I say the vertical line has to be dynamic as the vertical line could be anywhere between 9:00 to 16:00 or it might even be that I can't place the vertical line because the feeding cell is outside the range of 9:00 to 16:00.
I have seen a number of ways to do this but none that are dynamic and automatic and don't require end user to adjust the workbook to make the vertical line at the correct on the time scale.
View 2 Replies
View Related
Oct 26, 2011
The folder which contains the Excel file I'm working on is
C:Usersar3DocumentsKey Performance Indicators
however if I place =INFO("directory") in a cell I get
C:Usersar3Documents
Is there a function where I can get the full path of the workbook in the cell?
View 2 Replies
View Related
Aug 18, 2006
If you have a workbook or a template open you can find out the full path of the file from the .FullPath property. My problem is that when a user double-clicks my .xlt file (to create a new workbook based on it), I want the code in the Workbook_Open event to be able to tell the full path of the template it was created from (because the code will later go on to save the new workbook under a date related filename in the same directory that the template was located in). I haven't been able to find a property that gives me any clue as to the location of the template this new workbook is based on.
View 5 Replies
View Related
Sep 15, 2006
I am having trouble getting a macro to open a specific file from an open workbook in the same folder based on the current path of the open file. The application is this... I have two files in the same folder. The first is for data entry and it contains a macro button that will open the second which is a template to which the data in the first will be copied. I can easily do this with a fixed path to the second file, however, I would like to be able to move or copy the folder containing these files to other locations or computers without having to change the path each time. I would like to base the path to the second file minus the file name on the first (already open file) path. Assuming that the first file containing the desired macro button is already open, here is one of many of my attempts...
Workbooks.Open " ActiveWorkbook.PathJob Tracker -.xls"
Assuming the second file is the active workbook, I achieved a similar path assignment when saving the file...
ActiveWorkbook.SaveAs "Application.Path/Jobs Active/" & "Job Tracker -" & Range(" '[Job Tracker -.xls]Specifications'!$A$6").Value
View 2 Replies
View Related
Dec 28, 2006
I've go a shared Workbook, which will be distributed among several users and stored in different places. The workbook uses the following
Sub savemeas()
Worksheets("data").Visible = True
Sheets("data").Copy
Application.DisplayAlerts = False
ActiveWorkbook. SaveAs Filename:="d:" & Range("a1").Value, _
FileFormat:=xlText, CreateBackup:=False
Application.DisplayAlerts = True
ActiveWorkbook.Close SaveChanges:=True
End Sub
to save the sheet "data" as a text file with a name based on the value of cell a1. All I need is to modify the code so that the target path would not be
ActiveWorkbook.SaveAs Filename:="d:" & Range("a1").Value
but be the same as the source Workbook's - so that I wouldn't have to modify the code for each user separatly, because the sheet would alwayas be saved in the same folder as the current path of the source workbook.
View 4 Replies
View Related
Apr 17, 2007
Is there a way to find the full name and path of the workbook the macro is running in?
View 4 Replies
View Related
Aug 27, 2007
I'm trying to save a copy an excel file in the same directory of the original file. The code is pretty standard but it does not work if the file is stored in C: (only c: ) Is that a bug or am I doing something wrong? Anyways here is the code in case someone is interested in trying it out:
Sub CreateCopy()
ChDrive ThisWorkbook.Path
ChDir ThisWorkbook.Path
fileSaveName = Application.GetSaveAsFilename( _
fileFilter:="Excel Files (*.xls), *.xls", _
InitialFileName:="CMS_" & Format(Now(), "mm-dd-yyyy"))
If fileSaveName <> False Then
MsgBox "Backup copy saved as: " & fileSaveName
End If
End Sub
View 5 Replies
View Related
Jan 5, 2008
Excel 2002 SP3
When a file is currently open, and the user selects "Save as", it takes them to the files native directory. (Where it was last saved).
If the user has that same file open, opens another file from a different directory, goes back to the first file and selects "Save as", it takes them to the location where the last file was opened from.
View 9 Replies
View Related
May 9, 2014
I am trying to transfer a value from the active worksheet in one workbook to another workbook. I keep getting a subscript out of range error on the line that uses activeworksheet command when I run my code.
View 3 Replies
View Related
