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...
Firstly... using VBA i'd like to disable any save function to everyone other then me and a colleague on a spreadsheet (The one below).... Is it possible that just the two of us will have the ability to save and no other user?
Secondly... On opening one work book... in this case "Productivity Test" i'd like it to simultaneously open "Productivity Test Back End" both saved in P:MI TeamNew Prod with the back end remaining locked totally.
The reason behind this is these two spread sheet's are linked via sums, productivity test uses the back end as a kind of data source so i'd like "Productivity Test Back End" locked so they cannot see it/amend it..
Maybe i could use a bit of code that updates the link everytime something is used as opposed to having the back end open at all...? What do you think?
The last thing, i have alot of macros that run quickly on my PC, if my manager opens productivity test and uses the spread sheet it appears to run alot slower.
There are alot of tables in Productivity Test that are hidden and only appear when needed while everything else remains hidden. The example below is one buttons code..
ub Selectdsrtablenew()
'Select the correct table for users to view DSR detail'
Application.ScreenUpdating = False
Rows("23").Select
Selection.EntireRow.Hidden = False
Rows("26").Select
Selection.EntireRow.Hidden = False
Rows("36").Select
Selection.EntireRow.Hidden = True
Rows("47").Select
Is there any way of stream lining the code here to make it smoother as it appears quite a few times in the report for different buttons.. Maybe defining table names?
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"
[Code]....
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?
View 12 Replies View RelatedI 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.
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]....
I'd like to create a macro which will
-go to a given folder
-open each of the workbooks in the folder, one at a time
-after opening the first wkbk, go to a specific cell address on the first tab of the wkbk
-paste in some values and formulas
-save the workbook
-open the next workbook, and repeat as above, for all workbooks in the folder
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.
View 9 Replies View RelatedI 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.
View 2 Replies View RelatedI'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:
View 5 Replies View RelatedWork 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 have a macro that at the moment just opens a workbook SpreadsheetA.xls,
Sub OpenA
Workbooks.Open Filename:=C:DocumentsSpreadsheetA.xls, UpdateLinks:=0
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 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
The same two workbooks open when I open any other workbook. They also open up when I open Excel by itself.
View 9 Replies View RelatedI need to remove hyperlinks across probably 30-40 workbooks, is there a way of doing creating a macro that would do this across many instead of 1 at a time?
View 9 Replies View RelatedI have this code...
Code:
Sub Macro1()
'
Sheets("Log").Select
Range("F1").Copy
For Each wb In Workbooks
If wb.Name Like "Test Book*" Then
wb.Activate
Sheets("Data").Select
Range("O2").Select
ActiveSheet.Paste
[code].....
can't get it to work properly...Basically I want the code to copy the date in cell F1 of the 'Log' sheet in Workbook 'Main' - then goto an open Workbook called 'Test Book' (note: this Test Book is a partial string name used hence the other code around it) and paste the copied date into cell O2 in sheet 'Data'
Using this pasted date in cell O2 carry out the required filter function.
The copy/paste of the date from my Workbook Main to Test Book isn't working so the code then shows error when trying to filter the data using the pasted date.
I am trying to simultaneously use two Excel workbooks that are open at the same time--one is calculations--other is data scenarios...one worksheet = one scenario: A macro uses "Set" to specify workbooks for "current_wb" and "wbkFrom". Code here
Set current_wb = ThisWorkbook
Dat_Fil = Application. GetOpenFilename
Workbooks.Open Dat_Fil
Set wbkFrom = Application.ActiveWorkbook
NOTE-Workbook object variables are declared outside any macro, and as "public." Code here
Public current_wb As Workbook
Public wbkFrom As Workbook
In a second macro I want to use the Current_wb and wbkFrom object variables I set in first macro, but get the following error: "object variable or With block variable not set" for any of the following statements in the second macro
current_wb.Activate
current_wb.Worksheets(1).Select
wbkFrom.Activate
I've declared the object variables as public in a different code module where there is no other code; I've tried declaring as public at the top of the code module that has my macros, but declared at top of code module and outside any macro; I've tried declaring the object variables as "static" variables (instead of "public") within the first macro. I can't figure this out.
I have written a 2007 workbook which contains 4 simple macros. One of the macros automates the process of saving the print range as a .pdf file. It works fine on my pc but when I send it to others to use, when they try the macros, they all return a 1004 runtime error.
View 6 Replies View RelatedI have created a workbook with vb code that include 4 modules, 1 userform. These all work in the original workbook. When I copy and paste the workbook onto another laptop, none of the scripts work? Both laptops run the same os plus office 2003.
View 2 Replies View RelatedI have a need to open a file from my companies intranet. My current method was to open said file via the method that the recorder gave me. However, I would like ot be able to open a said file without having to start open another workbook.
This is the path:
[url]
So the command is this:
Workbooks.Open [url]
Links are not actual links
So what I need to know. Is how can I open this file without opening a workbook. I haven't been able to use the VB "Open Statement" to open a file and I don't believe that I've been successfull using the Filesystem object either.
i need to work out a formula for my spreadsheet which I use to work out cutting lists for timber frames. I need it to work out if the width of a job is for eg 2400mm i need to work out how many timber studs I need so the space between each stud is between 400mm and 500mm and this will need to work for a range of different sizes of frames. I have it written at the moment and it just devides the width by 400 and gives me a amount of studs but it would work much better if it could space them between 400 & 500.
View 4 Replies View RelatedIn a project i am compiling i need to work accurately with times to calculate the work progress of the people in the workshop thus....here goes....
I have in work book #1 (7) sheets mon to fri + complete week + a sheet where all job numbers are collected.
From monday to friday the workmen log their times as a start time and a end time. This has to be then calculated to a total hours:mins spent per job, wich in turn then has to be calculated to a total hours:mins spent per day. And the on the complete week sheet recalculated as a total time worked per week.
i have a made a macro that copies info to a new sheet now that is working great but if i change the name of the work book it wont work any more so i need the macro to work with what ever name i give the workbook
the current name is
AVERAGE PRICE (update 2009) Mimmos Armico 170809.xls
i have attached the code in notepad ...
In column J, Cad Hrs. How to make to display (0), instead of (-0,02) when I just do other work than Cad Work? I need to separate type of work according to price hour, overtime, and so on.
View 2 Replies View RelatedI have a range of cells in a work sheet "sheet 1 " my objective is to filter this range according to certain criteria (i ve succeeded to do this ) yet what i want to do now is copy this data to another existing worksheet in a certain range .
note :the existing worksheet to which i 'll copy the filtered data has some cells out of the range that i dont want to over write ..
Simply :how to copy a selected range of cells in a work sheet to already existing work sheet in a specific range aswell .
I have 25 files with certain worksheets that I need to move to 25 other files.
Worksheet 1, 2, 3 and 4 in Workbook A needs to be moved to Workbook A-2014 Worksheet 1, 2, 3 and 4 in Workbook B needs to be moved to Workbook B-2014 Worksheet 1, 2, 3 and 4 in Workbook C needs to be moved to Workbook C-2014 etc....
Is there a way to do this with a macro? Preferably I would like to do this automaticly - i.e. runing the macro from a master file that
1. Opens Workbook A copies the worksheets
2. Open Workbook A-2014 paste the sheets
3. Save and close Workbook A-2014
4. Close workbook A without saving
then doing the same for Workbook B, etc.
Basically the main workbook opens 2 files at a time performs a calculate in the main workbook and then copies and pastes information in 3 ranges. Then closes the two open workbooks and loops and performs same operations until it hits the maximum loops. My macro is as follows and I have 3 question in capital letters.
View 3 Replies View RelatedI am want to copy a formula across several work sheet and have the formula always take data from previous work sheet.
2) I am working with this formula =C12+INDIRECT((MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256)-1)&"!"&"C12")
and it comes from this thread http://www.excelforum.com/excel-gene...orksheets.html. I have included a worksheet attachment that has explanations
How do you open IE6.0 from VBA
I tried:
Dim IntApp As Object
Set IntApp = CreateObject("Internet.Application")
With IntApp
' Change file name to suit
.Documents.Open Filename:="http://web addr/"
.ActiveDocument.Select
.Selection.Copy
End With
Get error: ActiveX component can't create object