I would like to read a few custom properties of excel workbook, even if they are close.
My intention is to build a list of compatible workbooks.
The compatibility woul be determined fom a custom property.
How can I read a custom property from a file wothout opening it?
A custom function as shown below is not reading in the value of the parameter Ttorefinance which is an Excel cell value (actually a link in the spreadsheet). Eg when Ttorefinance cell value is 13, the function reads in 0.
Function MarginbyTranche(TfromIssue As Double, TtoRefinance As Double, AssetType As String, Tranche As String) As Double
Dim RmbsAaaMargin As Double
Dim RmbsAa3Margin As Double
Dim RmbsBaa2Margin As Double
Dim CmbsAaaMargin As Double
Dim CmbsAa3Margin As Double
Dim CmbsBaa2Margin As Double
Dim PsAaaMargin As Double
Dim PsAa3Margin As Double
Dim PsBaa2Margin As Double
See custom document properties for a give workbook by using File, Properties but where do sheet custom properties show up.
View 9 Replies View RelatedIs 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 RelatedI need a little bit of help here ... I can read and process a .txt file from my Excel VBA program, but I am having trouble as follows :
The file I am reading contains a number of heading lines, followed by a number of data lines.
All of the data lines end in a date, so I can use the code ...
If IsNumeric(Right(MyDSRec, 1)) Then
... to pick up the data lines & ignore the heading lines. This is fine and works perfectly.
However, the .txt file that I receive is out of my control, and is prone to error, in that the data line occasionally has what I assume is a "Tab" character after the last date, which means that the line above does not recognise the last character as being numeric.
If you double click "06" at the end of each line on the example below, you should be able to see what I mean (in the .txt document, if I place the cursor at the end and press backspace, all characters at the end are deleted, back to the 06, so they are not individual spaces) ...
RAMSEY1129/0503/0603/0613/06
SHAKESPEARE 1102/0609/0609/0618/06
PREMIER603/0604/0605/0618/06
I am running into one of the strangest issues I have seen in a long time. I have a user defined type that I utilize in VB6 and write out to a binary file....
View 7 Replies View Relatedi am trying to read the file name in a specific folder through macro of my EXCEL application. i wondered that it is reading that file name in all Lower case.
Ex: the file name in the folder is "Ford of Pompano.xls" when i am reading that name from macro, it is giving me as "ford of pompano.xls". Do i need to change any default settings in EXCEL so that it can read the files names what ever is presend in the folder
I need to modify/edit the properties of a CSV File. (Ex: Title, Subject, Author, Category, Keywords, Comments) through VBA
View 5 Replies View RelatedI have a .csv file that contains numerical data in the form of strings, much of which starts with leading zeros (i.e. "012345"). I double checked to make sure the .csv file has the leading zeros and that they are wrapped in quotation marks in notepad, and it does. However, when Excel opens the file, it must read those strings as numbers, becuase it chops off the zeros. Does anyone know of way to fix this? Can Excel read numbers as strings straight from a .csv file?
View 3 Replies View RelatedI am running a DOS command to create a directory listing of ONLY DIR files with the short dos filenames and the long filenames. The command used is
DIR /A:D /X /N /S > c:sampledata.txt
Once read into excel, I simply want to have a spreadsheet with the following:
Directory Path Name, DIR Short Name, DIR Long Name
Anyone have some code to already do this? I have attached the sampledata.txt file to go with the pseudo code.
I want to read in a space delimited file and to put each line in one cell starting with cell B2 (I will take care of splitting it into columns later). However, rather than reading one line at a time, it reads in the entire txt file at the first pass......
View 5 Replies View Relatedwhen i run this code under a button on a sheet it works fine. but i would like to run when the workbook opens (but i get an error message
On Error Resume Next
Application. ScreenUpdating = False
Dim wb As Workbook
Dim ws As Worksheet
'create new work order number...
Set wb = Application.Workbooks.Open("c: empex_ExternalOrderNumber.xls")
Set ws = Worksheets("NumberIncrement")
'get new WO number from numberincrement.xls
If Err.Number <> 0 Then
MsgBox Err.Description & "...help"
Else
mynewnumber = ws. Range("b1").Value
Range("g5").Value = mynewnumber
wb.Close
End If
Application.ScreenUpdating = True
I have a code in file A that opens several files (B,C,D&E), copies some data from them, then closes the files. That part of the code works fine, but each of the files that are opened (B,C,D,&E) have a Workbook Open event that causes the file to save automatically every 30 seconds. (I know this is not recommended, but this is what the user wants.) The files also have a Workbook Before Close event that is supposed to stop the timer so the file will close without reopening. These each run fine on their own.But if I run code A, the workbook Before Close event in file B (C,D, & E) does not seem to run and the files reopen after 30 seconds to save. When I step through the code it works fine and goes through the Before Close event in each file and the files remain closed.
View 3 Replies View RelatedI have the need to be able to open a closed file to archive some data from a
current file.
what coding i need to be able to kick the "open file" bit off?
I have a folder with some 126 word document files.
What I need is to create list of file names in a column & its properties like Author, Date modified in adjacent columns of each file..
The problem with the Write macro was that, although it did work, it used SendKeys which has to be slowed down considerably.
Here is a new version of the Write macro that works as normal. I originally tried to use CDDBControl.dll version 1.2.0.51 which is widely available on the internet but found that I could only get it to change 1 file before crashing Excel.
I have put the READ macro in the next message.
Dim ws As Worksheet
Dim FromRow As Long
Dim LastRow As Long
Dim FilesToChange As Integer ' number of files to change
Dim FilesChanged As Integer ' number of files changed
Dim MyFilePathName As String ' full path & file name
Dim MyFileType As String ' mp3 wma etc.
'-
Dim id3 As Object
Dim MyArtist As String
Dim MyAlbum As String
Dim MyGenre As String
Dim MyTrack As String
Dim MyTitle As String
Sub WRITE_TO_EXPLORER()
Application.Calculation = xlCalculationManual
Set ws = ActiveSheet
Set id3 = CreateObject("CDDBControlRoxio.CddbID3Tag")
LastRow = ws.Range("A65536").End(xlUp).Row ' count worksheet rows
FilesToChange = ws.Range("A2:A" & LastRow).SpecialCells(xlCellTypeVisible).Count
If FilesToChange = 0 Then MsgBox ("No files to change."): Exit Sub
FilesChanged = 0........................
I have a question regarding the properties of an Excel file (.xls 2003). I can change them all (as indicated on Microsoft support website) expect the name of the last person who recorded the document (File ==> Properties==>Stat.)
How to change the name?
I have a list of excel and word files in a folder...200+ of them. They all have the Author, Subject and Category populated under the File- Properties menu. From browsing through the site, I came across bits that suggest it is possible to do the following: Code that will run through a list of word/excel files in a designated folder, and extract the Excel(or Word) filename, Author, Subject and Category into a new excel file as a list?....................
View 6 Replies View RelatedI was looking for a way to get external file froperties into a cell!
for example:
Last modified date.
this is what I allready found/tried.
I want to amend the ' Date Modified' for a spreadsheet. I have just made changes to it - so the 'Date Modified' shows today's date and time. Is there a way of changing the 'Date Modified' so it shows as yesterday's date?
View 3 Replies View RelatedI have to excel files
1. Temperature & Humidity Reading
2. Summary
I tried to copy a date from Temperature & Humidity Reading file using the formula ='[Temperature & Humidity Reading.xlsx]Sensor 7'!$C$2 to Summary file
if the date format is like this 6/10/2013 12:00:00 AM the result is fine, but when i try to copy that formula for the succeeding dates the results is same from what i copied.
I am trying to summarise data from 50 different files (updated by 50 different users) into one file in one place. To do this I created a simple sumif function that links to those files. This sumif works correctly when those files are open. But when I open my output file after changes have been made to one or more of 50 input files, sumif function doesn't work when I hit update at the file opening pop up question and get #value error. If I start opening those files this error goes away and it works great again.
View 2 Replies View RelatedI want to copy the sheet named "data" from closed workbook (xlsm, xlsx, xls) via ADO.The closed workbook will be choosen by user via dialog box. The code must check the existence of "data" sheet. If there is no, then the code must give a warning. Futhermore, which is able to give a warning against the mistake of selecting the file itself.
View 1 Replies View RelatedI have 2 workbooks, and want to copy an entire worksheet from 1 to the other workbook.
In the closed workbook, I want to copy "Sheet1", and paste that into the open workbook on worksheet "Original File". What code do I need to complete this?
i know there is information on this ontime method, i haev read through it, but since i am working with reuters running live in the background they just dont work.
Anyway, on a normal excel sheet, I have made the automated macros run on a timer, which is great. BUT If someone switches off my pc, its not going to be so great because i dont think the macro would run without excel being open.
QUESTION:
How can i make my macro run every morning at 8am? Assuming the worksheet is closed, even excel is closed. i am sure the answer is starring me in the face, i am just getting boerd of looking at it! in the Thisworkbook:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.OnTime dTime, "thismodule", , False
End Sub
Private Sub Workbook_Open()
Application.OnTime Now + TimeValue("20:00:00"), "thismodule"
End Sub
and in the Module:
Sub thismodule()
dTime = Now + TimeValue("08:00:00")
Application.OnTime dTime, "thismodule"
I'm trying to find a way to import data from an external excel file that the end-user will ultimately end up choosing using the application. getopenfilename dialogue. I'm able to sucessfully open the file, but what I'm really trying to do is import data from one specific column within the file, preferably without the source spreadsheet popping up.
So, is this possible to do with VBA? Also, will I run into any issues if I try to import from an Excel 2.1 file to an Excel 2003 file using VBA? Finally, I need to be able to import one file, and then be able to add to that an import from another file. I'm thinking that this could be accomplished using a variant variable, but is there already a function within Excel so that I won't have to create my own add-to function.
I have a workbook with a button to get updated data. The button simply opens the other workbook and then closes it. THe problem is it opens the book and then closes it before the opened book has a chance to finish it calculation resulting in lots of #value errors. The book that is being opened works fine but because it is shared it can't be left open and needs to be closed asap, though it should be allowed to finsih calcs before it gets closed again. If anyone know a way to make the following temporarily halt until the opened book finishes calculating before it closes it.
Workbooks.Open Filename:= _
"X:gas daily pricingGD pricing weather call active summer.xls", ReadOnly:=True
ThisWorkbook. Saved = True
ActiveWindow.Close False
I have a file created in Excel 2003 that uses a vlookup to reference another file, also created in Excel 2003. The function returns VALUE when the referenced file is closed. I get the VALUE error whether or not I update links upon opening the file. If I open the source file, the function calculates properly.
Here is the formula when the referenced file is open:
=VLOOKUP(A38,'[June Scorecard confirmation.xls]VPSC Summary'!$C$10:$F$22,4,FALSE)
The full & correct path appears in the formula when the referenced file is closed.
Columns C, D & E in the referenced range contain text, column F contains a Sum. This file is linked to another file, also using lookups. The linked area in this file works without problem, but it is a precedent to the calculation in column F.
Both these files reside on a network in subdirectories of the same logical drive. I am using a laptop that briefly had Excel 2007 installed on it, but was wiped clean before it was issued to me.
Herewith I attached a sample excel file.
Structure file which i attached herewith include all the cost center details. normally it is a big file contain more than 1000 cost center details. Structure File.xlsx If i want a cost center name for some cost center code, i have to open this file and find a specific name.
What i plan is develop a macro function called "CC".
If I type =CC("D232") I need to print cost center name for D232.
But the problem is i don't want to open the structure file, without open that file, when i type this function i need a result.
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.