2010 excel worksheet include green cell pointer .i can't change it . i try to change it BY settings or scripts but i can't find it .is it possible to change color of pointer or possible to the change cell colour which cursor on it ?
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Cells.Interior.ColorIndex = xlNone
If Intersect(Target, [A1:Q50]) Is Nothing Then Exit Sub
Range(Cells(Target.Row, 1), Cells(Target.Row, 17)).Interior.ColorIndex = 6
End Sub
I have values like below in excel sheet.(No Column Header)
COLUMN A
5
9
7
13
8
i want to this with Oledb Query in VBNET or C#;
select MAX(A) from [Sheet1$]
But get Error !
For formulation of a Quadratic Assignment Problem we need to implement
∑(i=1)^n▒∑(j=1)^n▒∑(k=1)^n▒∑(l=1)^n▒〖(X_ij.X_kl)(〖TF〗ik)(D(jl)) 〗
where Xij are the decision variables
(X_ij.X_kl) can be linearised to a variable Yijkl,but how can implement this in excel..? Can anyone please tell me how to formulate this expression ..?
Any related advice regarding tackling Quadratic Assignment Problem computationally ..?
I have already tried and solved a normal assignment problem but i could not find any way of implementing the four index variable.
I am using MS excel 2010 Thanks in advance..!
I am having a lot of difficulty importing an excel spreadsheet into STATA. The spreadsheet is much smaller than previous spreadsheets I have successfully imported.
I am extremely frustrated: I have updated the program and my Mac (OS X Yosemite), restarted both STATA and the computer, and am completely unable to browse through the data. Each time I run the do-file or manually import the dataset STATA becomes unresponsive. What's worse is that when I eventually force quit STATA, I can see it pulling up my spreadsheet before systematically running through the commands and closing all of the windows.
PLEASE HELP.
I've a column with cells containing the word "Google" and "Google.com" as well. If I search for the keyword "Google" using the formula =COUNTIF(B1:B940,"Google") it doesn't count the "Google.com" ones. Which formula can I use which gives me the count for both.
The below code acquires all files having below format and stores the name and location in a text file.
( pushd C:\Users\<User_name>\
dir /s /b *.jpg *.png
popd ) > Image_Files.txt
FOR /F "tokens=2 delims=:" %%D in ('find /c /v "" Image_Files.txt') DO SET /a ImageCnt=%%D
Is there a way to integrate the extensions from a excel file instead of mentioning it in the code ?
For example : In excel, Name : new.xlsx... say Sheet3, Cell A1-AX have different extensions:
A1 = *.jpg
A2 = *.png
And so on, Instead of mentioning in the code, is there a way to get these extensions and search for the files ?
But the problem is that data exported is not filtered but its the same that was retrieved from excel.
DefaultTableModel model =(DefaultTableModel)TABLE1.getModel();
JTable table =new JTable(model);
table.getModel().getValueAt(table.convertRowIndexToModel(0),5);
table.setModel(model);
try{
File file = new File("ECN320C.xls");
try (FileWriter excel = new FileWriter(file)) {
for(int i = 0; i <model.getColumnCount(); i++){
excel.write(model.getColumnName(i) + "\t");
}
excel.write("\n");
for(int i=0; i<model.getRowCount(); i++)
{
for(int j=0; j < model.getColumnCount(); j++)
{
excel.write(model.getValueAt(i,j).toString()+"\t");
}
excel.write("\n");
}
}
JOptionPane.showMessageDialog(this, "Your Data Is Saved.");
}
catch(Exception e)
{
e.printStackTrace();
}
I have tried my level best to remove ~ character, which is a part of text in one column at excel 2013 file.
can somebody help me replace this character(~) with underscore(_)?
thanks
Is it possible to make a small program in excel? Or more specifically excels duplicate OpenOffice Calc?
I want to create an inventory system so my barcode scanner will enter the barcode and hit return. Then to have a script that runs that barcode and if found in column C add 1 quantity to the quantity column D.
Basically to cells on that would -1 every time it matches a barcode and one that would +1 to the quantity.
I don't want to enter the barcodes in multiple cells I want to select the cell, scan it(which would enter the barcode and hit return), then for it to search and add quantity automatically. And stay in this box so I can scan another without changing anything. Thanks a lot God Bless You All.
I am new to using VB alongside Excel and am in need of help.
I have an two access databases; one having 10 columns with many rows (more than 10), and the other Database has 8 columns and the same amount of rows as the other.
What I am trying to do is to export the first 10 rows of both databases to an excel sheet (in separate sheets or in two separate excel files, either way) so that this could be emailed or printed.
I've been looking around trying to get an idea on how to do this and have tried a few ways of doing it but none of them have worked.
Additionally I'll be fine if someone can help with exporting one database through VB with the first 10 rows of the database.
Could someone please help me.
Thanks, Andy
I'm trying to write VBA Codes that will read a given CSV file name and do some changes to the data before writing into another CSV file.
Set oSht_Input = Workbooks.Open(file_Path, UpdateLinks:=False).Worksheets(extractDataFile.convertedSheetName)
lastRow = oSht_Input.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
For Rows = 2 To lastRow
extractedDataFileRecord.Variable1 = CStr(oSht_Input.Cells(Rows, 1))
.... code continues
This code is working fine generally.
However, the flaw is that CSV files will just copy the file name and place into the sheet name. Therefore I can safely assume that the variable convertedSheetName is equivalent to the filename provided.
This code will work if the file name is short enough to be copied into the sheet name. However if the file name is too long, the filename gets cut off within the sheet name, thus I am not able to get the correct reference to set the Worksheet object.
Is there a better way to actually get the sheet reference, so that I can read the Cell Values within the sheet?
Thanks!
I want to read excel pivot table data and store it in a sql table. Pivot table is having ten RowFields and six DataFields. I have written the code to run through all the combination of PivotItems and calling the GetPivotData() to check whether any valid data available in the pivot table. Still it is taking ten minutes to finish the execution even after get rid of checking bottom level data if top level summary data is not available. Really appreciate Any Idea to read and get the unpivot data quickly from excel hierarchical pivot table?
I am trying to create a spreadsheet in which I need a button in each row to stamp the time in the cell next to it and then sort the rows in order of time. My problem is that the buttons do not move. E.g. Button in cell B1 changes time in cell A1 and button in cell B2 changes time in cell A2 and for this example lets say A2 has a lower time than A1 so when sorted A1 and A2 effectively swap data. Now the button in B2 changes the time in cell A1.
Been trying to figure this out for hours, any help would be greatly appreciated.
I have an application that runs as a service. This application allows the user to connect and starts a subprocess under the user's windows account. In other words, mydomain\john connects to the service via TCP and says "launch" which starts a process on windows running under mydomain\john account.
Next, this subprocess is able to map drive T: using net use command. Then the process can run a VB script living on the mapped drive T:. No trouble so far. the problem arises when this VB script tries to run Excel using "createObject".
After setting DCOM launch settings with DCOMCNFG and creating the missing "Desktop" folder in the systemprofile folder, I still cannot get this to work. Oddly, if run my vb script and feed Excel a UNC path \fileserver\share1 rather than T:\ the script works.
Does anyone know why Excel would fail to access the T:\ mapping but have no issue with the UNC path?
Suppose I have an Excel list of numbers {12345, 12354, 12435, etc...}. I want to output a list that has one row for each digit {1,2,3,4,5,1,2,3,5,4,1,2,4,3,5, etc...}.
Open to any suggestions!
Edit: (From comments)
Sample Data:
Result:
How can I get the sum of column B if the dates from Column A fall in the Current year. I have a Cell to represent the current year: Cell I1 =YEAR(TODAY())
SIGH i can't show you an image cause i need 10 reputation points SMH
I am new to VBA. I got t-SQL query that joining with three different database. Now I got requirement to embedded this query in Excel using VBA. I just read some articles how to create connection string with single database but my query involves multiple databases, How can I do this in VBA code.
Please advise me.Thanks in advance
Good Morning All,
I have a spreadsheet that I need to provide analytics on. One of the most important factors in the analytics is to analyse the data over time, the only issue is the format in which they have been storing the date. The question is very similar to this problem but for excel:
how do I convert mmyy to last day of month in netezza
or this question:
Create a date from Credit Card expire in MMYY format
Essentially I have a column filled with dates that have been written as mmyy i.e.
0215 0215 0215 0315 0315
The column has been saved as the data type: "Special". How do I convert this data into a useable format? I don't mind if we put it into dd/mm/yyyy and use the first or the last day of the month that's fine. Is there an excel function that I could use or is this something I would have to do in VBA? and if so, how?
I'm trying to use VBA on Mac Excel 2011, but nothing seems to be responding. When I click on the 'Editor' button, a blank window that says 'Project' appears, in which I can do nothing. If I hit macros, a window comes up allowing me to input a name and hit create, but that is all and nothing happens after I hit create. I am able to place things like buttons, but I am unable to assign any VBA macros to them. How do I go about getting started on VBA or fixing this problem? Do I need to set up a workspace or something?
How can I merge the rows with the same Date together? The merge/centre function doesn't work. Removing duplicates doesn't work either. Thanks.
I am trying to clear column contents from row 2 down on a VBA script which I am running, the piece of code below is how I am going about this.
This doesn't appear to be working, but no syntax issues or error messages. Are there issues with this code?
Option Compare Text
Option Explicit
Sub Loader()
Dim wb3 As Workbook
Set wb3 = ThisWorkbook
wb3.Worksheets("Config Sheet").Range("F2").End(xlDown).ClearContents
End Sub
I am trying to accomplish a relatively (I think) simple task. I want to create a button that copies the contents of the active cell to the clipboard. I will then use crtl+v to paste into another application. The goal is to copy a string of text inside of an excel sheet... including formatting and line breaks. I want to avoid having to press F2, Crtl+shift+home, then crtl+C. Is there a way to do this?
Plain old Crtl+C and activecell.copy do not achieve the right result because they get rid of any line breaks when pasting into another app. TIA
Is it possible to set a worksheet's gridline color using epplus?
The "Gridline color" option I'm trying to set programmatically is shown in the screenshot below.
[MS Excel]
How do I search and mark items with a sum at a particular amount,
i.e., among 1,000 transactions, find out the transactions which add up together will result in exact $10,000,000?
Thanks.
Not sure how many people know about WinWrap Basic language which supports the .Net framework. But that is where my problem is. Hoping someone out there know about the language.
I am using it to communicate with Excel 2010. Basically I am extracting data for a proprietary data format and spitting it out to Excel. The following execute perfectly when no additional instances of Microsoft Excel 2010 are active during script run.
xExcel = CreateObject("Excel.Application")
xBook = xExcel.Workbooks.Open(XLFilePath)
xSheet = xBook.Worksheets(“Sheet1”)
xExcel.Visible = False
xSheet.Cells(1,1).Value = "Study Name"
However, if an additional Excel 2010 instance is activated manually by the user during the following script run, script errors out.
xExcel = CreateObject("Excel.Application")
xBook = xExcel.Workbooks.Open(XLFilePath)
xSheet = xBook.Worksheets(“Sheet1”)
xExcel.Visible = False
Do
xSheet.Cells(1,1).Value = "Study Name"
Loop
The guys at WinWrap say that CreateObject() in WinWrap is a simple code that calls the CoCreateInstance API. I am sort of lost there. Can someone help me understand how create multiple Excel instances.
Is it possible to have multiple label filters in a pivot table? I see that I can use 'contains' which could work but I am having issue with the syntax if this is possible.
I'm thinking something like contains "apples", "banannas" should work Any insight would be appreciated.
I have a vba code that scans a folder and its subdirectories for excel files and lists the connection strings and sql command. But my problem is my program doesn't list the inaccessible network folders that gives you the error "Access Denied." I wanna be able to list the path to the folder too and indicate on the second column that the folder is inaccessible. How should I code it? I'm thinking
On Error Resume Next
If Err.Number = 52 Then
oRng.Offset(0, 1).Value = "Folder inaccessible"
End If
But this code doesn't work and I'm not really sure where to put it. Here's the full code (without the code above). You can see under Private Sub CheckFileConnections I did something similar with password protected excel files.
Private Const FILE_FILTER = "*.xl*"
Private Const sRootFDR = "L:\" ' Root Folder
Private oFSO As Object ' For FileSystemObject
Private oRng As Range, N As Long ' Range object and Counter
Sub Main()
Application.ScreenUpdating = False
Set oFSO = CreateObject("Scripting.FileSystemObject")
N = 0
With ThisWorkbook.Worksheets("Sheet1")
.UsedRange.ClearContents ' Remove previous contents
.Range("A1:E1").Value = Array("Filename", "Connections", "Connection String", "Command Text", "Time Scanned")
Set oRng = .Range("A2") ' Initial Cell to start storing results
End With
Columns("A:E").Select
With Selection
.WrapText = True
.ColumnWidth = 45
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlCenter
End With
ListFolder sRootFDR
Application.ScreenUpdating = True
Set oRng = Nothing
Set oFSO = Nothing
Columns.AutoFit
MsgBox N & " Excel files has been checked for connections."
End Sub
Private Sub ListFolder(ByVal sFDR As String)
On Error Resume Next
Dim oFDR As Object
' List the files of this Directory
ListFiles sFDR, FILE_FILTER
' Recurse into each Sub Folder
For Each oFDR In oFSO.GetFolder(sFDR).SubFolders
ListFolder oFDR.Path & "\" ' Need '\' to ensure the file filter works
Next
End Sub
Private Sub ListFiles(ByVal sFDR As String, ByVal sFilter As String)
Dim sItem As String
On Error Resume Next
sItem = Dir(sFDR & sFilter)
Do Until sItem = ""
N = N + 1 ' Increment Counter
oRng.Value = sFDR & sItem
CheckFileConnections oRng.Value ' Call Sub to Check the Connection settings
Set oRng = oRng.Offset(1) ' Move Range object to next cell below
sItem = Dir
Loop
End Sub
Private Sub CheckFileConnections(ByVal sFile As String)
Dim oWB As Workbook, oConn As WorkbookConnection
Dim sConn As String, sCMD As String
Dim ConnectionNumber As Integer
ConnectionNumber = 1
Application.StatusBar = "Opening workbook: " & sFile
On Error Resume Next
Set oWB = Workbooks.Open(Filename:=sFile, ReadOnly:=True, UpdateLinks:=False, Password:=userpass)
If Err.Number > 0 Then
oRng.Offset(0, 1).Value = "Password protected file"
Else
With oWB
For Each oConn In .Connections
If Len(sConn) > 0 Then sConn = sConn & vbLf
If Len(sCMD) > 0 Then sCMD = sCMD & vbLf
sConn = sConn & oConn.ODBCConnection.Connection
sCMD = sCMD & oConn.ODBCConnection.CommandText
oRng.Offset(0, 1).Value = ConnectionNumber ' 1 column to right (B)
oRng.Offset(0, 2).Value = oConn.ODBCConnection.Connection ' 2 columns to right (C)
oRng.Offset(0, 3).Value = oConn.ODBCConnection.CommandText ' 3 columns to right (D)
oRng.Offset(0, 4).Value = Time()
ConnectionNumber = ConnectionNumber + 1
oRng.Offset(0, 1) = Now
Set oRng = oRng.Offset(1) ' Move Range object to next cell below
Next
End With
End If
oWB.Close False ' Close without saving
Set oWB = Nothing
Application.StatusBar = False
End Sub
I have a MS Excel macro that creates a PivotTable. Within the PivotTable I want to filter on multiple items. So first you need to set all items to false and then set the items you want to include to true. So currently I have:
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Test Cycle")
.PivotItems("FXA BI_MH Pre UAT C1").Visible = False
.PivotItems("FXA BI_MH Pre UAT C2").Visible = False
.PivotItems("FXA Reg C1").Visible = False
.PivotItems("MC3").Visible = False
.PivotItems("PT Cycle 1").Visible = False
.PivotItems("Regression Test (APO)").Visible = False
.PivotItems("SIT CR").Visible = False
.PivotItems("SIT Cycle 2").Visible = False
.PivotItems("UAT - Data Conv").Visible = False
.PivotItems("UAT Pre-Test - Additional").Visible = False
.PivotItems("UAT Pre-Test - Final").Visible = False
.PivotItems("UAT Pre-Test - Iteration 2").Visible = False
End With
ActiveSheet.PivotTables("PivotTable2").PivotFields("Test Cycle"). _
EnableMultiplePageItems = True
However, the "Test Cycle" items change and if a new one is introduced it is automatically included as I have not set it to false. Is there a way to just set all selections to false, something like (which does not work):
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Test Cycle")
.PivotItems("(All)").Visible = False
End With
ActiveSheet.PivotTables("PivotTable2").PivotFields("Test Cycle"). _
EnableMultiplePageItems = True
I have an engineering application developed in c# and I need the following component/tool.
My application for each of its calculation cycles should read a set of parameters as input. Some of the potential customers keep those input parameters in excel files, others in sql databases and others in forms (paper or digital) or even other not known yet.
So, for each deployment, I need to customize the "input reader" module to comply with a specific requirement and at best I may be able to reuse some code. I need a module like an sql mapper tool to be integrated in my application so I will be able to construct a specification set (using eg. sql or QBE) to gather data from sources like sql, excel files or other and map them to my own internal dataset for the input parameters.
Thanks in advance for your time and support.
Regards, semag
we have an asp.net web application hosted in IIS7 in windows server 2012. while printing reports using microsoft excel through this application getting an error "activex component can't create the object".
Thanks for reading my post. I'm new to Excel VBA and have run into a wall debugging a call to Find(). I've gone through several posts on this site and others but so far each fix I've tried has been unsuccessful.
I am writing code to process elements out of financial reports. Each report contains one or more multi-row & multi-column blocks of cells with details describing a project. The size of each block isn't consistent, but each always begins in the top left with "Client Name". So I want to iterate through these blocks keying off that text, then pulling out needed elements.
There's no while loop here yet, as I'm running into the error just setting up the first condition.
Run-time error '91': Object variable or With block variable not set
Here's the section of code from within the Sub, with the error coming in the final line assigning cursorProject:
' store the next report to process
Dim nextReport As String
Dim sourceSheetName As String
Dim sheetSource As Worksheet
nextReport = rptMedia
' copy the worksheet into rptBurn and get that worksheet's name
sourceSheetName = GetSheet(nextReport)
Set sheetSource = Workbooks(rptBurn).Worksheets(sourceSheetName)
sheetSource.Cells.EntireRow.Hidden = False
sheetSource.Cells.EntireColumn.Hidden = False
Workbooks(rptBurn).Activate
' process the sheetSource into sheetCurrent
' set constants
Const constCursorKey As String = "Client Name"
Const constClientColumn As String = "B"
Const constClientNameOffset As Integer = 2
Const constProjectLeft As Integer = 2
Const constProjectRight As Integer = 52
' get range in Client Name column of project entries
Dim cursorStart As Long
Dim cursorEnd As Long
Dim cursorProject As Range
Dim rangeProject As Range
Dim rangeSearch As Range
cursorStart = sheetSource.Columns(2).Find(constCursorKey).Row + constClientNameOffset
' find the last project entry in the sheet
cursorEnd = sheetSource.Range("B" & Rows.Count).End(xlUp).Row
Set rangeSearch = sheetSource.Range(Cells(cursorStart + 1, constProjectLeft), _
Cells(cursorEnd, constProjectLeft))
cursorProject = rangeSearch.Find(What:=constCursorKey, LookIn:=xlValues, LookAt:=xlPart, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, _
SearchFormat:=False)
This is very sloppy currently as parts will be moved out to its own Sub called while iterating over the reports (hence nextReport is hardcoded here to a specific report name). The various constants are fixed parameters of the reports. The unlisted values like "rptBurn" are globals. The GetSheet function works well, but if you want to see it:
Private Function GetSheet(rpt As String) As String
Workbooks.Open rootPath + rpt
ActiveSheet.Copy after:=Workbooks(rptBurn).Sheets(Workbooks(rptBurn).Sheets.Count)
GetSheet = ActiveSheet.Name
Workbooks(rpt).Close
End Function
I've tried several variations on this. The Locals all look promising up to the error. I set the Hidden properties to False based on another post. I've tried simplifying the call down to the basics and using With, like this:
Set rangeSearch = Sheets(3).Range("B:B")
rangeSearch.Select
With rangeSearch
cursorProject = .Find("Client Name")
End With
But I'm always getting an error on cursorProject. There are definitely many "Client Name" entries in the worksheet I'm testing. I put in the Select to verify I'm grabbing the correct range; oddly I find that "B:AX" get highlighted (AX is the rightmost used column in the report) in the simple version, but the selection I expect in the original. Regardless there are "Client Name" instances in either selection--I can select B4 and see "Client Name".
What am I doing wrong?
For a school project, I have to collect data from an Excel file uploaded by the user. I am using Symfony2 and have installed a bundle I found on knpbundles, named ExcelBundle. I read that to collect data with it from an Excel file, I should use the createWriter method of my phpExcel object. That is what I have done as shown below.
public function addContactsFromExcelAction(Request $request) {
$uploadDir = '/var/www'.$request->getBasePath().'/uploads/';
//die(var_dump($uploadDir));
$file = $request->files->get('fichierExcel');
$fileName = $file->getClientOriginalName();
$fileSaved = $file->move($uploadDir,$fileName);
$phpExcelObject = $this->get('phpexcel')->createPHPExcelObject($uploadDir.$fileName);
$writer = $this->get('phpexcel')->createWriter($phpExcelObject, 'Excel2007');
}
But the thing is that actually, I do not really know how to use the writer to collect data from the cells of my excel datasheets.
Please, could anyone give me the trick to achieve my goal ?
Is it possible to pad a cell with spaces to ensure that it is the correct length?
Here's an example with * representing spaces.
If the user wants to enter 'red' then I would like it to convert to 'red**'. If the user wants to enter 'blue' then I would like to convert to 'blue*'. In effect adding spaces to always ensure the cell is 5 characters long.
I am new to VBA.So please bear with me. !! I have a access database on Shared drive and in that if table gets new record it will automatically export the records to the specified excel sheet. And if someone can provide me the code for it, will be a great help and explain a little.
I want to create an excel sheet to schedule employee annual planner.
If anyone submits leave application, while entering i need to find anyone applied for these date range. And also compared it with their respective designations.
can anyone help me to sort out this.....
I'm trying to migrate my excel files to the google spreadsheets but I have trouble with the following. In my excel design what I did was keep grades in a single file. From this file I made a connection with a report file in which all the grades automatically appeared.
When I tried to do this in Spreadsheets it appears to be very difficult to make cross references between files.
Basically what I want is this: 1. File with all grades for my students (this already works) 2. Some kind of way to create a report in another file in which the grades that belong to one student appear. 3. When there is a second report or third report make it possible to add the grades to the first report that was created.
I'm not a crack at using macro's, but is it possible to create something that allowes me to do this?
In Excel I created a report file for every student and then from that file I made a reference to the grade file. This was maybe not the most efficient way to to do it but it worked. Now I want to migrate to spreadsheets I really would I like to know if there are easier ways to create the report file.
I have converted a gradelist for report 1 and one report from a student so you have an idea of what i'm talking about. The grades of column B in the grade files correspond with the B report file with the report 1,2 and 3.
I hope you can help me out!
Thank you in advance!
Rik
I'm saving a list of phone numbers in google sheets, the number is saved in the format +88-65-987456, but formula is applied due to addition and subtraction symbols and the value in the sheet changes to the value obtained from the sum and difference, how to save the value as +88-65-987456 and not apply formula, basically turn off calculations that take place automatically.
I'm trying to break my string in MS Excel into different columns based on unique identifier, i.e. "."
I need to break the below string,
1.1.4.1.10.3
Formulae should give me below shown results in 6 different columns,
1 1 4 1 10 3
Would really appreciate any kind of help!! Thanks in advance.
Hi all may you please assist,
I need to sort data on a pivot table columns I want the column Jan and Feb to be placed after access type column and Apr May and M+ to placed after 8
DAY
Access Type 1 2 3 4 5 6 8 Jan Feb Apr May M+ Total
Current Access 42 56 55 63 25 36 5 105 250 637
Future Access 9 66 35 3 113
This is a followup question to this one.
The following formula can be used to find the value of the last non-empty cell in a column, in Microsoft Excel:
=LOOKUP(2,1/(A:A<>""),A:A)
My question is, how can I convert this formula to a Macro function? So that I can use it like this:
=GetLastValueInColumn('A')
Indeed, I need to convert the letter 'A' to range A:A, but don't know how.
I Have Phone Numbers Like This To Sort By Rows
66-66970877, 22-34567, 8764576890, 333-7465
(Column A1,Column B1,Column C1,Column D1)
These Are Phone Numbers Which I Have To Short For Small To Large Or Large To Small
Sorting Data In Row P1 To S1
22-34567, 333-7465, 66-66970877, 8764576890
(Column P1,Column Q1,Column R1,Column S1)
Column P1 = small($a1:$d1,1),
Column Q1 = small($a1:$d1,2),
Column R1 = small($a1:$d1,3),
Column S1 = small($a1:$d1,4)
These Formula Works Great On Without (-) Numbers.
I Need Formula To Ignore (-)
Numbers Before (-) Is Area Codes. If I Remove (-) With Find & Replace. I Will Get Confuse In Future As I Can't Differentiate Area Code And Phone Number.
If There Is Solution In Macro
I Want To Short Telephone Numbers In Three Rows After Mobile Numbers In Other Three Rows.
P1-Telephone Number, Q1-Telephone Number, R1-Telephone Number, S1-Mobile Number, T1-Mobile Number, U1-Mobile Number
I Need A Macro
Ignores The (-) Value In All Phone Numbers
Check First Digit Of Phone Numbers.
If First Digit Start's With Nine(9). If There Is Any Single Phone Number In A1, B1, C1, D1 Starting With Nine(9)(It's Mobile Number) Put Into S1. Else If There's Two Numbers Starting With Nine (9) Place It In Row S1 And T1 In Ascending Or Descending Order.
If First Digit Start's With Eight (8). If There Is Any Single Phone Number In A1, B1, C1, D1 Starting With Eight (8) Put Into Q1. Else If There's Two Numbers Starting With Eight (8) Place It In Row Q1 And R1 In Ascending Or Descending Order.
Like Wise For Other Digits Too
Thanks & Regards
Babulal Gandhi
Error Message while opening the file:
Excel found unreadable content in zz.xlsx. Do you want to recover the contents of the work book?If you trust the source of the workbook,click Yes.
If I say "Yes" got "Repairs to zz.xlxs" pop up shows.
Could any one please help me. What format of the excel could have caused this. As i tried sample workbook without any formatting it work fine.
My code :
from openpyxl import Workbook from openpyxl import load_workbook #open existing workbook wb = load_workbook(filename = 'xx.xlsx') wb.save('zz.xlsx')
Appreciate your help. Thanks!
I would like to save a number of worksheets from the current workbook to a different workbook and exclude a sheet named "buttons" (in current one) from that saving process.
Can anybody help please? The number of worksheets is changeable FYI.
Below is what I have so far which include all the sheets from current workbook.
Sub SaveAs()
D1 = VBA.Format(Now, "mm_DD_yyyy")
For Each ws In Application.Workbooks
ws.SaveAs Filename:="C:\Users\e2309\Desktop\Andy's\GBB_Report_" & D1 & ".csv"
Next ws
Application.Quit
End Sub
Can anybody help me to take-off quantities of loose furniture in the best and fastest way?
Information as follow:
There are about 2000 different types of rooms and 300 sets of different furniture
Two different schedules are provided to me;
1- Furniture-Code for each room that represent to a set of furniture
2- Furniture-Code Explained, means which type furniture related to each code
for excel data use this link enter link description here
I want to prepare a schedule in excel where each room and its furniture listed and finally sum of each furniture C-1, C-2, C-3 …….. etc
Kind regards
I can't explain what is wrong with this simple code. It works perfectly on Excel 2003, but in Excel 2010 it displays Compile error: Sub of Function not defined.
Can anyone explain why is there an error on Excel 2010. Thanks.
Sub Test_File_Opened()
If IsFileOpen("D:\Test.xls") Then
MsgBox "File is open!"
Else
MsgBox "File is closed!"
End If
End Sub
The issue is with Office 2013.
We are hosting excel workbook window in WPF application using HwndHost class. In overridden BuildWindowCore method of HwndHost class, we are creating a MDICLIENT Window and setting this MDICLIENT window handle as parent for Excel main window handle and returning HandleRef object of MDICLIENT window handle from BuildWindowCore method. Once HwndHost control is loaded, we are selecting sheet of excel in the loaded event of HwndHost control. The code for selection of sheet is given below.
dynamic workSheet = this.excelApplication.Workbooks[1].Worksheets[sheetName];
this.excelApplication.Workbooks[1].Activate()
workSheet.Activate();
workSheet.Select(true);
The first time execution of workSheet.Select(true) halt for few seconds and then it throws exception
“System.Runtime.InteropServices.ComException” with message “The server threw an exception. (Exception from HRESULT: 0x80010105 (RPC_E_SERVERFAULT))”.
But it executes successfully without delay on subsequent calls. Sometimes the execution halt for minutes on different Systems. This absurd behaviour is seen only once in application instance life time and that too after excel window gets loaded in WPF application. If we execute workSheet.Select(true) before loading of window then it executes properly. Once workSheet.Select(true) executed for first time with exception, it executes successfully without delay on subsequent calls.
I'm designing an excel template for importing data (into another DBMS).
The problem is, the data will be separated (normalized) into several tables in my DBMS (master-detail model).
Let's say I have two sheet Order and Order Detail.
The Order contains: ORDERID, CUSTOMERID, EMPLOYEEID, ORDERDATE, REQUIRED_DATE, and SHIPPED_DATE.
While Order Detail contains: ORDERID, PRODUCTID, and QUANTITY
I'm planning to create a template like this:
When the Order is entered, there will be a collapsible pane to enter the Order Detail (from corresponded Order).
Is it possible to create something like this? How to achieve that?
Thanks in advance :)
I have a pivot table that displays agencies in rows, products in columns and sales units as values. I have to make up a report in this format:
Data is coming from an SQL Server Analysis Services and the "Estimate 2015" measure is a different measure (but uses the same dimensions and granularity so it is in fact possible to display the values side by side).
I could add a separate pivot table for the estimates, but then filtering or sorting the two tables will make them loose sync.
Is it possible to somehow align or combine pivot tables with different measures nicely?
I need to (non-manually) download this file and convert the contents to a data.frame, the ability to ignore a few rows would be useful.
The file itself can be taken from:
Here's what I've done so far:
If you open the file in notepad, it's an xml file, and when opening in Excel, you get a warning message "The format and extension don't match".
It doesn't matter to me if the solution is in python or R, so long as the result is usable in R.
i will conduct a research for comparing the three machine learning approaches to predict stock return (naive bayes, decision tree and logistic regression). i have some difficulties to run this research because i didn't know what data in excel that must be prepared as an input in rapidminer software. Hope you can help me to solve this problem. Thank you
I have a list of staff emails in an Excel sheet and I'm trying to retrieve users' organisational team hierarchy using VBA. The output I am after is like the following:
Email address, Team Hierarchy
aaa@email.com, Team 1\Team 2\Team 3\Team 4
bbb@email.com, Team 1\Team 5\Team 6\Team 7
I've seen that team hierarchy information is readily shown in Outlook (right-click contact -> Properties -> General tab -> Organisation field). So I think there should be a way to retrieve this information to a spreadsheet using VBA, but couldn't find a way to do it.
I did some research and saw that using LDAP there are ways to get the basic information directly from Active Directory such as First name, Last name, Org unit (team), etc., but couldn't find a way to get the team hierarchical view.
Appreciate if someone can help!
Cheers!
I have an application where i try to upload a excel file (approx 21,000 rows) from a browser - The application is written in JSP , Servlet , Apache POI and JDBC. Now when the file is uploaded from browser(jsp file) using Servlet and then read the records of excel file using Apache POI and then write the rows to the MySQL database. The upload runs successfully and able to insert records in to Mysql database. However, the jsp page just stays there and user is notified of the successful upload only after few minutes.
Can anyone help me How do i show the user a message on the browser that file has been uploaded successfully but it is getting processed ? Any better implementation of the code also please let me know. My Servlet code is as below
import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.io.PrintWriter;
import java.util.Iterator;
import java.util.List;
import java.util.logging.Logger;
import javax.servlet.ServletConfig;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.commons.fileupload.FileItem;
import org.apache.commons.fileupload.FileUploadException;
import org.apache.commons.fileupload.disk.DiskFileItemFactory;
import org.apache.commons.fileupload.servlet.ServletFileUpload;
import org.apache.commons.io.FilenameUtils;
import org.json.simple.JSONObject;
import src.com.pion.web.config.business.UploadFileProcessor;
/**
* Servlet implementation class UploadServlet
*/
@WebServlet(name = "UploadServlet",
description = "Manages the uploaded servlet file and sends it to processing",
urlPatterns = { "/upload.do" }
)
public class UploadServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
private static final Logger LOGGER = Logger.getLogger(UploadServlet.class.getName());
private static final String UPLOAD_DIRECTORY = "upload";
private static final int THRESHOLD_SIZE = 1024 * 1024 * 3; // 3MB
private static final int MAX_FILE_SIZE = 1024 * 1024 * 40; // 40MB
private static final int MAX_REQUEST_SIZE = 1024 * 1024 * 50; // 50MB
public UploadServlet() {
super();
}
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
throw new ServletException("GET method used with " +
getClass( ).getName( )+": POST method required.");
}
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
//PrintWriter to send the JSON response back
// checks if the request actually contains upload file
if (!ServletFileUpload.isMultipartContent(request)) {
PrintWriter writer = response.getWriter();
writer.println("Request does not contain upload data");
writer.flush();
return;
}
PrintWriter out = response.getWriter();
//set content type and header attributes
response.setContentType("text/html");
response.setHeader("Cache-control", "no-cache, no-store");
response.setHeader("Pragma", "no-cache");
response.setHeader("Expires", "-1");
// configures upload settings
DiskFileItemFactory factory = new DiskFileItemFactory();
factory.setSizeThreshold(THRESHOLD_SIZE);
factory.setRepository(new File(System.getProperty("java.io.tmpdir")));
ServletFileUpload upload = new ServletFileUpload(factory);
upload.setFileSizeMax(MAX_FILE_SIZE);
upload.setSizeMax(MAX_REQUEST_SIZE);
// constructs the directory path to store upload file
String uploadPath = getServletContext().getRealPath("")
+ File.separator + UPLOAD_DIRECTORY;
// creates the directory if it does not exist
File uploadDir = new File(uploadPath);
if (!uploadDir.exists()) {
uploadDir.mkdir();
}
JSONObject myObj = new JSONObject();
String fileName = null;
String fullName = null;
File file = null;
try {
// parses the request's content to extract file data
List formItems = upload.parseRequest(request);
Iterator iter = formItems.iterator();
// iterates over form's fields
while (iter.hasNext()) {
FileItem item = (FileItem) iter.next();
// processes only fields that are not form fields
if (!item.isFormField()) {
fileName = new File(item.getName()).getName();
String filePath = uploadPath + File.separator + fileName;
file = new File(filePath);
fullName = item.getName().trim();
// saves the file on disk
item.write(file);
}
} String message = null;
UploadFileProcessor uploadProcessor = new UploadFileProcessor();
String extension = FilenameUtils.getExtension(fullName);
if(extenter code hereension.trim().equalsIgnoreCase("xlsx")){
// Send the code for processing and later insert into database
message = uploadProces`enter code `enter code here`here`sor.processExcelFile(file);
}
myObj.put("success", true);
myObj.put("message", message + " item(s) were processed for file " + fileName);
LOGGER.info(myObj.toString());
}
catch(FileUploadException ex) {
log("Error encountered while parsing the request",ex);
myObj.put("success", false);
LOGGER.info(myObj.toString());
} catch(Exception ex) {
log("Error encountered while uploading file",ex);
myObj.put("success", false);
LOGGER.info(myObj.toString());
}
out.close();
}
}
In my excel-export.html I have:
<div class="config-menu-container first">
<div class="menu-title">
<span class="excel import">Excel import</span>
<span class="excel export">Excel Export</span>
</div>
<ul class="menu-body step2" id="exportable">
<li class="title">
<span class="label main order first”>1</span>
<span class="label main order second”>2</span>
<span class="label main order third”>3</span>
</li>
<li class="row" ng-repeat="device in devices">
<span class="label sub first" ng-bind="device.deviceId" ng-click="deviceScope = device.id"></span>
<span class="input second">
<custom-input type="text" width="330" height="24" model="device.name"></custom-input>
</span>
<span class="input third" ng-repeat-end>
<custom-input type="select2" width="462" height="24" label=“Select Device."
options="option.billingInfo" model="device.billingTargetId"></custom-input>
</span>
</li>
</ul>
</div>
In my excel-export.directive.js I have:
'use strict';
angular.module('bemsApp')
.directive('excelExport', function () {
return {
templateUrl: 'app/directives/excel-export/excel-export.html',
restrict: 'EA',
link: function (scope, element, attrs) {
scope.exportData = function () {
var blob = new Blob([document.getElementById('exportable').innerHTML], {
type: "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8"
});
saveAs(blob, "Report.xls");
};
}
};
});
When I try to save the Excel file, an error occurs saveAs did not define.
I have to separately define the saveAs function wondering if there is a way to save the Excel file.
ws1.Cells(1, i).Copy ws2.Range("a65536").End(xlUp).Offset(1, 0)
I am looping through the sheet so i is a part of loop. The number of rows are more than 65536 , then how can I adjust this. Thanks in advance.
How can I show I winform that I create in VB.NET just below the active cell?
I have no idea how to solve this. I found the following promising solutions: Excel addin: Cell absolute position
-The accepted solution seems too complicated to work reliably. I got an error on the first row (Private Declare Function GetDC Lib "user32" (ByVal hwnd As Long) As Long)
-The second solution looked promising, but it didn't give me the right positions for my windows form.
The following adaptations of the second proposed solution does not create any errors but does not put the windows form in the correct position:
Public Sub GetScreenPositionFromCell(cell As Excel.Range, excel As Excel.Application)
Dim x As Double
Dim y As Double
If Not excel.ActiveWindow Is Nothing Then
x = excel.ActiveWindow.PointsToScreenPixelsX(cell.Left)
y = excel.ActiveWindow.PointsToScreenPixelsY(cell.Top)
End If
Me.Left = x
Me.Top = y
Me.Show()
Me.TopMost = True
End Sub
I have am new here, trying to learn. at the same time facing issue, i am trying to count and sum up the duplicate values into a pivot table type of result. sorry the posts keep saying my code is not properly formatted, unable to share what macros i'm using. i use it to combine multiple sheet into "master sheet to display overall result." but i would like to add in such as counting different duplicate model type at "B" column only and sum it at column J(display model) and I (total quantity) on "master sheet". Thanks if any kind soul could help.
Hi;
I have a sheet which has 4 columns ;
Column A and C have product numbers
Column B and D are for detail of the products.
What i'm trying to do is ; If a number in column A match with a number in column C then i want to take the value (product details) of B to column E. but if the number repeats then at second row and match again with that number in column C then i want to that the new value to column F.
It's a really particular problem for us. We are trying to create a new sql database for our new web page and we have to convert all data like this.
I tried it with =vlookup formula in Excel but it didn't work. i think this problem can solve only with macros. (If i'm not wrong...)
PS_ Please check the image, it's even hard to explain in my mother language.
Thanks
For i = 1 To wss.Cells(Rows.Count, "B").End(xlUp).Row
poc(i) = wss.Cells(srow + 3, scolumn).Value
wsd.Cells(drow, dcolumn).Value = poc(i)
drow = drow + 1
Next
This is the error I get
subscript out of range error
I am developing an Excel addin that provides specialised calculations. VBA code in the addin talks to a COM server written in Python. The communication uses strings or Variants.
Someone using the addin has reported a failure three times now (Office 2010-32 bit, Windows 7 Professional) on two different machines. On each occasion, they turned off the computer and when they turned in on again, the addin was disabled by Windows Data Execution Prevention.
I would like to try to track down the reason for this, but so far have not been able to reproduce the problem. I have even tried one of the machines with the same spreadsheet, but did not manage get the addin disabled.
Can anyone tell me whether this action of disabling the addin by Windows only occurs at shutdown/restart, or can it occur at any time? Not only was the addin disabled, Windows also deleted a number of the DLLs needed by the COM server!
I know that the spreadsheet had been worked on for several days without apparent problems before the machine was turned off. So should I consider that the problem might have occurred at any time while the addin was being used, or does the failure at shutdown/restart point to a problem setting up, or tearing down, the components involved?
Any suggestions on how to approach this problem?
I am a new learner of VBA. I would like to plot chart for each row whereby if there is 20 rows in the table, there will be 20 charts. The number of rows and columns are dynamics. I get parameter error at the part defining series collection and I can't figure out the solutions.
Set cht = Sheets(wsName).Shapes.AddChart.Chart
'sets the chart type
cht.ChartType = xlLine
For a = row To totalRow
With Sheets(wsName)
'now the line chart is added...setting its data source here
cht.SetSourceData Source:=.Range(.Cells(a, ColFirst), .Cells(a, ColLast))
cht.SeriesCollection.NewSeries
cht.SeriesCollection(a).XValues = .Range(.Cells(a, ColFirst), .Cells(a, ColLast))
cht.SeriesCollection(a).Values = .Range(.Cells(4, ColFirst), .Cells(4, ColLast))
End With
Next
Say i have a data set
apple 11
orange 22
grape 33
and a list
apple
grape
is there anyway i can look up (and sum up) the values in column B based on the sub-list? In this case, my sum will be 44
I tried index match and vlookup but can't seem to lookup using a list. I also google extensively but all search results i got were related to lookup using multiple criteria (array) which i know how to do.
Please help?
In this case, it'd be easy to add up two individual lookups. However, in my real example, my list has over 10 values so it wouldn't be too efficient
Thank you very much!
set ws1 As SheetA
set ws2 As Target
With ws1
LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
End With
Lastrow = ws1.Range("B" & Rows.Count).End(xlUp).Row
For i = 1 To Lastcol
For l = 4 To lastrow
ws1.Cells(l, i).Copy ws2.Range("a65536").End(xlUp).Offset(1, 0)
Next l
Next i
I am trying to copy all the columns in one sheet to another sheet one below the another. Basically copying Column A of Sheet1 to Column A of sheet2 and then column B of sheet1 below column A of sheet2 . Data starts from row 4th of sheet A in every column, so I kept the second for loop running from 4. Any help is greatly appreciated as I am stuck for hours now. When I run the code it goes in to infinite loop.
I have a large text file which is contains the text "_ _Z_1_:_" followed by data such as:
_ _Z_1_:_0_1_3_4_2
Fixed Totaliser Period 1 Reset Report
NET sales 57 £202.05
CASH in Drawer 55 £172.35
CREDIT in Drawer 2 £29.70
TOTAL in Drawer 57 £202.05
There are two occurrences of the "_ _Z_1_:_" text in each file.
I am trying to get the macro to find the "_ _Z_1_:_" and then lift the cash amount (in the above case £172.35) and credit etc.
I have tried using a code to allow me to specify the text file and then used the InStr function but it will only identify the first occurrence.
Any help appreciated.
Shane
Is it possible to to declare Cell position with using variables from an array?
Here is my old code which works perfectly but what if I need many more Cells? (I make it shorter). This code by pressing button sets Value of selected cells to zero.
Sub ResetButt01_Click()
Cells(12, 5).Value = 0
Cells(12, 13).Value = 0
Cells(12, 21).Value = 0
Cells(19, 4).Value = 0
Cells(19, 6).Value = 0
Cells(19, 12).Value = 0
End Sub
And here is my attempt to do it by using Array:
Sub ResetButt01_Click()
Dim myRow() As Variant
Dim myCol() As Variant
myRow = Array(12, 12, 12, 19, 19, 19)
myCol = Array(5, 13, 21, 4, 6, 12)
Cells(myRow, myCol).Value = 0
End Sub
But this code doesn't work. So I tried this:
Sub ResetButt01_Click()
Dim myRow() As Variant
Dim myCol() As Variant
Dim r As Integer
Dim c As Integer
myRow = Array(12, 12, 12, 19, 19, 19)
myCol = Array(5, 13, 21, 4, 6, 12)
r = myRow
c = myCol
Cells(r, c).Value = 0
End Sub
But again it doesn't work. Do you have any idea how to put values to array and this use these values as variables to define the position of Cells?
I'm using the following string to copy a column from one sheet to another
Dim wsCore As Worksheet
Dim wsData As Worksheet
Set wsCore = Sheets("R2 Data Dump")
Set wsData = Sheets("Active")
Dim rowNumber As Integer
Dim cellFormulas As Variant
Dim cellFormulas1 As Variant
Dim counter As Integer
counter = 0
Dim currentCell As String
Dim importSheetCell As Variant
Dim importSheetOffset As Variant
Dim contractnum As Integer
Dim pt As PivotTable
Dim ws As Worksheet
Dim pc As PivotCache
Dim lastrow As Long
Set wsCore = Sheets("R2 Data Dump")
Set wsData = Sheets("Active")
Sheets("R2 Data Dump").Visible = True
Sheets("R2 Data Dump").Select
rowNumber = ActiveSheet.UsedRange.Rows.Count
With wsCore
Startrow = 3
wsCore.Columns("W").Copy Destination:=wsData.Columns("A")
wsCore.Columns("B").Copy Destination:=wsData.Columns("B")
wsCore.Columns("C").Copy Destination:=wsData.Columns("C")
wsCore.Columns("D").Copy Destination:=wsData.Columns("D")
End With.
So what is does is copy the particular columns from one shit and paste it on the specified column on the destination sheet. My problem is, the action starts pasting the copied cell from the source sheet to the first row on the destination cell. What I would like to find out is how to set a row number where it would start pasting instead of row 1.
When I use the below code to paste the unique values from column a to b, the first value in column a is always getting repeated at the end of column b.
Sheet1.Range("A1:A10").AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=Sheet1.Range("B1:B10"), Unique:=True
column A: test1,test2,test3,test1,test2,test3
column B: test1,test2,test3,test1
As shown above test1 is getting repeated. I tried changing the values, alter sequence in column a but still 1st value is getting repeated.
worksheet.PrintOutEx(1,2, 1, false, Type.Missing, false, false, Type.Missing);
I am using this code to print successfully.
I want to do duplex printing but I don't know. Any direction on this?
VBA newbie here.
I am creating a spreadsheet in excel that will be imported into an Access database later on. I am getting users to select a value from multiple drop down menus. My problem is, these drop down menu values need to be very specific, and are not very readable, for example:
notAttendingSchoolWhy: (choose one)
TOO_YOUNG_FOR_SCHOOL GRADUATED NOT_ATTENDING_DUE_TO_DIABETES NOT_ATTENDING_DUE_TO_FINANCIAL_REASONS NOT_ATTENDING_FOR_OTHER
I'd like these to be a bit more readable, but still be able to be imported into my database. So, I'd like to use something like an alias in excel where the user sees the 'nice' version, e.g.
not Attending School Why: Too young for school Graduated etc.etc.
and I can still put the matching version into my database.
any ideas? All help much appreciated!!
I am having some troubles with an IF statement in my code, I noticed the code was not doing what it was ment to, and it is due to a buggy IF statement.
The code below checks if two values match, these two values are targetcolumnvalue and sourcecolumnvalue. If these two values match, excel will perform a copy/paste based on cell row and column positions using these values as the column and row identifiers.
When I debugged the code, the targetcolumnvalue was equal to 26, and the sourcecolumn value was equal to "", but the code was executing on the scenario that the if statement was being met. Am I not seeing something here?
Sub BuggyIf()
Dim SourceColumnValue As String, sourcerow As String, targetrow As String, targetcolumnvalue As String, sourcecolumnnumber As String
Dim F as long, O As Long, P As Long, TargetValue As Long, actualsourcerow As Long, actualtargetrow As Long, actualtargetcolumn As Long, sourcedateposition As Long, actualsourcecolumn As Long, targetdateposition As Long, actualtargetforecastrow As Long
Dim Copysource As Range, pastetarget As Range
TargetValue = dumpsheet.Cells(rows.Count, 1).End(xlUp).row
sourcedateposition = dumpsheet.Cells(rows.Count, 5).End(xlUp).row
targetdateposition = dumpsheet.Cells(rows.Count, 7).End(xlUp).row
For F = 1 To sourcedateposition
SourceColumnValue = dumpsheet.Cells(F, 5).Value
'Get Target Column Match to Source
For P = 1 To targetdateposition
targetcolumnvalue = dumpsheet.Cells(P, 7).Value
' BUGGY IF STATEMENT
If targetcolumnvalue = SourceColumnValue Then
TargetColumnRange.Value = SourceColumnValue
targetcolumnvalue = dumpsheet.Cells(P, 8).Value
sourcecolumnnumber = dumpsheet.Cells(F, 6).Value
For O = 1 To dumpsheet.Cells(rows.Count, "a").End(xlUp).row
If O > 1 Then
Sourcename = dumpsheet.Cells(O, 1).Value
sourcerow = dumpsheet.Cells(O, 2).Value
targetrow = dumpsheet.Cells(O, 3).Value
dailyrate = dumpsheet.Cells(O, 4).Value
'Set Integers
actualsourcerow = CInt(sourcerow)
actualtargetrow = CInt(targetrow)
actualtargetcolumn = CInt(targetcolumnvalue)
actualsourcecolumn = CInt(sourcecolumnnumber)
actualtargetforecastrow = actualtargetrow - 521
dailyfte = CInt(dailyrate)
'Copy and Paste
Set Copysource = SourceSheet.Cells(actualsourcerow, actualsourcecolumn)
Set pastetarget = TargetSheet.Cells(actualtargetrow, actualtargetcolumn)
If pastetarget.Cells.Interior.Color <> 1 Then
Copysource.Copy
pastetarget.PasteSpecial (xlPasteValues)
Set Copysource = TargetSheet.Cells(actualtargetrow, actualtargetcolumn)
Set pastetarget = TargetSheet.Cells(actualtargetforecastrow, actualtargetcolumn)
Copysource.Copy
pastetarget.PasteSpecial (xlPasteValues)
fte = TargetSheet.Cells(actualtargetforecastrow, actualtargetcolumn).Value / dailyfte
TargetSheet.Cells(actualtargetforecastrow, actualtargetcolumn).Value = fte
End If
End If
Next O
End If
Next P
Next F
I am new in docx4j and openxml. I have a necessity to create the excel file which is contain N separate tables (existing template) with pre-created charts according these tables. Actually, I need to replace some text placeholders with the values from database and show to the user completely generated Excel document. I checked the sample with replacing values, it is working for me after obtaining the latest patch in the source code. Unfortunately, my charts cannot be evaluated according formulas because the replaced values are in string type. Ok, I can initially mark some rows for the future converting them to the numbers, but when I set the STCellType.N for these rows, the necessary values were missing and unknown values have been inserted to this place.
Source xlsm file contain:
F5 = ${gender:1} G5 = ${gender:1:value} H5 = =IF(G5>0; G5/SUM($G$5:$G$6)*100; "")
F6 = ${gender:2} G6 = ${gender:2:value} H6 = =IF(G6>0; G6/SUM($G$5:$G$6)*100; "")
After replacements I see in the result xlsm file:
F5 = Man G5 = 30 H5 = #VALUE!
F6 = Woman G6 = 32 H6 = #VALUE!
Note: I'm inserting "20" and "30", not "30" and "32".
here is my code:
List<String> numbers = new ArrayList<String>(); //convert to number the cell type with this address when all placeholders will be replaced
numbers.add("G5");
numbers.add("G6");
JaxbSmlPart smlPart = (JaxbSmlPart)pkg.getParts().get(new PartName("/xl/sharedStrings.xml"));
HashMap<String, String> mappings = new HashMap<String, String>();
mappings.put("gender:1", "Man");
mappings.put("gender:1:value", "20");
mappings.put("gender:2", "Woman");
mappings.put("gender:2:value", "30");
smlPart.variableReplace(mappings);
//when replacements done, iterating over all rows and columns in the sheet and set the type "Number" for the selected cells
for (int i=0; i<sheetData.getRow().size(); i++) {
for (int j=0;j<sheetData.getRow().get(i).getC().size();j++) {
if (numbers.contains(sheetData.getRow().get(i).getC().get(j).getR())) {
sheetData.getRow().get(i).getC().get(j).setT(STCellType.N);
}
}
}
Can you please tell me what I am doing wrong and is there a possibility to convert the string value to number (not a changing the type of the cell)? I also tried to use the online service http://ift.tt/1OScRxx, but didn't find anything useful in this case :(
Thanks!
In table A I have a list of codes that contain a numerical component after 1-3 letters. I need to use the numerical value to find the corresponding year via the ranges detailed in table B.
Table A: Table B:
Code Year Value Min Value Max Year
AC19 ? 0 10 2011
ABC2 ? 11 20 2012
AC12 ? 21 30 2013
AFC30 ? 31 40 2014
GXC0 ? 41 50 2015
To separate out the number I have been using:
=(RIGHT(B7,LEN(B7)-(FIND("C",B7)+0)))
(Where B7 in this case is the first code in the table)
This works well as all codes have a C before the numerical component.
I found that using example numerical segments (without using the above formula) I could use VLOOKUP with the "True" flag. However when I attempted to run it using the numerical value generated with the above formula I got a #Ref! error.
Why would VLOOKUP suddenly stop working when using data collected from the formula above?
The ideal output would be:
Code Year
AC19 2012
ABC2 2011
AC12 2012
ACF30 2013
GXC0 2011
So I am trying to add a secondary axis to the xyScatter plot but i keep getting an error stating objet doesnt support property or method
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlXYScatter
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).Name = "='Sheet2'!$A$9"
ActiveChart.SeriesCollection(1).XValues = "='Sheet2'!$B$9"
ActiveChart.SeriesCollection(1).Values = "='Sheet2'!$C$9"
With Sheets(3).ChartObjects("Chart 8")
.SeriesCollection(1).AxisGroup = xlSecondary (Error Occurs here)
.HasAxis(xlCategory, xlPrimary) = True
.HasAxis(xlCategory, xlSecondary) = True
.HasAxis(xlValue, xlPrimary) = True
.HasAxis(xlValue, xlSecondary) = True
.Axes(xlCategory, xlPrimary).CategoryType = xlAutomatic
.Axes(xlCategory, xlSecondary).CategoryType = xlAutomatic
End With
ActiveChart.SeriesCollection(1).Values = "='Sheet2'!$D$9"
Not sure why this is happening.
Hi I want to copy all the columns of a worksheet in to another worksheet one below the another.
Sheet A
Column A B C ....................BL
ValueA ValueB ValueC..................ValueBL
ValueA2 ValueB2 ValueC2.................ValueBL2
. . . .
. . . .
ValueA10 ValueB10 ValueC10...............ValueBL10
Target Sheet
Value A
.
.
Value A10
Value B
.
.
Value B10
Value C
.
.
Value C10
and so on. Basically each column has multiple values(rows) and there are hundreds of column. I want to copy all the columns one below the another. This will require a loop , but I don't understand as how to use it. Because
set ws1 As SheetA
set ws2 As Target
Lastcol= ......
Lastrow=......
For i=1 to Lastcol
ws1.Range("?" & lastrow).copy ws2.Range("a65536").End(xlUP).Offset(1,0)
Next i
I am not getting as what to put instead of "?". Appreciate any help. Thanks.
Im unsure if this is possible. I need certain Excel Cells to display a number (unique to each cell) when I haven't entered any data in the cell. Its used to assign sites on a 'map/list' and I want them to have a number when no one has been assigned the site.
I have a question here, I have an application and I need to add images to it. I have tried:
Sheet.Shapes.AddPicture(G_V.Prog_Dir+'pic.BMP',false,true, 190, 10+(15*rowcount), 100, 100 );
it works just fine, but I don't want to give parameters, I want to insert pictures to specified (and parametric) cells because I need to add picture to the last column of the page; this excel needs to be printed I must mention that. So I tried:
Sheet.Range['E'+inttostr(rowcount),'E'+inttostr(rowcount)].Select;
Sheet.Pictures.Insert(G_V.Prog_Dir+'pic.BMP');
It looks OK at first sight, however I think this code links images to the sheet. For example, I send the created Excel to another computer and these images cannot be seen (I don't recall the exact error) and when I searched it, I found out that receiving computer needs to have images at the exact path. As a solution to this, "Sheet.Shapes.AddPicture" recommended but as I stated before, I need another solution here.
I didn't see anybody experiencing this kind of problem, I hope someone helps me out.
I have the below code. In a nutshell- I have two files a reader and a data. I need multiple users to be able to access these files, but they are stored on a Box account, so the file directory is different for each user. I have created the code that SHOULD do this. It runs perfectly when I step through the code by hand using F8, or even pressing F5 after the workbook is open. However I need this to run on workbook open. When I open it I get the following error: Run-time error '1004': Application-defined or orbject-defined error. This happens at the
Set directoryRange = ImSapMacroWorkbook.Worksheets("Users").Range(Cells(1, 2), Cells(lastUser, 2))
I have no idea why it happens at this point, but it is not consistent so I haven't been able to pin down why this happens. If it matters this is the first sub run in the auto_open sub. No variables are even declared before this runs.
Sub GrabData()
Dim DataWorkbook As Workbook
Dim ImSapMacroWorkbook As Workbook
Dim copyRange As Range
Dim pasteRange As Range
Dim directoryRange As Range
Dim LastRow As Integer
Dim lastUser As Integer
lastUser = ThisWorkbook.Worksheets("Users").Range("A1048576").End(xlUp).Row
Set directoryRange = ThisWorkbook.Worksheets("Users").Range(Cells(1, 2), Cells(lastUser, 2))
Set MacroWorkbook = ThisWorkbook
On Error GoTo nextUser
For Each c In directoryRange
Set DataWorkbook = Workbooks.Open(c.Value)
Exit For
nextUser:
Next c
LastRow = DataWorkbook.Worksheets("Sheet1").Range("A1048576").End(xlUp).Row
Set copyRange = DataWorkbook.Worksheets("Sheet1").Range(Cells(2, 1), Cells(LastRow, 36))
Set pasteRange = MacroWorkbook.Sheets("input").Cells(2, 1)
Call clearData
copyRange.Copy
MacroWorkbook.Activate
pasteRange.Select
ActiveSheet.Paste
End Sub
I would like to compare two columns but have conditions with them.
My Data:
Coloumn A Mon, 23 Mar 2015 17:56:40 Mon, 23 Mar 2015 17:57:03 Tue, 24 Mar 2015 08:51:48 Tue, 24 Mar 2015 06:20:57 Tue, 24 Mar 2015 11:42:07 Tue, 24 Mar 2015 11:42:41 Tue, 24 Mar 2015 11:43:11 Tue, 24 Mar 2015 11:40:31 Tue, 24 Mar 2015 11:56:13 Tue, 24 Mar 2015 18:55:38 Wed, 25 Mar 2015 08:45:36 Wed, 25 Mar 2015 12:59:24 Wed, 25 Mar 2015 13:50:26 Wed, 25 Mar 2015 13:58:20
Coloumn B
4802xxxxx
4802XXXXX
4802XXXXX
4802XXXXX
4802XXXXX
I have to 2 sheets that have the same format, and would like to compare the coloumn B(sheet1) with coloumB (sheet2). And make sure the calls came in at the same time. USING the Coloumn A. Compare Coloumn A (sheet1) with ColoumnA (sheet2). All this at the same time.
I am exporting data from one workbook to another workbook to T13:Tlastrow
This data, from column F in my workbook where I run this macro, I want to be put into {nyckel="TEXT HERE";} in column T in the "new" workbook, starting from row 13 (T13).
I am stuck here. So would really appreciate some help/solution. Thanks!
Sub CopyData()
Dim wkbCurrent As Workbook, wkbNew As Workbook
Set wkbCurrent = ActiveWorkbook
Dim valg, c, LastCell As Range
Set valg = Selection
Dim wkbPath, wkbFileName, lastrow As String
Dim LastRowInput As Long
Dim lrow, rwCount, lastrow2, LastRowInput2 As Long
Application.ScreenUpdating = False
' If nothing is selected in column A
If Selection.Columns(1).Column = 1 Then
wkbPath = ActiveWorkbook.Path & "\"
wkbFileName = Dir(wkbPath & "CIF LISTEN.xlsm")
Set wkbNew = Workbooks.Open(wkbPath & "CIF LISTEN.xlsm")
'Application.Run ("'C:\Users\niclas.madsen\Desktop\TEST\CIF LISTEN.xlsm'!DelLastRowData")
LastRowInput = Cells(Rows.count, "A").End(xlDown).Row
For Each c In valg.Cells
lrow = wkbNew.Worksheets(1).Range("B1").Offset(wkbNew.Worksheets(1).Rows.count - 1, 0).End(xlUp).Row + 1
lastrow2 = Range("A" & Rows.count).End(xlUp).Row
lastrow3 = Range("T" & Rows.count).End(xlUp).Row
wkbCurrent.ActiveSheet.Range("E" & c.Row).Copy Destination:=wkbNew.Worksheets(1).Range("A" & lrow)
wkbCurrent.ActiveSheet.Range("A" & c.Row).Copy Destination:=wkbNew.Worksheets(1).Range("B" & lrow)
wkbCurrent.ActiveSheet.Range("F" & c.Row).Copy Destination:=wkbNew.Worksheets(1).Range("T" & lrow)
' Standard inputs
wkbNew.Worksheets(1).Range("D13:D" & lastrow2).Value = "Ange referens och period"
wkbNew.Worksheets(1).Range("E13:E" & lastrow2).Value = "99999002"
wkbNew.Worksheets(1).Range("G13:G" & lastrow2).Value = "EA"
wkbNew.Worksheets(1).Range("H13:H" & lastrow2).Value = "2"
wkbNew.Worksheets(1).Range("M13:M" & lastrow2).Value = "SEK"
wkbNew.Worksheets(1).Range("N13:N" & lastrow2).Value = "sv_SE"
wkbNew.Worksheets(1).Range("P13:P" & lastrow2).Value = "TRUE"
wkbNew.Worksheets(1).Range("Q13:Q" & lastrow2).Value = "TRUE"
wkbNew.Worksheets(1).Range("S13:S" & lastrow2).Value = "Catalog_extensions"
'wkbNew.Worksheets(1).Range("T" & lastrow3).Value = "{Nyckelord=" & wkbNew.Worksheets(1).Range("T" & lastrow3).Value & ";}"
Next
LastRowInput2 = wkbNew.Worksheets(1).Range("T" & wkbNew.Sheets("Sheet1").UsedRange.Rows.count + 1).End(xlUp).Row
For i = 0 To LastRowInput2 - 13
wkbNew.Worksheets(1).Range("T" & 13 + i).Value = "{Nyckelord=" & wkbNew.Worksheets(1).Range("T" & 13 + i).Value & ";}"
Next i
' wkbNew.Close False
' Find the number of rows that is copied over
wkbCurrent.ActiveSheet.Activate
areaCount = Selection.Areas.count
If areaCount <= 1 Then
MsgBox "The selection contains " & Selection.Rows.count & " suppliers."
' Write it in A10 in CIF LISTEN
wkbNew.Worksheets(1).Range("A10").Value = "COMMENTS: " & Selection.Rows.count & " Suppliers Added"
Else
i = 1
For Each A In Selection.Areas
'MsgBox "Area " & I & " of the selection contains " & _
a.Rows.count & " rows."
i = i + 1
rwCount = rwCount + A.Rows.count
Next A
MsgBox "The selection contains " & rwCount & " suppliers."
' Write it in A10 in CIF LISTEN
wkbNew.Worksheets(1).Range("A10").Value = "COMMENTS: " & rwCount & " Suppliers Added"
End If
wkbNew.Worksheets(1).Activate
Application.ScreenUpdating = True
Else
MsgBox "Please select cell(s) in column A", vbCritical, "Error"
Exit Sub
End If
End Sub
I am trying to match data in two excel workbook
workbook 1, sheet 1 has
A B
1 abcde 0
2 efghp 0
3 ykiyp 2
4 abcdg 1
workbook 2, sheet 1 has
A B
1 abcdk 9
2 efgho 9
3 yjiyp 5
4 ykiyp 0
so if the first four alphabets match in column A of workbook 2 to that of workbook 1, then the value in column B of workbook 1 must be equal to that in workbook 2
Such That the final result will be
A B
1 abcde 9
2 efghp 9
3 ykiyp 0
4 abcdg 9
Any method would do vba or any other
I'm new to programming world. I want to know the value of "sheet.nrows".
i'm writing the program for creating and feeding record in excel file. and somehow i manage to so by "try, error , modify" method, but i got stuck in understading the code i get from detecting blank cell in excel it work fine and doing its job of creating the serial no.
scenario 1:
book_r = xlrd.open_workbook("text.xls")
first_sheet = book_r.sheet_by_name("Form")
row_count = 1
for row_index in range(first_sheet.nrows):
value = first_sheet.cell(rowx=row_index, colx=0).value
if value != "Sr.No." or value != range(1, 200):
sheet1.write(row_index + 1, 0, row_count)
row_count += 1
it gives output in excel file like :
Sr.No.
1
2
3
4
5
6
scenario 2:
sheet1.write(row_index + 2, 0, row_count)
it gives output in excel file like
Sr.No.
1
2
3
4
5
6
with a blank after Sr.no.
i'm not getting why the gap arises after Sr.no. and as u increase the row value in sheet1.write(row_index + 1, 0, row_count) gap also increases
i want to get the last value after / in excel.
http://ift.tt/1ylRXwm
http://ift.tt/1OInPFZ
http://ift.tt/1ylRVEX
from the above link i want to separate the pagename for eg. from 1 url output should be www.google.com/new/ 2nd http://ift.tt/19o4gCe and so i used RIGHT formula but we can define only static. pls. provide if any one has the solution.
I get spelling suggestions with
GetSpellingSuggestions
but how to get Grammar suggestions?
I've been stuck on this for the past couple of hours and it's driving me nuts. I'm trying to enter this code in cell B2:
=IF(OR(C2="Yes",D2="Yes",E2="Yes",F2="Yes",G2="Yes",H2="Yes",I2="Yes"),"Yes",IF(AND(C2="No",D2="No",E2="No",F2="No",G2="No",H2="No",I2="No"),"No"))
I recorded the macro, copy-and-pasted the output, and the cell still remains blank. Here is the output from the recording:
Range("B2").Select
ActiveCell.FormulaR1C1 = _
"=IF(OR(RC[1]=""Yes"",RC[2]=""Yes"",RC[3]=""Yes"",RC[4]=""Yes"",RC[5]=""Yes"",RC[6]=""Yes"",RC[7]=""Yes""),""Yes"",IF(AND(RC[1]=""No"",RC[2]=""No"",RC[3]=""No"",RC[4]=""No"",RC[5]=""No"",RC[6]=""No"",RC[7]=""No""),""No""))"
I've also tried Range("B2").Formula, without success.
Any suggestions? Thanks!
I have 4 columns in an excel file, column A contains the values of kilometers starting from 0 to 300, column b contains the letters, PH, PP etc. I want to search "PP" within every 1.6km interval i.e. 0-1.6, 1.6-3.2... and the function should return for every 0-1.6, 1.6-3.2... if there is a word "PP" in the other column.
I have the following scala code to load a file:
import org.apache.poi.ss.usermodel.WorkbookFactory
import scala.collection.JavaConversions._
val wb = WorkbookFactory.create(new File(s"/tmp/$filename"))
val sheet = wb.getSheet("Pricelist")
println(s"${sheet.getLastRowNum}")
If I pass a xls, the last row number is given:
However on the xlsx (same file, but then saved as xlsx) it returns -1. I have tried various files generated by different software (office/LibreOffice) however it still keeps returning -1. Anyone came across this before as I found little about this issue.
Have a problem. I convert xls table by "Excel.Workbook.SaveAs" and in final txt file all numbers that are bigger than 999 are showed like: "2,300" with coma and quotes. I convert xls table in excel application by clicking "save as" and choosing txt format (with tab separators) and in final txt file all numbers that are bigger than 999 are showed like: 2 300 without quotes and coma. I need to convert that with "save as" method and recieve normal txt file without quotes and comas. I tried to use all text formats in the "save as" method and that haven't gave me necessary result. Thank you in advance!
I have a pivot table that displays agencies in rows, products in columns and sales units as values. I have to make up a report in this format:
Data is coming from an SQL Server Analysis Services and the "Estimate 2015" measure is a different measure (but uses the same dimensions and granularity so it is in fact possible to display the values side by side).
I could add a separate pivot table for the estimates, but then filtering or sorting the two tables will make them loose sync.
Is it possible to somehow align or combine pivot tables with different measures nicely?
Im trying loop over a cell range (single column) that contains names and codes containing the string "cgl".
I can loop over the range and identify the cells but i was trying to use a collection to store the name and the row number so i can return it. But i am not setting the value correctly and probably not retrieving the value correctly (the msg box command does not work after this point). I am using excel 2007.
I followed examples found in other q's, online help, but what is the correct way to use collection?
Function DHNameRows(rng As Range, name)
' eg name = david
Dim item As Range
Dim staff As Collection
Set staff = New Collection
For Each item In rng
found = InStr(item.Value, "CGL")
If found = 0 Then
'item value should equal david
MsgBox item.Value
staff.Add item.Value, item.Row
End If
Next
MsgBox staff.item(name)
DHNameRows = staff.item(name)
End Function
I have managed to do the following vba codes but i am having issues with the restrict filter part where its picking up emails with RE: within the Subject for the 10pm and 5pm emails.
I would also like to copy the data within each open workbook into another workbook which is saved in another directory path.
The first data copied should include the header row and the remaining data copied from all other workbooks should not include the header row. The data needs to be pasted below the last row plus 1 into the workbook which is saved in another directory path
I would only like the current region to be copied from each workbook.
Sub DownloadAttachmentUnreadSubjectEmail()
Const olFolderInbox = 6
Const AttachmentPath As String = "\\lcwfsv1\users\e494356\My Documents\Outlook Test\"
Dim oOlAp As Object, oOlns As Object, oOlInb As Object
Dim oOlItm As Object, oOlAtch As Object
Set objOutlook = CreateObject("Outlook.Application")
Set objNamespace = objOutlook.GetNamespace("MAPI")
Set objFolder = objNamespace.GetDefaultFolder(olFolderInbox)
Set objFolder = objFolder.Folders("**CLIENT ISSUES**").Folders("*Daily Reports").Folders("1. Open Trade Report")
Set colItems = objFolder.Items
Set colFilteredItems1 = colItems.Restrict("[Unread] = True AND [Subject] = '10PM FXC Email notification'")
Set colFilteredItems2 = colItems.Restrict("[Unread] = True AND [Subject] = '5PMFXC Email notification'")
'~~> Check if there are any actual unread 10PM FXC emails
If colFilteredItems1.Count = 0 Then
MsgBox "NO Unread 10PM Email In Inbox"
Else
'~~> Extract the attachment from the 1st unread email
For Each colItems In colFilteredItems1
'~~> Check if the email actually has an attachment
If colItems.Attachments.Count <> 0 Then
For Each oOlAtch In colItems.Attachments
'~~> save the attachment and open them
oOlAtch.SaveAsFile AttachmentPath & oOlAtch.Filename
Set wb = Workbooks.Open(Filename:=AttachmentPath & oOlAtch.Filename)
Next oOlAtch
Else
MsgBox "10PM email doesn't have an attachment"
End If
Next colItems
End If
'~~> Check if there are any actual unread FXC Email emails
If colFilteredItems2.Count = 0 Then
MsgBox "NO Unread 5PM Email In Inbox"
Else
'~~> Extract the attachment from the 1st unread email
For Each colItems In colFilteredItems2
'~~> Check if the email actually has an attachment
If colItems.Attachments.Count <> 0 Then
For Each oOlAtch In colItems.Attachments
'~~> save the attachment and open them
oOlAtch.SaveAsFile AttachmentPath & oOlAtch.Filename
Set wb = Workbooks.Open(Filename:=AttachmentPath & oOlAtch.Filename)
Next oOlAtch
Else
MsgBox "5PM email doesn't have an attachment"
End If
Next colItems
End If
End Sub
