What I'm trying to achieve.I'm trying to create a toolbar to have visible in excel which provides me with shortcuts to a selection of functions and some other useful documents.
I've created a document "Toobar 1.xls" in column B of which I have the file locations of the documents I want to open, the documents are moved from time to time and it is easier for other users to update the spreadsheet than vb.As you can see below I've written a piece of code to open up a document, I've created a function because there are a few documents I want to create links to and thought it would be cleaner to do it like this. This code is stored in "Module1" of "Toolbar 1.xls".
I've then created a new toolbar in excel and added a custom menu item and allocated it the macro "Pension_Credit_Calc".
When I run the code from within "Toolbar1.xls" it works fine, however if I try to run it from another workbook I get "run time error 1004". I suspect it's probably something to do with where I've stored my code,
PS. The reason I have not simply created a shortcut in the quicklaunch bar is that I will also be altering the workbooks once opened.
Code.
VB:
Function OpenUp(FileLocation) As String
Workbooks.Open (FileLocation)
End Function [code]....
End Sub SpreadsheetA.xls has a macro called "ExpectedRun" which is currently operated by clicking a commandbutton.
What is the code to run the "ExpectedRun" Macro in Sub OpenA?
Eventually I'll be opening a bunch of these workbooks, each with its own unique "ExpectedRun" macro, so is there any special command to call on the "ExpectedRun" macro for specifically the opened workbook?
I have a spreadsheet that I need to open two other workbooks to copy and paste data from.
The name of the two workbooks changes each day, and the location because they are filed in subfolders.
I have the following code, which works to open the first sheet but then comes up with the following error when trying to open the second sheet. "Run-time error '1004: Excel cannot access 'Hub PVA'. The document may be read only or encrypted".
The folder is not encrypted because when I was only opening the second document with the same code, it worked.
The code I am using is: 'DECLARATION On Error GoTo ERR1: ChDir "G:GENERALTRANSPORTDAILYPLANS" Workbooks.Open Filename:="G:GENERALTRANSPORTDAILYPLANS"
Is there a way to use a for next loop to open a workbook that is in a folder, then save and close the workbook then open the next workbook in the folder and do the same routine till all the workbooks have been updated?
I am making a macro and I need to extract some data from workbooks. However, I have many workbooks and it would take time to the macro to open and analyze each of them.... is it possible to work with workbooks without opening them?????
For instance, I need to open a workbook and get all rows that contain the word HEXXX..... However, I have about 30 workbooks and it would take forever...
I have a Sharepoint site which seems to be set up incorrectly as I cant even do a webquery with it, or sync Excel with it.
I want to be able to open up the Excel workbooks held in this one dir within Sharepoint take some data out of it pop it into another Excel workbook and move on down to the next one!.
So far all the code I have managed to locate causes errors, so I have managed to get a feed from the Sharepoint site which gives me a hyperlink to all the docs (as these can change). What I now need is some code to run through all the hyperlinks open the doc (if it ends in xls or xlsx as some are PDF's!) take the info copy it out and move on to the next one.
I would like to read data from Worbooks through VBA code and UserForm without opening them. Is it possibe?
I know I can read data from Workbooks without opening them if I write this formula directly into a Worksheet cell:
='C:Documents and SettingsUserNameDocuments[FILE_NAME.xls]Sheet1'!$A$1
The problem is it won't work for my task. I just need to analyze with UserForm Controls and For Each loops through many cells and many big Workbooks.
If I try:
Private Sub UserForm_Initialize() Dim aaa as String aaa = 'C:Documents and SettingsUserNameDocuments[FILE_NAME.xls]Sheet1'!$A$1 End Sub
OR
Without opening the file if I try:
With Workbooks("C:Documents and SettingsjohnMy DocumentsmProjectfilename.xls").Worksheets("sheet1") For Each thing ... .... Next End With
In both cases it gives me - Compile error: Syntax error, Expected expression.
I can also say that I definately don't mistake path, workbook's or worksheet's name ------------------------
If you're not quite sure that my explanations are clear I wrote some more below:
Reasons i want this: It's just a lot of traffic/memory to Open/Activate/Close many Workbooks/Worksheets from 10MB files for just taking one or a few values to an UserForm :->
What I do: I'm using several Workbooks to manage data. One of them consists of the Macro code (ThisWorkbook.) and the rest of the Workbooks are just tables with data I use. On UserForm with controls such as (ComboBoxes, CommandButtons, ListBoxes, Labels etc.) I find, visualize, compare and copy just the data I need from other Workbooks to ThisWorkbook(on a new worksheet) :->
The idea of my task is: 1. I have 10 files, each of them 10-20MB with data. 2. Now, I'm making 1 file with VBA code which analyse these 10 files and displays the information in it.
The general idea is that these 10 files are well structured but have many columns and rows and it's not convinient each time I need to find 3 numbers I need to open 10MB file, scroll 5 minutes, copy/paste the numbers, close the file...i just want an UserForm with several controls to do this job
Each of these 10MB files consist of let's say about 10000 rows and 100 columns. Between these rowns and columns there's data (or empty cell). Everytime I use one of these files I need different information. So, if I use the formula you gave me, I should copy exactly the same Workbooks in one file but with formulas.
With the macro code I just go through these rows and columns and find the specific data I need with For Each loops. Everytime I need different data. With 4 ComboBoxes I just select an year, a name, a code (by rows) and another code (by columns) and I get the needful info.
This info could be in one file, could be in many files, it could be in HZ45 cell (file 1), it could be in C5 cell (file 2), the next time I would need file1,2,3,4,5 with cells GF45, J7, KK88, DR8, Y90 etc. etc. etc. So, the problem is that every time I need different information from these 10MB files, each of them consist of 10000*100 cells*10 files = 10 0000 000 cells which I have to write the formula you gave me and it will become one 100mb excel file, which is no good.
I have 2 workbooks open and I want to set up formulas from one linking to the other. When I toggle on the task bar between the 2 workbooks they replace each other within the same window so I can only see one workbook at a time. How do I set it up so I can have 2 separate windows open at the same time, one with each workbook in it? I tried 'side by side' in one window but the viewing area is too small.
I have a problem when opening Excel work books, The Personal file will not open automaticly. The file is in the XLStart folder. The link is "C:Documents and SettingsUSERApplication DataMicrosoftExcelXLSTARTPERSONAL.XLS. I have a desk top short cut I need to open first manually. Also the menu bar short cuts I had to my VB code and Macros will not work. Any body have any ideas what the problem may be? Im sure its a simple link problem but I don't know where to look.
I'm trying to develop a macro to amalgamate some data on currency exchange rates that are stored in different files with the dates saved in the file's name. The code of the macro I recorded when opening an example of such a workbook is:
Work have just upgraded from 97 to 03 (very with the times ) and when opening multiple workbooks, 03 behaves differently.
With 97 I could select many sheets from the open window and excel would open them in alphabetical order. Now we've moved to 03, the order seems to be completely at random. It's a real pain, as the set up of the workbooks we use has everything linked together. In order to keep everything working sweet, various groups of workbooks need to be open at once. Previously it was easy to find a workbook in the task bar, but now they're scattered everywhere. We don't use a set list of workbooks - the selection we need to open varies quite a bit, but there's usually at least 20 or so.
I was wondering if anybody could catch an error in the following code. I have a file, let's call it a "Guide" which is opened by user and at this time this file has only one purpose - to help user choose the program. After clicking on the "Choose Program" command button, an Inputbox appears where the user types in a program. Then the code should open the file with this program and this is pretty much it. But something is not working with the code below, and I do not see anything wrong with it after looking at it for so long. It just does not do antthing. how to handle the "Choosing" option
Private Sub CmdBttn_ChooseProgram_Click() Dim sFilename As String On Error Goto ErrorHandler sFilename = "Approval_" & Application.InputBox("Input Program") Workbooks.Open Filename:="C:Documents and SettingsjsmithDesktop" & sFilename & ".xls" ErrorHandler: 'if file name does not exist error message should display. Not defined yet End Sub
which of these toolbars provides the better 'controls' to paste onto worksheets (as opposed to UserForms)? For those wondering, both toolbars contain some apparently similar controls, e.g., combo box, radio button, spinner etc. but there are differences in their behaviour it would seem...
I've created a custom toolbar via Excel's toolbar wizard, to which I have added several macro buttons. However, when clicked, instead of running the assigned macro, Excel instead attempts to open my already open file?? I think I read something about having to manually change the buttons to refer to a .xla ??? Not sure though.
I have created some macros that need to be used in several workbooks by more than one users, so I put them in an add-in and gave them instructions on how to get the add-in. I also created a toolbar with buttons calling those macros, attached it to a workbook the users can open, and instructed them to open that workbook once to get the toolbar.
Both the add-in and the toolbar workbook are on a network drive that my users can access, but when they get the add-in, it gets copied to their own add-in folder.
Here's my problem: When a user tries to click on one of the buttons in the toolbar, the button tries to call the macro using the path that would be valid on my computer to get to my add-in folder. This path includes my user name, so it doesn't go to their own add-in folder: it goes to my add-in folder on that computer if I ever logged in on that particular machine, or it goes nowhere.
When I created the button, I attached the macros by referring to the add-in workbook by name only, without referring to a path, so I don't know why MY path ended up defined on that toolbar.
How can I go around this? I manually relinked all the buttons for that user, and I can do it for the other users (there aren't that many users nor that many buttons), but I'd rather avoid it: I'll have to re-do it if the toolbar is updated and it just seems like more work than should be necessary.
I'm also opened to suggestions on a better way to share those macros. They can't be attached to the workbooks because we use new workbooks every year - they're emailed to us by the people who create them and for a variety of reasons, pasting everything into a workbook of our own is not a good option.
When I try to record macro, the small toolbar which has two buttons "stop recording" and "relative reference" do not appear. I need to turn relative reference on. Can you please guide me how can I make it visible to turn relative reference on?
I have a custom toolbar containing several buttons which run several macros. Works fine except on exiting the programme and re-opening the workbook, if I try to use the custom buttons I get an error message which tells me it cannot find the ' name of workbook, name of macro' macro I then have to re-assign the macro which has been there all the time but without the file name in front of it.
I have written a macro. The macro in question simply shows a user form. There is far more code within the user form itself. It is designed to automatically format a workbook.
I saved the macro as an .xlam add-in file to the default folder. I then added a button for the macro to my Quick Access Toolbar. This seemed to work great.
However, when I open a new file that I want to run the macro on, the button disappears. In fact, a 2nd Excel window opens up on my task bar. The original window still has the macro button, but the new window does not.
I have finally come up with a macro and would like to install it to around 10 people's "Personal Workbook" in my department. Let me explain a bit further...
We use web-based software which has an "Export to Excel" option which we all use. The resulting data populates into a spreadsheet automatically.
I would like the user to be able to click on Tools/Macro/Run Macro and then run that particular macro. I assume that this macro should be in the "Personal Workbook" so that the macro will be visible no matter what worksheet they may have open?
Can this be done automatically/with a macro or must this be manually done?
I need a macro to open when opening an excel workbook. Viz: When a workbook is opened I would like it to immediately display an 'info/warning' box with some basic advice on how to operate the sheets, once read click yes (continue) and do what is required.
I'm looking for a command for a macro, haven't been able to find it thus far. Is it possible to get excel to open a .txt for example, and copy its contents into excel at an activecell?
I'm attempting to write a macro (in Excel) that uses a path that is given in one of the fields of the worksheet to open a workbook and get find a field to return to the original worksheet. I have working code to pull the information from a worksheet within the same workbook, but I can't seem to get it to work with an external worksheet. Can someone assist me in getting this to work?
I have attached my original code that works, so all I need to do is translate it to open the external workbook and worksheet instead of using the local worksheet. This is pretty simple right?
Public Function GetRate(strClassification As String, strContractNo As String) As Integer
I would like to something arranged where a macro I currently have assigned to a button is automatically run once the workbook is opened. The reason being because I would it to be current with todays date. I have done the following and it results in nothing I still need to click the button to get the macro to work.
