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.
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.
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?
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:
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.
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?
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.
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
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)
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"
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.
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...
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.
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.
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.
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.
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.
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...
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
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.
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
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.
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.
