Cell Value Of Closed Workbook With Concatenated File Path In Cells

Sep 24, 2007

I have a report that is collated from several other workbooks. The source workbooks follow the same naming convention throughout the year and so I have managed to concatenate the file path to bring through the text of the filepath and cell reference however cannot work out how to get the value of the cell. For example, I have the file path in A1, file name in B1 and sheet and cell referernce in C1.

I am using {=(A1&B1&C1)} which gives me R:CONFIDENTIALREPORT FOLDER[REPORT 24.09.07.xls]Sheet1!$D$1. To make matters slightly more difficult, the source reports would be closed. Is there an easy way or am I going to have to do this in VBA.

View 4 Replies


Macro To Save Workbook By Referencing Cell In Another Workbook For File Name And File Path

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

File Path From Cells In Workbook

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


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")


View 4 Replies View Related

Import CSV File Using Cell As File Name & Active Workbook Path

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
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

Pull Data From Closed Workbook Through Assigned Path

May 6, 2014

I want vba on pulling data from closedworkbook from certain path with range of end column values

I have two workbook one is Opencall_06-05-2014...

File string contains Half today date...

I want pull data from Opencall_06-05-2014.xlsb ,Sheename is "Opencall" Paste into Master file...with range of (A:BC).

Find the attachment of two workbook...

View 5 Replies View Related

Copy Range From Closed File & Paste To Closed Workbook

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

VLookup Function Which Takes Data From A Table In Closed File - Dynamic Path

Feb 23, 2012

I have a vlookup function which takes the data from a table in a closed file. A folder where the file would be located will change based on the value in the cell reference.

I used below formula which works, but every time when i drag or try to edit and enter, the path of the file path brokes and i need to reselect the path where the file is located. Is there a way where i can stop this?



View 4 Replies View Related

Get Relative File Path Of Workbook

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.


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

Create & Save Workbook To Same Path As Macro File

Aug 21, 2008

I have code to create a new workbook, and when I try to rename it I get "Compile error: Can't assign to read-only property."

Dim objXlApp As Object
Dim wkb As Workbook
Dim wks As Worksheet
Set objXlApp = CreateObject("Excel.Application")
' Create a workbook
Set wkb = objXlApp.Workbooks.Add
' Delete all worksheets bar the first one.
For Each wks In wkb.Worksheets
If Not wks.Index = 1 Then

End If
Next wks
'Create some worksheets and names
With wkb
.Worksheets(1).Name = "myWorksheet1"
.Worksheets.Add.Name = "myWorksheet2"
.Worksheets.Add.Name = "myWorksheet3"
.Worksheets.Add.Name = "myWorksheet4"
End With...................................

View 5 Replies View Related

Save File As Text Using Cells For Path & File Name

Dec 20, 2009

How to save a file, with file- name. but the directory is to be read in worksheet "towns" in Cell1 (brussels) and filename in worksheet "names" in cell B2 (i.e. winter), so it saves to c:russelswinter.txt as a wordpad or kladblok txt file, that keeps a number, so each time we push a button "go back from worksheet names to worksheet towns" the "number" that is saved in the txt document goes up by value +1. In Flemisch, the "old" code goes as follows, and saves the number in the txt file Factuurnummer7.txt. But I want that the file name (here: FactuurNummer7) can be a variable text issue, which has to be read - as already noticed - in cell B2 (with the word WINTER). So the are 2 worksheets: towns, ans names

pad$ = Application.DefaultFilePath
'controle = Dir(pad$ + "FactuurNummer7.txt")
'If controle = "" Then GoTo EerstAanmaken
'Open pad$ + "Factuurnummer7.txt" For Input As #10
'Input #10, Nummer1
'Close #10......................

View 2 Replies View Related

Macro To Append Multiple Xls File In Single Workbook From Specified Path

Jan 25, 2010

I need a macro to do the following

1.Get list of .xls files from specified folder.

2.Append the files in new workbook in same folder.


I want to get the list of Sample_*.xls and
create Sample.xls master file which adds the above three .xls as sheets in it.

Actually my macro as below

View 10 Replies View Related

File Backup To Different Folder Location Automatically Whenever Workbook Closed

Feb 10, 2014

1. A workbook is closed

2. In addition to the original workbook being saved, a copy of the workbook with the current date is also saved to the specified location of my choice.

For example

"C:UsersUSER1DesktopBackup Test as of 02-10-14"

3. If a copy of the workbook is already saved with today's date, then overwrite it automatically without prompting the user.

4. All the above happens without any user interaction.

View 8 Replies View Related

Save Hyperlink Path For File With Absolute Path And Non-Relative?

Mar 4, 2013

is it possible to configure Excel in order to save the Hyperlink path for a file with absolute path and non relative?

I notice that the hyperlink is ....pdf ry.pdf

if I change the position of the file excel there is a problem!

I would like to save es. d:invoichepdf ry.pdf

View 1 Replies View Related

Trim Full File Name & Path To File Path Only

Sep 27, 2006

I have a variable ("DestFile") that defines a path to a file (used in saving the file)...

I'm in the process of getting a Sub to hyperlink to this file, but in some circumstances, I may only want to hyperlink to the folder, not the actual file...

How would I go about trimming the "DestFile" address to get a "DestFldr" address?...

An example of "DestFile" might be;
S:BryanFor KenGulf ConstructionST0609014-t.xls
(the file name length may vary)
What code can I use to consistently trim it back to;
S:BryanFor KenGulf Construction
as the "DestFldr" variable?

View 4 Replies View Related

Path Of File Picked Up From Cells In Spreadsheet

Oct 17, 2008

Is there a way to make the contents of a particular cell in a worksheet show where to pick up another file. Here is my code to pick up a file:

View 9 Replies View Related

Macro To Separate File Name And Path From Complete Path

Oct 3, 2012

I want to run a macro to separate File Name and Path from the given complete path

For Example


In Column A : Given Complete Path " C:MainFolderRecordsSubFilesFile1Record.pdf

I need it separeted like


In Column B :File Name = Record.pdf
In Column C :File Path = C:MainFolderRecordsSubFilesFile1

Is there any way to do this through a macro

View 2 Replies View Related

Cell Addresses Derived From Concatenated Values In Separate Cells

Apr 2, 2013

I need to return a value from a cell in Column H, but with a row number that varies with each entry.

I repeat this formula every 5 rows or so. But the cells referenced on another spreadsheet are only one row apart. Because of this discrepance, when I copy my highly developed cluster of formulas, they only reference every 5th entry on the other sheet. What I've been doing is manually altering long, complicated formulas by hand. I can't do a replace function because every once in awhile it'll find an extra instance of the digit I'm replacing and mess up parts of the formula I don't want changed. I could avoid this tedious, time-consuming work-around if I could make a formula that would do something like this:

=Display Value from in column H: (row number per cell B1)

And cell B1 would include the number 3789.

So the returned result is the value from cell H:3789

View 4 Replies View Related

Copy Cells From A Closed Workbook

Dec 9, 2008

I have a folder at this location c:documents and settingscharliedesktoplabor

it contains numerous files that have this format 01_Projections with 01 being the store number and it goes from 01 to 20. the word projections is the same on all 20.

I want to access cell A12 on a sheet titled "summary" in all files and have bring over to another workbook without having to open each one. How can I accomplish this?
What if I want to bring back numerous cells from this same sheet and place them in cells in the open workbook?

View 9 Replies View Related

Save File Using Cells For Path & Name Macro Code

Mar 19, 2008

I want to create a macro that saves my file this way:

in a folder named after B2
as a file named after B4n.

View 4 Replies View Related

Transfer Data Cells Into A Closed Workbook

Sep 25, 2007

Workbook A contains data that feeds into Workbook B. Workbook B retrieves data from Workbook A (via a macro) and then puts data into cells in Workbook B, Workbook B process the information and does calculations etc... on the data from A. Now I want to transfer the results back to Workbook A in specific cells.

Example (just for information only and not actual problem): Workbook A, contains employee information, years in company, position etc... This is transferred to Workbook B (this portioin I have coded and tested). Workbook B calculates pay, benefits etc... Now I want to put the pay information back to Workbook A.

View 9 Replies View Related

Pull Value From Closed Workbook Where Workbook Name Is In A Cell

Nov 24, 2007

I want to open an excel file whose name is a value in a spreadsheet, and then pull a value from the spreadsheet I queried into my existing workbook. Here is the syntax i have so far:

to note: I'm calling the workbook in which the macro is located testproject.xls, and I'm using [integer].xls as my list of files that the macro will query. Ultimately, this code will go in a loop.

Set currentCell = Worksheets("Sheet1").Range("A1")
varCellvalue = currentCell.value
Workbooks.Open "path_of_file" & varCellvalue & ".xls"
ActiveCell.FormulaR1C1 = "=[3.xls]Sheet1!R1C1"

The problem is, I want [3.xls] to carry the sale value as varCellvalue with .xls appended, and not be kept static at 3. I tried inserting & varCellvalue & ".xls" into the brackets, but with no luck. My only difficulty is getting [3.xls] to vary along with varCellvalue.

View 6 Replies View Related

Open From Dialog Box To Insert File Path And Name Into Cell

Jun 21, 2009

Is there a way to use the open dialog box (like as if you where opening another workbook) and then with the selected item inster the path and file name into a cell.

View 2 Replies View Related

Change File Path In Formulas For Every Cell In A Sheet

Oct 9, 2009

I have a master workbook that is referencing data from several other workbooks whose file names are based on the years data was taken. In the master work book on any given sheet, I would like to be able change the data being used in the formula calculations by simply changing the year value in a single cell. By changing the date value, the formulas are directed from one data workbook to another.

e.g. in the master workbook in Sheet1, if you enter 2008 into cell A1, all of the formulas in Sheet1 now pull values from 2008.xls; if 2008 was replaced with 2009 in cell A1 on Sheet1 of the master workbook, then all of the formulas on Sheet1 would now pull values from 2009.xls.As I am completely new to VBA (bought my first book last night), I am looking for any help that is out there. I am not entirely new to programming (used C++ to write numerical simulations) but I have zero experience writing macros and the like.

View 13 Replies View Related

Dynamic File Path Depending On Cell Data

May 18, 2007

I think this is possible but cant find anything on it ........

View 9 Replies View Related

Assign Value From Closed Workbook Cell

Nov 11, 2006

Is it possible to write a macro in Book1 to assign Public Varibles in Book1 to values located in cells in Book2 without first opening Book2. So lets say in Book2, sheet1, cell B2 contains the number 5. I want Public Variable "Number_value" in Book1 to equal the number 5.

View 3 Replies View Related

Refering To Cell In Closed Workbook

Apr 17, 2007

I'm trying to check if a workbook can be used in some processes later and whithout opening the other workbook. is it possible. i have done this

str_prod_PT = Application. GetOpenFilename
If str_prod_PT <> "FALSE" Then
If Workbooks(str_prod_PT). Sheets(1). Range("A1").Value <> "Ordem" Then
MsgBox "Ficheiro incompativel", vbCritical
TextBox1.Value = str_prod_PT
End If
End If

View 3 Replies View Related

Pull Cell Value From Closed Workbook

Sep 20, 2007

it is possible to get a range value from a worksheet without having to open the workbook. I have being trying for a long time to try and work out some code to do the good but have failed miserably. Please find below my latest attempt

Option Explicit

Sub ValuationDataExtraction()

Dim shtDataSheet As Worksheet
Dim strMfolder As String
Dim rngBrokerName As Range

Set shtDataSheet = Worksheets("Data Sheet")

strMfolder = "RD: PEP data files:PEP valuations:PEP 13200s:13235.worksheets(5th Apr 08)"

Set rngBrokerName = strMfolder.Range("brokerName")
shtDataSheet.Range("b65536").End(xlUp).Offset(1, 0) = rngBrokerName.Value

MsgBox "Done"

End Sub

View 5 Replies View Related

Function To Get Full Path Of Workbook In Cell?

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


Is there a function where I can get the full path of the workbook in the cell?

View 2 Replies View Related

Read Many Cell Values From Closed Workbook

Apr 16, 2009

I've recently discovered the usefulness of VB in excel and have managed to write some basic macros that enhance many of my workbooks. BUT, I've bumped into a problem I can't seem to solve. Please help!

I'm looking to import the values in a large range of cells ( 5 x 5000) in a closed workbook into a range that i specify in an open one, where both workbooks are housed in the same directory. I also want to include logic that allows me to move the 2 files to different directories (the names will never change, only the paths) and have the code still work--I believe i'd use relative path references?

I've found lots of snippets of code on the topic but can't seem to get any of them to work. For instance: http://spreadsheetpage.com/index.php...a_closed_file/

Problem is, I'm so new that I don't even know where to begin pasting the code (objects vs. modules, etc) in the VBA editor.

View 9 Replies View Related

Add COUNTIF To Cell & Reference Closed Workbook

Jul 27, 2007

i'm trying to do a COUNTIF from an open workbook. The range I want for it is in another workbook, which I do not want to have open every time I run the macro.

This is my current
Sub cellLink()
Sheets("Calls In-Out Trend").Range("ag18").Formula = _
"=COUNTIF('C:***[*.xls]sheet name'I:I, QXO)"
End Sub

editthe range is just so I can see if it's giving the right values, the range won't be like that once I get it working. I'll probably be using logic statements to place them in the appropriate cells. The * is just me taking out the directory names, its on the desktop)

It can copy cell values from another unopened workbook, but when I try to put in COUNTIF it doesn't want to compile/run the script.

That's basically the question. Here's some background and my aim:

I'm scanning row I:I for certain keywords which I then count up and put into corresponding columns of a chart.

View 9 Replies View Related

Copyrights 2005-15 www.BigResource.com, All rights reserved