Search Results

Search found 5640 results on 226 pages for 'excel automation'.

Page 64/226 | < Previous Page | 60 61 62 63 64 65 66 67 68 69 70 71  | Next Page >

• Weird Excel Formatting

  - by Gage

  Recently a new co-op was hired at our company and has been tasked to run a report. The report queries the database and returns a resultset and from there procedes to create the spreadsheets. Depending on the number of days selected a different number of reports are generated but I do not believe that is relavent to the question. Basically it runs the reports and loops through the resultset but at some point continues to loop through until tow 65536 at which it stops. For Example if the resultset contained 74 records then the first 74 rows would appear normally (formatted yellow) while everything after that would also be formatted yellow although it should be left alone. I am inheriting this code as I to am a new co-op. Apparently this only happens when a "change of guards" happens (New co-op has to run the report).` DoCmd.SetWarnings False DoCmd.OpenQuery ("DailySummaryQueryMain") strSQL = "SELECT * FROM DailySummaryMain" Set rs = CurrentDb.OpenRecordset(strSQL) DoCmd.Echo True, "Running first Report" If Not rs.EOF Then rs.MoveFirst Do While Not rs.EOF And Not rs.BOF xlapp.Range("A" & i).Value = rs.Fields(0).Value xlapp.Range("B" & i).Value = rs.Fields(1).Value xlapp.Range("C" & i).Value = rs.Fields(2).Value Set rs2 = CurrentDb.OpenRecordset("SELECT dbo_StatusType.StatusTypeID, dbo_StatusType.Name FROM dbo_StatusType WHERE (((dbo_StatusType.StatusTypeID)=" & rs.Fields(3) & "))") rs2.MoveFirst xlapp.Range("D" & i).Value = rs2.Fields(1).Value xlapp.Range("E" & i).Value = rs.Fields(4).Value xlapp.Range("F" & i).Value = rs.Fields(5).Value xlapp.Range("G" & i).Value = rs.Fields(6).Value 'count number of outages that start and end on same day If Format(xlapp.Range("F" & i).Value, "mm/dd/yyyy") = Format(xlapp.Range("G" & i).Value, "mm/dd/yyyy") Then dayCount = dayCount + 1 End If xlapp.Range("H" & i).Value = rs.Fields(7).Value xlapp.Range("I" & i).Value = rs.Fields(8).Value xlapp.Range("J" & i).Value = rs.Fields(9).Value xlapp.Range("K" & i).Value = rs.Fields(10).Value xlapp.Range("L" & i).Value = rs.Fields(11).Value xlapp.Range("M" & i).Value = rs.Fields(12).Value xlapp.Range("N" & i).Value = rs.Fields(13).Value 'highlite recently modified rows If rs.Fields(14).Value = "Yes" Then xlapp.Range("A" & i & ":N" & i).Select With xlapp.Selection.Interior .ColorIndex = 36 .Pattern = xlSolid End With End If 'break apart by sector If CInt(rs.Fields(2).Value) = 1 Then row = row1 ElseIf CInt(rs.Fields(2).Value) = 2 Then row = row2 ElseIf CInt(rs.Fields(2).Value) = 3 Then row = row3 Else row = row4 End If xlapp.Worksheets(CInt(rs.Fields(2).Value) + 1).Activate xlapp.Range("A" & row).Value = rs.Fields(0).Value xlapp.Range("B" & row).Value = rs.Fields(1).Value xlapp.Range("C" & row).Value = rs.Fields(13).Value xlapp.Range("D" & row).Value = rs.Fields(4).Value xlapp.Range("E" & row).Value = rs.Fields(5).Value xlapp.Range("F" & row).Value = rs.Fields(6).Value xlapp.Range("G" & row).Value = rs.Fields(7).Value xlapp.Range("H" & row).Value = rs.Fields(8).Value xlapp.Range("I" & row).Value = rs.Fields(9).Value xlapp.Range("J" & row).Value = rs.Fields(10).Value xlapp.Range("K" & row).Value = "" xlapp.Range("L" & row).Value = rs.Fields(11).Value xlapp.Range("M" & row).Value = rs.Fields(13).Value If CInt(rs.Fields(2).Value) = 1 Then row1 = row1 + 1 ElseIf CInt(rs.Fields(2).Value) = 2 Then row2 = row2 + 1 ElseIf CInt(rs.Fields(2).Value) = 3 Then row3 = row3 + 1 Else row4 = row4 + 1 End If 'activate main summary sheet for next outage xlapp.Worksheets(1).Activate i = i + 1 rs.MoveNext Loop` Also I should note that this is all happening within an access database which has its tables linked from SQL. The query is extremely slow to run from which I believe is the use of views but thats neither here nor there. All you have to know is attempting to debug takes an enormous amount of time due to having to wait for the recordset to return. My guess is that its not checking to see if the resultset is empty correctly. Is there a way I could check to see if theres a value is rs.Fields(0) and base it off that maybe? That is the ID column and there should always be a value. I am wondering why rs.EOF isn't catching this though.

  Read the article

• Excel VSTO set selection

  - by Florin Sabau

  How do I set the selection in the active worksheet to a specific Range, using C# and VSTO 2008?

  Read the article

• Copying Excel Worksheets in POI

  - by Patrick Nichols

  Does anyone know of a means to copy a worksheet from one workbook to another using POI? The Workbook class has a cloneSheet method, but there doesn't seem to be able to insert a cloned sheet into a new workbook? If there isn't an API to do this easily, does anyone have the code to copy all of the data (styles, column widths, data, etc) from one sheet to another? The jxls has methods to copy sheets, but they don't work when copying between workbooks.

  Read the article

• Using Excel VBA to capture edit mode through checking the standard tool bar

  - by srimal

  I'm using this code to check if the standard toolbar is deactivated in the edit mode. CommandBarControl oNewMenu = Application.CommandBars("Worksheet Menu Bar").FindControl(1, 18, 1, True, True) If (IsNull(oNewMenu)) Then MsgBox "Edit mode enabled" End If but the FindControl function raise an error. Is there any conflict in the parameters?

  Read the article

• Macro to alert me when a cell changes value -- popup alert - EXCEL

  - by patrick

  Hey gurus, I am looking to create a macro to alert me when a cell's value changes. The cells in this particular column can have values of either "OVER" or "UNDER". I would like to write code to alert me via a popup (Message: "Cell A crosses under(over)") when the value changes. Thanks, patrick

  Read the article

• Creating an excel macro to sum lines with duplicate values

  - by john

  I need a macro to look at the list of data below, provide a number of instances it appears and sum the value of each of them. I know a pivot table or series of forumlas could work but i'm doing this for a coworker and it has to be a 'one click here' kinda deal. The data is as follows. A B Smith 200.00 Dean 100.00 Smith 100.00 Smith 50.00 Wilson 25.00 Dean 25.00 Barry 100.00 The end result would look like this Smith 3 350.00 Dean 2 125.00 Wilson 1 25.00 Barry 1 100.00 Thanks in advance for any help you can offer!

  Read the article

• decrypt excel files

  - by locojay

  Hi I have 1000 encrypted workbooks which I would like to decrypt by providing a pwd. I could not find a decrypt method under apache poi or python's xlrd module. Does anyone know a library which could handle this (wbc.decrypt(pwd)). I would prefer a lib i could you use from a unix box. Thanks

  Read the article

• excel vba moving non-contiguous range selection to an array

  - by Russ Urquhart

  In the situation where the user select two non-contiguous column ranges i wrote the following: Dim count long Dim points variant Dim i long Set user_range = ActiveWindow.RangeSelection count = user_range.count / 2 ReDim points(1 To count, 1 To 2) For i = 1 To count MsgBox "value is" & user_range.Areas.Item(1).Value(i,1) points(i, 1) = user_range.Areas.Item(1).Value(i,1) points(i, 2) = user_range.Areas.Item(2).Value(i,1) Next i But i get an object error when i try this. Am i indexing Value wrong? This should work right? Is there an easier way to do this? Any help is greatly appreciated! Thanks, Russ

  Read the article

• MS-Excel Negative times

  - by oxinabox.ucc.asn.au

  I'm writing a spreadsheet for a shop manager. What it does is keep track of the number of hours a worker has worked. So you enter times for Monday-Sunday, and then an adjustment - e.g. if they work 40/40/40/32 hours for the month, then you would have an adjustment of -2/-2/-2/+6 to bring the worker to the 38 hour week that he's being paid for. Some (most) weeks may be adjusted for overtime. The spreadsheet then totals the hours. This spreadsheet is supposed to just be a self-calculating version of a paper form. It needs to match the paper form as it has to be substituted for the old form which is given to some other member of the company (pay clerk, I don't know; I'm not rebuilding their whole system, just replacing a form) I'm having trouble entering a negative time in the adj field - the field has a [h]:mm formatting. and when i enter a negative time (e.g. -2:00) it displays an error, saying "incorrectly formatted equation", with the suggestion that if I was entering a string then I should prefix with a apostrophe. How do I overcome this?

  Read the article

• Excel VBA: importing CSV with dates as dd/mm/yyyy

  - by Michael Smith

  ello I understand this is a fairly common problem, but I'm yet to find a reliable solution. I have data in a csv file with the first column formatted dd/mm/yyyy. When I open it with Workbooks.OpenText it defaults to mm/dd/yyyy until it figures out that what it thinks is the month exceeds 12, then reverts to dd/mm/yyyy. This is my test code, which tries to force it as xlDMYFormat, and I've also tried the text format. I understand this problem only applies to *.csv files, not *.txt, but that isn't an acceptable solution. Option Base 1 Sub TestImport() Filename = "test.csv" Dim ColumnArray(1 To 1, 1 To 2) ColumnsDesired = Array(1) DataTypeArray = Array(xlDMYFormat) ' populate the array for fieldinfo For x = LBound(ColumnsDesired) To UBound(ColumnsDesired) ColumnArray(x, 1) = ColumnsDesired(x) ColumnArray(x, 2) = DataTypeArray(x) Next x Workbooks.OpenText Filename:=Filename, DataType:=xlDelimited, Comma:=True, FieldInfo:=ColumnArray End Sub test.csv contains: Date 11/03/2010 12/03/2010 13/03/2010 14/03/2010 15/03/2010 16/03/2010 17/03/2010 Thanks Michael

  Read the article

• C++ library to load Excel (.xls) files

  - by Jake88

  Hey Everyone, I'm looking for a free C++ library that can load .xls files in both Windows and Linux. If I had to make a choice, Linux would be the bare minimum. I've tried LibXL, but got this amazing error: "can't read more cells in trial version" So now I'm on the hunt for a free version :), unfortunately xlsLib isn't are enough along either to provide the ability to load existing .xls files. Thanks

  Read the article

• Excel VBA: Passing a collection from a class to a module issue

  - by Martin

  Hello, I have been trying to return a collection from a property within a class to a routine in a normal module. The issue I am experiencing is that the collection is getting populated correctly within the property in the class (FetchAll) but when I pass the collection back to the module (Test) all the entries are populated with the last item in the list. This is the Test sub-routine in the standard module: Sub Test() Dim QueryType As New QueryType Dim Item Dim QueryTypes As Collection Set QueryTypes = QueryType.FetchAll For Each Item In QueryTypes Debug.Print Item.QueryTypeID, _ Left(Item.Description, 4) Next Item End Sub This is the FetchAll property in the QueryType class: Public Property Get FetchAll() As Collection Dim RS As Variant Dim Row As Long Dim QTypeList As Collection Set QTypeList = New Collection RS = .Run ' populates RS with a record set from a database (as an array), ' some code removed ' goes through the array and sets up objects for each entry For Row = LBound(RS, 2) To UBound(RS, 2) Dim QType As New QueryType With QType .QueryTypeID = RS(0, Row) .Description = RS(1, Row) .Priority = RS(2, Row) .QueryGroupID = RS(3, Row) .ActiveIND = RS(4, Row) End With ' adds new QType to collection QTypeList.Add Item:=QType, Key:=CStr(RS(0, Row)) Debug.Print QTypeList.Item(QTypeList.Count).QueryTypeID, _ Left(QTypeList.Item(QTypeList.Count).Description, 4) Next Row Set FetchAll = QTypeList End Property This is the output I get from the debug in FetchAll: 1 Numb 2 PBM 3 BPM 4 Bran 5 Claw 6 FA C 7 HNW 8 HNW 9 IFA 10 Manu 11 New 12 Non 13 Numb 14 Repo 15 Sell 16 Sms 17 SMS 18 SWPM This is the output I get from the debug in Test: 18 SWPM 18 SWPM 18 SWPM 18 SWPM 18 SWPM 18 SWPM 18 SWPM 18 SWPM 18 SWPM 18 SWPM 18 SWPM 18 SWPM 18 SWPM 18 SWPM 18 SWPM 18 SWPM 18 SWPM 18 SWPM Anyone got any ideas? I am probably totally overlooking something! Thanks, Martin

  Read the article

• Excel 2007 and Unicode

  - by pjlasl

  I have an israeli spreadsheet reading right to left. When I read the values (using VBA) it places a question mark (?) at the beginning and end of the text, in other words it wraps the text with the question mark (ie ?0123456?). If you type Range("A2").value or .value2 or .text the results are the same. Any idea on how to prevent this?

  Read the article

• MS Excel - Macros for consolidating values from multiple sheets into a single sheet

  - by SpikETidE

  Hi everyone... Consider i have 4 workbooks with the following structure... 1. Main.xlsx Name Jan Feb Mar A B C 2. Jan.xlsx Name Jan A 3.3 B 6.4 C 5.3 3. Feb.xlsx Name Feb A 1.3 B 3.4 C 5.5 4. Mar.xlsx Name Mar A 1.3 B 3.4 C 5.5 I need to combine them like 1. Main.xlsx Name Jan Feb Mar A 3.3 1.3 1.3 B 6.4 3.4 3.4 C 5.3 5.5 5.5 And i need to automate the process... And i guess i can do this with macros...? Can anyone suggest some way with which i can proceed with the macro? Thanks for your time....

  Read the article

• How can I copy Data from one sheet to another Sheet in Excel 07 Through Macro

  - by Mwaseem Alvi

  Hello, I am using MS Office 2007. Please let me know that how can I copy whole data from sheet one to sheet two. I want to copy the whole data from row 5 to onward in sheet two. The whole scenrio is given below in detail. Sheet one: Copy the data from column B and Row 3 Sheet Two: Paste the Copied Data in Column B and Row 3 Sheet One: Copy the whole data from Column B to Column G and Row 5 to onward Sheet Two: Paste whole copied data in sheet two from last filled row to onward Data dont overwrite on any row or column. Every data will be add in sheet two from sheet one when macro will be run. Thanks

  Read the article

• Create chart using dynamic interactive ranges to select the series in Excel 2007

  - by jhc

  I would like to create a non-VBA based solution to the following question: How do I create a multi-series chart that will allow a user to select from a dropdown to change the data being graphed? I can do this already when the data series is contiguous; however, I'd like to be able to do it for non-contiguous data. Is this possible? My data look something like this: ID Salary Sal Min Sal Mid Sal Max Division Job Grade Job Subgrade Job XXX 10000 5000 15000 25000 North 13 1 Programmer XXX 12000 5000 15000 25000 North 13 1 Programmer XXX 14000 5000 15000 25000 South 13 1 Analyst XXX 11000 5000 15000 25000 South 13 1 Analyst XXX 20000 5000 15000 25000 North 14 1 Super Programmer XXX 25000 5000 15000 25000 North 14 1 Super Programmer XXX 22000 5000 15000 25000 North 14 1 Manager XXX 17000 5000 15000 25000 South 14 1 Manager XXX 19000 5000 15000 25000 South 14 1 Manager I would like to display Salary, Sal Min, Sal Mid, and Sal Max using a line graph. I would like the user to be able to select Job Grade, Division, and/or Job to determine what is charted. Is this possible? Would I somehow be able to do this if I used a pivottable or converted my data into a datatable? Thanks.

  Read the article

• Excel VBA merge many columns into one on separate rows

  - by gorussgo

  I have a large array of cells in multiple columns that need to be combined into one large column with a new row for each cell. I do NOT want to merge the contents of any cells.

  Read the article

• (EXCEL)VBA Spin button which steps through in an sql databases date time

  - by Gulredy

  I have an sql Database table in MySQL which have lots of rows with varied date time values. For example: 2012-08-21 10:10:00 <-- with these date there are around 12 rows 2012-08-21 15:31:00 <-- with these date there are around 5 rows 2012-08-22 11:40:00 <-- with these date there are around 10 rows 2012-08-22 12:17:00 <-- with these date there are around 9 rows 2012-08-22 12:18:00 <-- with these date there are around 7 rows 2012-08-25 07:21:00 <-- with these date there are around 6 rows If the user clicks on the SpinButton1_SpinUp() or SpinButton1_SpinDown() button then it should do the following: The SpinButton1_SpinUp() button should filter out those data from an sql table which is the next after what we are currently on now. Example: We have currently selected: 2012-08-21 15:31:00. The user hits the SpinUp button then the program selects those date from the database, which is the next higher value like this one: 2012-08-22 11:40:00. So the user hits the SpinUp button the data which is selected in the database will change from those with date: 2012-08-21 15:31:00 to those with date: 2012-08-22 11:40:00 The SpinButton1_SpinDown() will do exactly the reverse of the SpinUp button. When the user hits the SpinDown button the data which is selected in the database will change from those with date: 2012-08-21 15:31:00 to those with date 2012-08-21 10:10:00 So I think the date which we are currently on, should be stored in a variable. But on button hit not every bigger or lower data should be selected in the database, only those which are the closest bigger or the closest lower date. How can I do this? I hope I described my problem understandable. My native language is not english, so misunderstandings can occur! Please ask if you don't understand something! Thank you for reading!

  Read the article

• MS Excel - Copying columns from multiple worksheets into a single worksheet based on some condition

  - by SpikETidE

  Hi everyone... Consider i have 4 workbooks with the following structure... 1. Main.xlsx Name Jan Feb Mar A B C 2. Jan.xlsx Name Jan A 3.3 B 6.4 C 5.3 3. Feb.xlsx Name Feb A 1.3 B 3.4 C 5.5 4. Mar.xlsx Name Mar A 1.3 B 3.4 C 5.5 I need to combine them like 1. Main.xlsx Name Jan Feb Mar A 3.3 1.3 1.3 B 6.4 3.4 3.4 C 5.3 5.5 5.5 And i need to automate the process... Can someone enlighten me about the possible options that i have to do this...? Thanks for your time....

  Read the article

• Excel VBAa: Sum invoice by client id with copying result to new worksheet

  - by Melkior

  Hi, i have strange problem doing reporting: i have numerous clients with different issued invoices. Problem comes to the point when there are invoices in minus and plus: Column A consists of client unique IDs, Column B invoice number, column C invoice amount A | B | C 0010019991 | 1800149471 | 162.00 0010019991 | 1800136388 | 162.00 0010019991 | 1600008004 | -36.00 0010021791 | 1800132148 | 162.00 0010021791 | 1800145436 | 162.00 0010021791 | 1600007737 | -12.00 0014066147 | 1800119068 | 1,684.80 0014066147 | 1800123702 | 1,684.80 0014066147 | 1600007980 | -1,300.80 0014066147 | 1600007719 | -1,286.40 I need to remove rows with negative invoices in a way that amount is summed with invoices which are not with negative amount. So that final result would look like: A | B | C 0010019991 | 1800149471 | 126.00 0010019991 | 1800136388 | 162.00 0010021791 | 1800132148 | 150.00 0010021791 | 1800145436 | 162.00 0014066147 | 1800123702 | 782.40

  Read the article

• Error while creating a table style in excel

  - by Rashmi Pandit

  Hi, I am using the following function to create a TableStyle: Public Function CreateTableStyle() ActiveWorkbook.Unprotect Dim objTS As TableStyle On Error Resume Next Set objTS = ActiveWorkbook.TableStyles("MyTableStyle") On Error GoTo err_CreateTableStyle If Not objTS Is Nothing Then Exit Function End If Set objTS = ActiveWorkbook.TableStyles.Add("MyTableStyle") With ActiveWorkbook.TableStyles("MyTableStyle") .ShowAsAvailablePivotTableStyle = True .ShowAsAvailableTableStyle = False End With With ActiveWorkbook.TableStyles("MyTableStyle").TableStyleElements( _ xlHeaderRow).Font .FontStyle = "Bold" .TintAndShade = 0 .ThemeColor = xlThemeColorDark1 End With With ActiveWorkbook.TableStyles("MyTableStyle").TableStyleElements( _ xlHeaderRow).Interior .ThemeColor = xlThemeColorLight2 .TintAndShade = -0.249946592608417 End With With ActiveWorkbook.TableStyles("MyTableStyle").TableStyleElements( _ xlTotalRow).Font .FontStyle = "Bold" .TintAndShade = 0 .ThemeColor = xlThemeColorDark1 End With With ActiveWorkbook.TableStyles("MyTableStyle").TableStyleElements( _ xlTotalRow).Interior .ThemeColor = xlThemeColorLight2 .TintAndShade = -0.249946592608417 End With ActiveWorkbook.TableStyles("MyTableStyle").TableStyleElements( _ xlSubtotalRow1).Font.FontStyle = "Bold" With ActiveWorkbook.TableStyles("MyTableStyle").TableStyleElements( _ xlSubtotalRow1).Interior .Color = 16764828 .TintAndShade = 0 End With ActiveWorkbook.TableStyles("MyTableStyle").TableStyleElements( _ xlSubtotalRow2).Font.FontStyle = "Bold" With ActiveWorkbook.TableStyles("MyTableStyle").TableStyleElements( _ xlSubtotalRow2).Interior .Color = 16777164 .TintAndShade = 0 End With ActiveWorkbook.Protect Exit Function err_CreateTableStyle: Call Common.ErrRaise(Erl, "Common", "CreateTableStyle", "CreateTableStyle") End Function At the line below: With ActiveWorkbook.TableStyles("MyTableStyle").TableStyleElements( _ xlHeaderRow).Font .FontStyle = "Bold" I am getting an error: Run-time error '1004' Unable to set the FontStyle property of the Font class. Can someone please identify the issue? I am not able to figure why it is not letting me set the property.

  Read the article

• Output a NULL cell value in Excel

  - by Peter

  I have an IF statement. If a cell = n, then do something, else output NULL =IF(A1=5, "Success", NULL) // #NAME? =IF(A1=5, "Success", "NULL") // NULL (as in text, not actually NULL!) =IF(A1=5, "Success", "") // blank but not NULL =IF(A1=5, "Success", 0) // zero value but not NULL

  Read the article

• Overriding events of excel sheet using VBA

  - by Rashmi Pandit

  Hi, I need to programmatically override the following events of a worksheet: BeforeDoubleClick SelectionChange BeforeRightClick I have been able to override the OnActivate event using the following code: sheet.OnSheetActivate = "MyOwn_Activate" Private Sub MyOwn_Activate() myForm.Show End Sub I have implemented BeforeDoubleClick on similar lines: sheet.OnDoubleClick = "My_BeforeDoubleClick" Private Sub My_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) ... End Sub However, an 'argument not optional' error is thrown at run-time when user double clicks a cell on the sheet. Can someone please suggest how should I pass the paramters? In addition, I am not able to find event names for SelectionChange & BeforeRightClick. I tried: sheet.BeforeRightClick = "My_BeforeRightClick" sheet.SelectionChange = "My_SelectionChange" But, both the above lines do not work. Any help/ suggestion is greatly appreciated. Thanks :)

  Read the article

• Excel document incorrect format

  - by Jim

  I have a macro enabled work book and i change the name of the .xlsm file to [FileName].xlsm.zip and then i unzip i get some folders I then put these extracted folders in to another folder and zip it back and rechange the extension to the previous xlsm format i now try and open but i get an unreadable error. I am not changing any content here just extracting and zip it back. What could be the problem?

  Read the article

• Updating Excel Cell with Non-Numeric Data in C#

  - by kbo206

  I have a query that is ExcelQuery = "Update [Sheet1$] set CITIZEN_ID = #" + value + " where CITIZEN_ID = " + value; As you can see, I'm essentially just appending a "#" onto the CITIZEN_ID field. value is a int/numeric value. So if I had "256" in the CITIZEN_ID column it would be converted to "#256" When I execute this I get an OleDbException Syntax error in date in query expression so I surrounded part of the query in single quotes like this, ExcelQuery = "Update [Sheet1$] set CITIZEN_ID = '#" + value + "' where CITIZEN_ID = " + value; With that I get yet another OleDbException this time with, Data type mismatch in criteria expression. I'm guessing for some reason the CITIZEN_ID fields don't want to take anything besides a plain number. Is there any way I can remedy this to get that pound symbol in? Thanks!

  Read the article

< Previous Page | 60 61 62 63 64 65 66 67 68 69 70 71  | Next Page >