is it possible to display the files open (the contents of the Windows drop down menu from standard toolbar) in a display that can be viewed at all times on a workstation. My reason for this is to monitor the files I have open to avoid trying to open a second copy and getting error reports from macros within. These error reports seem to frighten some of the users here and rather than read them and act accordingly they go for coffee and say thier system is down. It is my intention once I can get a system working, to introduce these people to monitor what they already have open, and more importantly what they do not need open at any particular time.
I want to create a macro that will “open the look in list” and stop so I can pick a file to open. I’ve tried to use “record a macro” and “ctrl-o”, but the record a macro won’t stop until I pick a file or cancel the file list. I also tried to use “o” in the short cut key box
I have a tool file which contains Excel VBA codes. When I click button it asks to select a file. I want that anytime I click the button the dialogue box should open in the folder containg the tool.,
I have two workbooks. One is a no-nonsense form interface that my bosses will use to enter safety information. I'll call this workbook "Form". This file is stored locally on each of their computers. The other workbook is stored on a common drive. I'll call it "Master".
When my bosses fill out the Form and click "Submit", the Master file is opened, and certain cells are populated based on information entered in the Form. This is the code I am using to make this happen:
Dim myname As String myname = Application. GetOpenFilename
I use it to attempt to open first a QuickBooks file and then an excel workbook. The Quickbooks file opens fine. The Excel workbook never appears to open but I do get the full file pathname to my excel workbook returned which I then parse off to get just the workbook name.
Why would it behave this way? I open both these files manually all the time.
Also, I'm trying to use a filter as follows:
myname = Application.GetOpenFilename("*.QBW")
I get a compile error saying that I'm calling the function wrong.
I am trying to open a website, then for excel to download the csv file, then for it to save it in a specific folder under a specific name and file format (excel).
I am successful at opening the website with the following code, but how to do the rest.
Sub Searchez() Dim IE As Object Set IE = CreateObject("InternetExplorer.Application") IE.Navigate "http://quote.morningstar.ca/Quicktakes/stock/keyratios.aspx?t=clwr®ion=USA&culture=en-CA&ops=clear" 'load web page google.com IE.Visible = True While IE.Busy DoEvents Wend
'IE.Navigate2 "javascript:SRT_keystuts.exportcsv()" 'this is the name of the download link as from when i hover my pointer over download link. End Sub
Actually i've downloaded Tracking report of my SPO and this report is on 4-5 csv files (1 file per week). I have to gather data from these 4 files into one Worksheet. I have to do this with command button on my worksheet; with Open file dialogue box (i want to locate the csv files).
CSV files are something like that (I need only first 4 columns):
and my Worksheet is like that (With a command Button):
I want to import first 3 columns of csv file on first 3 columns on my worksheet; leave 2 columns blank and then import the 4th column... now for example i have done importing data from 1st csv file and the data is on 50 rows.. i click the command button again, locate the 2nd csv file.. do the same thing (import 1st 3 columns, 2 blank, then 4th) --from 51st Row-- and so on...
I need a VB code to open excel files located in a path (fixed path) by providing a part of file name through a input box for ex: I have some files located in my local drive as below
I do routine tasks every day which involves opening 3 different files and pasting data into my main file. The data is always in the same format, and the 3 files are saved in the same location (3 different folders through). The 3 files are saved each day and the naming convention is constant, with only the date changing. For example, the files are always saved in C:My Documents and the files are called test_05.02.2013.xls. Tomorrow the file will be called test_06.02.2013.xls and so on.
So each day I will be rolling a file forward and I want to bring in the info from each of these files based on the new day.
How to do 1, and I will do the others (because the concept will be the same).
I need to open an excel file based on a date mentioned in the file name, but minus 1 day. The file name is composed with Statistics_date_time.xls
Example: statistics_20140423_142754.xls
Once I need to open the file (only in the mornings), it has to be the one from the previous day to see the yesterdays performance. How can I create a macro that will look at the date in the file and take the one from yesterday?
This will be a part of a macro I'm making. The rest of the macro will just format the file and combine a few ones from different departments.
I've got a couple of formulas in a workbook which lookup values in another workbook. When I reopen the initial workbook the formulas results come back as errors (i.e. #VALUE!) unless the workbook it is looking at is also open. This is not desirable, does anyway know how I can lookup values in another workbook which are not open? The formula which is causing me problems is as follows:
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
When it opens the desired file, it increments the file name by 1 each time it is opened (via the macro).
Example; The first time it is opened you see the file name in the header read "P'Binder L&T Pages" for a moment, then it changes to "P'Binder L&T Pages1"... I close the file and open it again (via the macro), and see the file name in the header read "P'Binder L&T Pages" for a moment, then it changes to "P'Binder L&T Pages2"... The file name continues to increment on every opening until the PC is rebooted, then it starts at 1 again.
Sub OpnLTpages()
Dim wb As Workbook Dim AlreadyOpen As Boolean
AlreadyOpen = False
For Each wb In Workbooks 'Scan open workbooks If wb.Name = "P'Binder L&T Pages.xls" Then ........................
I have a workbook were a user will write down some info. Then I need to copy that data to a second workbook located anywere in the Hard drive. i used the function "GetOpenFilename" or the "Application.dialogs(xlopenfile) to ask the user where is the file (and open it). Now I need to get the name of that file. How can I get it?(the name of the file the user selected to open). P.S. I saw there is somewhere a "Print" function to copy data from a workbook to another closed workbook. I was planning to have both workbooks open and then just use several lines like:
I need a code to open a file automatically based on file name, for example if the active file name is "trial", then it need to open another file named "trial1" based on which the macro will run, because I have two set of files and I want to compere daily with two users. for example the file names in Set a- iron, cement, stone, sand where as the set b-iron1, cement1, stone1, sand1
What I would like to do is to open an excel workbook from a bat file in dos with one arguement.
so something like
start c:lacklist.xls 2315t
Once the blacklist.xls is opened I would like to have the macro to save the excel file as a csv file ignoring all the prompts and use the argurement 2315t as the directory to save it to on the j drive ie save to j:2315tlacklist.csv. Then close the workbook and continue with the rest of the commands in the bat file.
I'm guessing I have to use GetCommandLineA to read in the arguement but I can't find anything on the net that gives a clear explanation of how to use it.
I have a template file for ordering trafolyte and steel plates. I have added macros to this template file. The existing macros do the following (shortly described):
Macro 1: clears order Macro 2: update order date + send a read only file to the supplier of plates + save a read only copy of the file into one of three folders acc to info in one of the cells.
It's the Macro 2 I want to edit.
I want to add a "function" which copy a selection of data.column A to N from row 12 to 548 but only the rows where there is a value in column A.
Row 1 to 11 includes standard order info and Macro buttons. Row 11 includes the heading for order data.
For everytime someone click on the Macro 2 button in the template file, I want the selection to be paste into the first "available" row in a "Total list" file.
The "Total list" file may have to be open (or a function to open, paste selection and then close the "Total list" file may be added)
File and Folder info:
To simplify suggestions, the following file and path info can be used (I can change to the correct later): Template file name: template_order.xlsm Template file location: \servershared emplate
Total list file name: total_list.xlsx Total list file location: \servershared otal
Selection info:
The template file exists of a "general order info area" A1:N10 The column heading for order data is located at A11:N11 The selection to be copied is A12:N550 - But only rows where column A includes data (not empty). (If the spesific order consists of 14 plates than there will be item no 1-14 in column A and I then I want to copy A12:N25 (row 25 will be item 14).
When I try to use record macro it looks like it only records what's happening in the template file - It doesn't record the pasting in the total list.
I have a textbox that constantly shows the contents of cell B1, a changing cell, as it is a total formula (to make my textbox display the contents of cell B1 I clicked on the textbox and then entered =B1 in the formula bar).
Trouble is, my textbox scrolls with the rest of my sheet and soon disappears from the screen. I need it to float on the screen and remain always visible when the sheet scrolls far up or down. How can I achieve that?
Or is there any other floating object that will constantly display the changing contents of cell B1? I’ve tried a textbox within a UserForm (which does float and remains always visible) but I cannot make that textbox display the contents of B1.
I wish to have a floating link in large spreadsheets that will always be visible and when clicked will return to a particular cell in that same spreadsheet.
Is there any code available for a floating Message Box instead of Standard Message where by the user can scroll the sheet and decide to choose "Yes" or "No".
if it is possible to create a floating Tab like you get on websites, so ehwn you move the spreadsheet to the left or right, the tab/shape with words in moves with you.
I was asked by a co worker if there is a way to make a data table float b/w tabs or in other words, it would always be visible no matter what tab or location you are in a workbook.
My first answer was no, that he would need to create that table on each tab and freeze the panes so that it was visible no matter where he was in his workbook.
When I click on the "Office Button", then in the recent "documents dialog box" there is a list of my worksheets plus an "allsenatename" file". This then downloads a worksheet from a website. I don't know how it was added to my list, I must have entered it when looking at that web site in error.
i have many excel workbooks in a folder i want a macro that will get the names of all the files and make the file names so extracted as a hyperlink to open the files.
cell A1 contains the numeric value: 3961.3 cell A2 contains the follwing: ="z equals "&B3-3933.7
which i would expect to give me: "z equals 27.6"
BUT, it gives me: "z equals 27.600000004"
which is awefully strange becos the 4 comes form nowhere...
what makes some sort of sense is that if the value in A1 is changed to 3961.4 or 3961.5, then the final decimal 4 in A2 changes into a 3, and then a 2.
when the value in A1 is changed to xxxx.7 the number in cell A2 becomes what one would expect it to be.
how do i get rid of these strange decimals???
(formatting cells, setting number of significant figures does not work. i naturally already tried that)
