I download excel report from Business object reporting tool and the file will be very huge something like 25MB and it takes nearly 40 Min to open the report and the data will be merged in the report......can any one suggest what can be done to open the report quickly or can we open the report by using macro.... can we improve the performance of opening the 25 MB report thru macro...
View 9 RepliesHow can I get VBA to recognise if a certain report is already open?
Basically, what I want to do is if the report is open then just copy the sheet across else if the report is not open the open the rpeort and copy the sheet across.
I'm only struggling with the first part, I can get it copying over fine but its the "if the report is already open" part I can't get my head around...
I want the work book to open on the "Weekly Report" sheet.
I have this code, but keep getting a subscript error.
Private Sub Workbook_Open()
Sheets("Weekly Report").Select
End Sub
I have a macro that takes the date from different excel sheets, consolidates the data and renders the pivot table and chart accordingly. It was working fine when the date range defined for the pivot table was static. Now I have made it dynamic since the data range changes each month depending on the number of days it has got. When I run the macro, it runs succesfully, generates the report and save & close the report, but after that I am getting this error. Errorneous
View 2 Replies View RelatedI have written two VBA programs around the same time. Both run on open and pull external data and create graphs. My problem is that I want the end user to be able to run the report multiple times by choosing the name of the macro from the Excel macro menu (i.e. Tools>Macro>Macros) but only one of the workbook macros shows up on the menu. why the other macro is not visible on this menu???
View 2 Replies View RelatedI 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
View 5 Replies View RelatedI have two worksheets in my report cards:
1) Data - the students are listed in Column A, math scores in Column B, reading scores in Column C and science scores in Column D. The grades of 300 students are entered in this sheet.
Student
Math
Reading
Science
Jimmy
75
84
100
[code].....
2) Report Card template - This is the report card that needs to be generated for each student. It's pulling the student name and grades from the Data worksheet.
Student Name
=Data!$A2
Math
=Data!$B2
Reading
=Data!$C2
Science
=Data!$D2
How do I create worksheets (report cards) for additional students? I have 300 students in the school. I need the next worksheet to reference Data!$A3. I know how to cut and paste the report card template and then edit =Data!A2 to be =Data!A3 to create a report card for Sally. How do I create the 300 report cards I need?
I've been teaching for 13 years. I can create a report card for each student in my class and edit each worksheet individually for each student. Now I've been asked to do this for the whole school and I don't know how to create the other 299 sheets I need.
I did a macro on my mac to transfer a sheet from one workbook to another worbook. It works very well when the destination workbook is open. Therefore I wanted to add some piece of code to check if the destination workbook is open. If not then I wanted the macro to open it before tranfering the sheet. Here is the code I´m using for tranfering the sheet
Sub Transfer_Sluttet()
If ActiveSheet.Index <> Sheets.Count Then
Application.DisplayAlerts = False
Set ws = ActiveSheet
Sheets(ws.Index + 1).Delete
ws.Move Before:=Workbooks("Sluttet.xls").Sheets("sheet2")
'Moves active sheet to beginning of named workbook.
'Replace Test.xls with the full name of the target workbook you want.
Application.DisplayAlerts = True
End If
End Sub
This is the type of macro I useually use on my pc to check if a workbook is open and if not then open it
If IsWorkbookOpened("Filename.xls", "C:Documents and ..................
How do you create a macro to copy the information from my weekly reports to a monthly report and be able to update automatically. If you had 4 worksheets (for each week of the month) and 1 mastersheet for the whole month in a workbook. All titles are the same and If you needed to copy all the data that is in the columns, say, A through I, starting with row 4 to however many rows are in a given week. The reports can be made up of numicerial values, text and dates. Let me know if more information is needed or an example worksheet.
View 4 Replies View RelatedI have a macro (see attached) that works fairly well, but the one thing I cannot figure out how to do it how to merge cells whenever there is a break in Project ID.
If you take a look at the spreadsheet, you will see that columns B and C for the 2nd Project ID is merged just how I want it to look. The number of rows in each Project ID is variable.
I've got a basic handle on the application/use of macros, & personally use them for cleaning up data exported from a very very old as400esq application.
My problem is that i have managed to clean up all the blank data & piffle, but Im stupped by the header part of the text dump.
Heres an example;
A B C D E F
-------------------Header A-------------
1 3 john 50 piano
4 1 Bill 25 horn
6 5 Ted 60 trumpet
-------------------Header B-------------
3 3 Phil 71 Cello
1 1 James 83 Viola
3 2 Earnie 62 Violin
I basically want to rid the header rows from the spreadsheet & drop the header description (Header A/ Header B) into column F for all those items below until you get to the next header.
trying to do anything other than the basics with Excel. I would like creating a macro to import data tables contained in a report that I receive in HTML format.
The Report looks like the following. I de-identified the data
VRAAPCO1 VER 8.1 VANMANDY ADMINISTRATOR
PAGE 1 01201010 CONNECT SUMMARY CPU 046E16
REPORT DATED: JUN 12, 2012 17:53
INFORMATION AS OF: JUN 11, 2012 23:09
EXTRACT FILE(S) USED:
DDName=SVSAM DSN=SYSL.VRA.SVSAM.EXTRACT
DDName=MVSAM DSN=SYSL.VRA.MVSAM.EXTRACT
MASKING CRITERIA: MGROUP=FSCSC OR MGROUP=FSCORE OR MGROUP=FSTECH OR MGROUP=FSBSM OR MGROUP=FSCCS OR MGROUP=FSEMFE OR
[Code] ........
The column group represents a security profile for an application. I am auditing the access to that application. They either start with the '#' symbol or the '@' symbol.
I receive this report once a quarter. This being my first time. I had to manually cut and paste the data tables from the HTML report file into a spreadsheet.
This is what I would like to be able to do in excel using VBA. The report name is the same every time. 'FS.ASET.Users.htm'
1. Would like to create excel spreadsheet with a macro button and master worksheet that already has the headers.
Group Userid User Name Owner Create Date Special Operations
Auditor Revoked ADSP GRPACC UACC Authority
2. That looks in a network directory(will be the same everytime) for this htm file(will be same name every time)
3. Pull only rows that start with # or @. Place them into single worksheet with sorted by 'Group' ascending.
4. A stretch goal would be to have the macro create a different worksheet within the spreadsheet for each profile name, I.e.,@UFSGUAR, #ZCBSAU, and copy only those rows that start with that profile name into each subsequent worksheet.
I have n rows on a tab and I have counted them using Offset and Counta and put that count on B1.
I now need to make a little macro that print from row n to row number in B1. I've seen macros using things like "For i" "Next i", etc... but I do not know how to set the macro to start at a cell with a number n and repeat till the number in B1.
I have a very large workbook which includes 100 sheets (A) each containing fixed data relevant to one unit (n) (in this case n = a building; there are 100 buildings), a few sheets (B) containing parameters & settings, and then 1 main sheet (C) which does complex calculations pulling & combining data from sheets (A) based on the parameters in sheets (B). The workbook is so large, (100MB+) that it has to be set to calculate manually.
One of the settings in one of the sheets (B) dictates which building (n) is currently being looked at. As such, sheet (C) effectively builds a report for building (n) and so can report on just one building at a time, that building (n) being determined by a drop-down box listing all of the available buildings and that list is in turn defined in one of the sheets (B).
If I want to get a consolidated picture for all buildings I currently manually change the building choice (n) in the dropdown box, and then run a macro (m1) which updates the report to show what I want, selects the specific bits of the report I want and copies the selection, switches to a separate pre-formatted consolidation sheet I have created (D), jumps to the last row in that sheet, pastes values and then jumps back to Sheet (C).
I then need to manually change the selection (n) and then rerun macro (m1).
What I would like is another macro (m2), which automatically loops through the different values for (n) so that the whole process is automated. Then in a perfect world, perhaps even a separate macro (m3) which would give me a list of tickboxes so that I could select specific properties for which to run the report.
I need to be able to select a pic from a client's folder and imbed that pic for my report.
View 1 Replies View RelatedI'm trying to find a macro that will report how many values are returned in a pivot table. For example, if there is 5 values returned, I want it to return the value "5" in a cell If there are 10 values returned from the pivot table, I want to return the value "10" in a cell
View 1 Replies View RelatedPlease see attached two books,
The first book is a report that is generated on my reporting systeM (Report I use everyday)
The second report is the one that I have created myself using a macro (macro report)
I have to manually manipulate this report everyday and extract some information from it to
a custom report(macro report) that we use everyday.
I have created a macro for this - please see my code below.
I need some help with some small issues I am having,(Highlighted in yellow)
1) I need to include a Total line, but the size of the report varies everyday from say
around 30 rows to over a 100 rows. Is there a way i can make the total always 2 rows below
the report, please see attached for an example.
2) I need to hardcord a formula in on Column F and G. But again the report varies in size,
the columns do not change but there could be lots more rows. how do i take this into
account
The formula i need to hard code into column F is
=(IF(($D$60>=0)*AND($D$60<=5000000),0.0009))+IF(($D$60<=10000000)*AND($D$60>5000000),0.0007
)+IF($D$60>10000000,0.0005)
D60 in the above formula is just the total of all the other months summed together.
For Column E the formula is just =F*E But again the size in the report varies.
I have a pivot chart, filtering data from a pivot table, the problem I've got stuck is that every time I filter the chart the colors and weights of the chart change to default values, not what I want, I've managed to every time I click on the pvt cht tab it updates with the colors and weights I want, but when filtering I don't know how to do.
View 1 Replies View RelatedI want a macro that will transfer the data of billing report in the data sheet of the excel file billing dec 2011.
View 1 Replies View RelatedI have an excel sheet containing data as given below, wherein we receive certain report from each department on monthly basis and the same is mentioned as received against each month under header column. We need to remind the department about non receipt of report for particular months which contain empty cells. Macro is required to generate the report of empty cells mentioning the months against each department.
Available Data
Dept
Sep-12
[Code]....
find the attached Example file. I need to create a report either by using or without using VBA.
View 3 Replies View RelatedI am making a spreadsheet that sorts and pastes, but I need to know if I can add a code to the Sort and Paste Macro that will open the second spread sheet needed without just already having it open and using the
Windows("estimate sheet one.xls").Activate
I use Excel 2003 at work. I'm looking for some code that will Open a folder and then lets the user to select a file then continues to run the macro.
The file name they select will look similar to this K2271011.504 or K3011111.201
I created folder on my desktop to hold various documents. I have master excel document which contains forms and command buttons that open documents from same folder. Is there a way to change code instead of having full file path to specify current directory. I'm worried that if I move this folder to another location then my open file button will not work since location is changed.
Also when I open with command button word document, I have a button on word document to close. I can close document but word application is still active. Can I close word application with the document at the sam time. This is the code I'm using: "ActiveWindow.Close"
I am trying to set up a macro that hides certain columns of data in an automated spreadsheet that I don't need. How do I make the macro that hides the columns apply to all spreadsheets that are open except for the one I am in?
View 9 Replies View RelatedIs it possible to use a macro to:
1. Open Excel from the Desktop
2. Open a workbook
3. Execute a macro from this workbook
If so, how can it be done, specially point 1?
I currently have a macro set up to run once a workbook is saved/ closed. This works fine unless tehre is more than one workbook open. When I try it with more than 1 different workbook open I get a run error.
My code begins:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
and then performs the macro. I don't really want to specify the filename as this is liable to change. Is there anyway of adding a "this workbook" command to the text string, and if so where would it go?
I use the following macro to open excel files. Can this be modified to open a picture ppppp.jpg?
. . . or, can someone help me with a new macro that can open pictures
. . . I would still like to have the file checking options to see if the picture is already open and if not, that it actually exists.
I have the code below that puts in the file name, date and time and page numbers. That works fine, however, I changed the name of this macro to AutoOpen thinking that this would execute upon opening an Excel doc -- I think this works for Word, that's why I tried it. Anyway, it doesn't execute upon opening an Excel doc. Can anyone tell me how to get that going? Also, the font of the file name comes at at 12 point while the other entries come in at 6 pt - any way of making these all consistent, say 8 pt? Thanks for your help.
By the way, I'm a novice at Excel macros so being fundamental in your answer would be very helpful (i.e., don't know why there is a distinction between Regular Modules, Worksheet Modules and Workbook Modules. Seems to me I should always be creating Regular Module macaros so that they are accessible to all Excel docs but, again, I'm new at this so I'll have to read more about these items as well).
if workbook("Result") is not open then
add new workbook and name it "Result + date and time"
else activate workbook "Result"