Put File Name In Cell Of Workbook

Feb 7, 2007

Is it possible to have the name of a workbook referenced in a cell via a formula or VBA in that workbook? I'm trying to extract the date that is in the filename, and input that in all cells in column N that have data in column M. I know I'll have to do some manipulating, but if I could somehow automate getting the filename input into the workbook, I might be able to muddle my way through it.

View 2 Replies


ADVERTISEMENT

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

Saving Cell Contents To XML File Saved As Current Workbook File Name

Dec 12, 2012

I have numerous spreadsheets that I need to open and unhide a sheet, that has XML data stored in cell A1. What I need to do is copy that data in cell A1 and paste it into a text document and save that as an XML file saved as the XLS workbook name with a date stamp.

I'm running into many issues, the main issue is the saving as current file name, and the formatting of the text/XML file.

Here is my current code, which doesn't reference the current file name and is just very generic. Once I get the saving as file-name correct and the formatting of the xml file correct, I will work on it a bit more.

Code:
Sub Test()
Dim Rng As Range
Dim wb As Workbook
Set Rng = Range("A1:A2")
Set wb = Workbooks.Add
With wb
Rng.Copy

[code]....

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

Cell Content As File/Workbook Name

Aug 23, 2006

how I can use the contents of a cell (in this case D2 and its a number) as a file name and then save in a specified location. I am using a template to create invoices and then wish to save them all in one folder. Each invoice will have a different invoice number so there will not be any problems with duplications.

View 3 Replies View Related

Use Cell Data To Open File/Workbook

Aug 24, 2006

I have a cell that is pulling data from a separate file, no problem there. However, I want the name of one of the folders within that path to change monthly (eg. by monthly name). How can I make a file path incorporate a cell link within a filepath (eg. instead of it saying 'Aug 06' I want it to refer to cell A1 which reads 'Aug 06').

View 2 Replies View Related

Macro To Save Workbook/File As Cell Value

Sep 15, 2006

I need to be able to save a workbook by running a marco and it save the file automatically by pulling what ever the value of cell A1 is.

Ex: I want to run the macro and it save my excel workbook in C:Documents as (Value of Cell A1) What code would you put into Visual Basic.

View 3 Replies View Related

Formula Reference To WorkBook With File Name In Cell

Apr 9, 2008

I need to reference a cell to another cell in a different workbook (not opened), however the name of this workbook contains a variable in its name. Something like:

='C:[Week" & variable &".xls]R23'!$D$3

where variable is the number of the week. I have tried using a cell name instead of a variable: ='C:[Week=($B$3).xls]R23'!$D$3

and I get a dialog box to update values by selecting a file.

View 4 Replies View Related

File Copy Macro - With Directory Specified In Workbook Cell

Feb 23, 2010

I am continuing to struggle with my first attempt at a macro, and wonder if someone would mind taking a look to see where I have gone wrong? All I am trying to do is create file copying tool where the user can define the source and destination directory in workbook cells, and assign the macro to a button to complete the copy of all files from source to destination.

The macros do this:

Source and Location directory defined in Sub Archive1.
These are passed to file copy macro Sub ArchiveScript.
This macro checks for presence of destination directory, ensures that the directory backslash is in place, specfies the variables fn and fn2 to make sure the filecopy command copies all files in the directory, and then carries out the filecopy function itself.

However, I am getting two errors from the code that I can't figure out (I am still very much a beginner!)

- Invalid procedure call or element on fn = dir() at the bottom

- The copy still works, but only seems to copy one file from the source to the destination, and not all of them.

View 6 Replies View Related

Open File And Move Cell Data To New Workbook

Sep 12, 2006

I created a new workbook that contains a list of additional design requirements than our old checklist. These requirements are answered by placing a 1, 0 or .5 in the cell next to the requirement (1 = yes, 0 = no and .5 for half credit). I can't simply go to the old workbook/worksheet and copy the results into the new workbook/worksheet as the rows of requirements don't match up since some requirmements have been deleted and new ones added between the old and new checklists.
I created a command button in the new checklist and what I want it to do is:
- open a dialog box that lets me select the old checklist file (these are all excel files, but with different names).
- go the the proper worksheet in the old file (DFT Checklist) and copy a column of results from the old checklist ("DFT Checklist" worksheet) and paste them into the proper cells of the new checklist ("DFT Checklist" worksheet).
- The macro would know (i'll have to tell it) which requirements and cells are still valid answers between the old checklist and the new one. It would disregard requirements that are no longer in the new checklist.
- Once complete, it would close the old checklist without saving.

View 4 Replies View Related

Retrurn Date Portion Of File/Workbook Name To Cell

Sep 27, 2006

I am using Windows XP and Excel 2003. The filename I want to open is called "MMO Activity Report 25-09-06.xls". Part of the filename as "MMO Activity Report" will never change but "dd-mm-yy" will change constantly. I want it to show in active worksheet cell A1 in format YYYY.MM.DD ie. 2006.09.25. I type in Sheet1, cell A1 as "=filedate()" but it shows me "#VALUE!"

Function filedate()
Application.Volatile
Filename = Mid(Right( ActiveWorkbook.Name, 21), 1, 9)
filedate = Format(DateValue(Mid(Filename, 1, 2) & "-1-" & Mid(Filename, 4, 2) & "-1-" & Mid(Filename, 7, 2)), "yyyy.mm.dd")
End Function

View 6 Replies View Related

Save File / Workbook As Cell Reference Filepath & Name

Feb 8, 2008

I'm trying to get Mac OS X Excel VBA to understand this code however the ChDir reference and the filepath seem to be causing problems. I would like to specify in the code where the file should be stored but I did try removing the filepath section altogether but to no avail.

Option Explicit
Sub SvMe()
'Saves filename as value of A1 plus the current date
Dim newFile As String, fName As String
' Don't use "/" in date, invalid syntax
fName = Range("A1").Value
'Change the date format to whatever you'd like, but make sure it's in quotes
newFile = fName & " " & Format$(Date, "mm-dd-yyyy")
' Change directory to suit your PC, including USER NAME
ChDir _
"C:Documents and Settings USER NAME Desktop"
ActiveWorkbook. SaveAs Filename:=newFile
End Sub

View 2 Replies View Related

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

Open Text File From File Manager Vs. Within Workbook

Oct 2, 2006

I've encountered a bit of an odd one. I am opening a tab delimited file, which has been sved as an xls. When I open from file manager, it opens correctly. When I open from Excel, I have to go through 3 screen dictating how the data is set up, and the end result is that a couple of the numbers come through as text, rather than numbers.

Failing an explanation - how do you open workbooks as file system objects - currently I am opening them using

Workbooks.Open Filename:=wb.Path & "/" & ws. Range("B1").Value

If I open them by mimicking the FM, perhaps it will work OK.

View 2 Replies View Related

Convert Each Column Of An Workbook File To New Text File

May 30, 2007

I want macro which export each excel column to new text file. The data in excel file is number. The column has only 5 rows that means each new text file should contain five lines of one column. It looks simple but couldn't manage to do macro for it. I have very big data set in one excel file, and have to be splitted into text files. The file name in new text files can be any kind as long as it can be in some sort of order for each export.

View 2 Replies View Related

Import A .bas File Into A Specified Workbook

Jan 9, 2007

At work I have a folder full of .bas files containing useful UDF's.
At the moment I am the only person in my team that makes use of these files as importing a .bas file is beyond the rest of the team (i.e. then know it contains code so they get scared).

What I'd like to do is create a workbook where they can select one of the files (each file contains a single UDF), maybe see what it does (I put an explanation in the Comments section of the properties for each file) and then have it automatically imported into the workbook of their choice so they can make use of these functions.

View 9 Replies View Related

Does File / Workbook Exists

Nov 28, 2009

a macro to check if a file exists before saving. The code saves the file OK if the filename does not exist but fails if it does.


Sub CheckFileName()
Dim FName As String
FName = ThisWorkbook. Name

'saves file
Dim FPath As String
FPath = ThisWorkbook.Path & ""
Dim y As String

View 9 Replies View Related

Convert Workbook To PDF File

Apr 26, 2006

convert all spreadsheet in a workbook to one pdf file. I use PrimoPDF to convert, then I only convert 1 sheet to PDF even that I have select all sheets. My be it is a better PDF converter for free you use or other ways to do it.

View 4 Replies View Related

Open Workbook Within Another File

Nov 12, 2006

I have tried to work this problem out by myself, but I'm afraid it's beyond my ability. Is there a way to open a file from inside excel. Can I open " Master1.xls" from inside " Master 2.xls, (Master 2 is running, Master 1 is not open). I've tried linking but I think that's beyond me as well.

View 4 Replies View Related

Workbook File Not Accessible

Oct 8, 2007

I tried opening an excel 2003 file in an xp environment and it says that the file is not accesible? how can i open the file?

View 3 Replies View Related

External File(s) Referenced And File Links Change Based On Row Cell

Oct 22, 2009

I have attached the file I am working on. I am attempting to create a link to an external file based on the value of cells in column A. Then I would like to simply copy the formula down, lets say in Column B, the rows and as I do the external file reference will change depending on the value within the cell in Column A. I hope that I am making sense.

I am using Windows Vista with Excel 2007. The files will all be within the same file folder, however, there are hundereds of files so I won't be able to open them all for the indirect to work.

View 9 Replies View Related

Save Each Worksheet As PDF File And Name Each File Based On Cell Value In Each Sheet

Jul 10, 2014

I have a Workbook that already has a macro in it that will generate multiple sheets based on certain criteria.

For each of these newly generated sheets (numbered 1-6 in the attached example), I need to be able save each of them to a PDF file based on a unique name contained in a certain cell (in this case, each named is referenced in cell Q1 of each sheet). As such, I should end up with 6 pdfs based on the attached sample files.

The PDFs would ideally need to be landscaped and should be just 1 page per sheet.

In my attached sample workbook, you'll notice that I have a "PracticePrint" macro that doesn't quite get the job done. One other criteria involves not printing any sheet with the characters "1010" in it (my PracticePrint macro is setup to do that, but it fails to do much else).

View 1 Replies View Related

Determine If A File IS A Workbook Before Opening

Aug 6, 2009

Is there anyway I can test / inspect a file before attempting to open it to check that it is indeed an Excel workbook?

I don't want to do it by file extension as that can / will be changed, but rather wondered if there was anyway to examine the ACTUAL file?

I'm wondering if all Excel workbook files start with a particular section of data or anything?

View 5 Replies View Related

Saving Workbook And Changing File Name?

Mar 20, 2014

I've got a macro set up to copy a range of cells in one workbook, open another existing workbook, and then paste that range. So far so good.

However, I need the macro to also save a copy of that second workbook with a filename that is derived from the original workbook.

To be a bit clearer, let me give an example of how I'd like this to work. Let's say the original workbook is titled Pickle2014-03-14.Raw.xlsm, and I have a second workbook titled UploadFormat.xlsx. I want to run the macro in Pickle2014-03-14.Raw.xlsm, have the data pasted to UploadFormat.xlsx, then save UploadFormat.xlsx with the file name Pickle2014-03-14.Final.xlsx.

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

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

Trying To Import File Into Current Workbook

Jul 21, 2008

Sub Macro1()

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "COOPtbl1", "C:Documents and SettingskanegrMy DocumentsBook1.xls", True, ""

End Sub

Heres the code I keep getting a

"Run-time error '424':

Object required"

Message.

View 9 Replies View Related

Workbook Growing In File Size

Aug 12, 2008

I have a workbook that I am working on that seems to be having file size growth issues.

All it is is 2 worksheets.

Worksheet 1- Columns A-S & 63 rows. That worksheet alone is 7mb.

no macros..No VBA..No formulas. Just straight text data with a handfull of auto-filters. I took the auto filters off and saved to see if that was the issue and it did not make a difference.

Worksheet 2- Columns A-S & 35 rows. This worksheet is almost 3mb.

Now I can make a change (when my computer catches up) to 2 cells and sometimes it will jump in size by 1mb or more.

View 9 Replies View Related

Save A Single WorkBook Tab To A New File

Aug 28, 2008

I am using Excel 2003 and need to save via VBA code a single tab in a workbook to a new workbook. I need to be able to specify the new workbook file name and location. The copy feature on the tab won't work because the data is too large and I get an ugly message using that option.

View 9 Replies View Related

Open Workbook Using File Name In ListBox

Mar 8, 2009

I've created a Form (Userform1) and a Listbox(ListBox1). The listbox is initialized by creating a File System Object so that all XLS files within a folder(Test1) are added to the Listbox. I have a Sub to call the form from a workbook. The files saved in the "Test1" folder appears in the list but I get an error when I select and launch the command to open.

Private Sub UserForm_Initialize()
Dim fso As FileSystemObject
Dim fld As Folder
Set fso = New FileSystemObject
Set fld = fso.GetFolder("c:Documents and SettingsEricDesktopTest1")
For Each Fil In fld.Files
If UCase(Right(Fil. Name, 3)) = "XLS" Then
ListBox1.AddItem Fil.Name
End If
Next
End Sub

Code to open workbook from the listbox

Private Sub CommandButton1_Click()
Workbooks.Open ActiveWorkbook.Path & "" & ListBox1
UserForm1.Hide
End Sub

View 4 Replies View Related

Room Reservation Workbook File

Jul 3, 2009

Following on from this thread: Create Room Reservation System

There are 3 problems that i've spent all morning trying to work out but can't:

1: I need to change back the layout of the Gantt chart (as it matches the paper system we have been using here, so it will be easy for the other volunteers to use it) - I did change it back and played with the conditional formatting and macro's but i can't get it too work like the version that AAE changed...

2: If I make a reservation ‘Booking’ is placed on the gantt chart, though only on the first date (I.E 22/09/09 – 25/09/09 – The first date is blocked but i can still double book the room via 23/09/09…) so I need the entire date range blocked out and I’m helpless at writing or even attempting to change VB Code…

3: Booking rooms and checking availability is great for single rooms, but I have realized that for dorms (upto 10 Beds) then once I book one person in, then I won’t be able to book the other 9 beds, which is a pain! Is there a way to have the room list but also a bed list for each room.
So for instance Dorm 1 has 4 beds – I book 1 person in it and it still appears as available until I book another 4 people in it, then it blocks me from booking someone else in the room?

View 4 Replies View Related







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