I want to copy & paste a data ,that is stored as string,in selected cells but most of the times i get some casting or conversion errors and i want this code to be in for loop so that code must not be lengthy & must look simple.At last It shouldn't be hardcoded.So can any body help me regarding this in vb.net.I will be highly obliged.
the following code open a workbook...selects cell A1 copies its data to the clipboard....then selects cell B1 and paste the text from the clipboard:
Dim xlapp As New XL.ApplicationDim xlbk As XL.Workbook = xlapp.Workbooks.Open("C:\MyWorkbook.xls")Dim xlsheet As XL.Worksheet = CType(xlbk.Sheets(1), Microsoft.Office.Interop.Excel.Worksheet) xlsheet.Range("a1").Select() Clipboard.SetText(CStr(xlsheet.Range("a1").Value)) xlsheet.Range("B1").Select() xlsheet.Range("B1").Value = Clipboard.GetTextxlbk.Save() xlbk.Close() xlapp.Quit() System.Runtime.InteropServices.Marshal.ReleaseComObject(xlapp) |
Thanks for replying,I will definitly post the code.
Please also suggest me book on vb.net excel so that i can have a proper knowledge in it.
Hi Dman & DeborahK,
Thanks for your kind support.I have executed that code successfully & posting it so that it might help somebody else.It doesn't make use of "Clipboard Methods" to copy & paste data in excel sheets and getting error was the result of just improper use of codes i had done.
It selects the range of 1st column,loop through rows,selects the value & put in string variable,matches the string with blank cells value & copy it in next blank cells.
Sub fillBlankcells()
opObjSh.Range("A2:A68").Select()
a = 0
For l = 1 To opObjSh.UsedRange.Rows.Count()
str = opObjSh.Cells(l, 1).value()
If str = "" Then
lastStr = opObjSh.Cells(l - 1, 1).value() // It takes the cell's value of the row before blank cells row.
opObjSh.Cells(l, 1).Value = lastStr // It copies the value in to blank cells
a = a + 1 //counts the no. of cells filled
End IfNextMessageBox.Show("Filled cells are " & a, "Information", MessageBoxButtons.OK, MessageBoxIcon.Information, MessageBoxDefaultButton.Button1, MessageBoxOptions.DefaultDesktopOnly)
End Sub
Regards
Abhishek
