|
Which version of Office do your interop-dll's target? If you are using the files from Office 2007, then it probably will not be able to read a 2013 file, since Excel 2013 did not exist at that time.
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
Thanks for the response. I have excel 2013 and the code executes well in vb 2008 and excel 2007. From the references tab, Microsoft Excel Object Library 15.0 exists. Is there anything I have to do again, please? If you could link me to any code concerning that, i will be glad. Thanks
|
|
|
|
|
Should be the correct version. In your loop, add the statement below;
System.Diagnostics.Debug.WriteLine(ExcelSheet.cells(i, 1).Value)
After that, run the code again and see if any values are printed to the output-screen. If there's no data, then you either have no data at that point, data might get converted from the wrong type, or you might be looking at another file than you think you are.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
ExcelSheet = Nothing
ExcelBook = Nothing
ExcelApp = Nothing
I could be wrong, but I suspect when you set all those objects to Nothing, the Excel app is removed from memory and hence is unable to display your worksheet.
Perhaps you can comment those lines out and try?
Happiness will never come to those who fail to appreciate what they already have. -Anon
|
|
|
|
|
Many thanks. I commented out those lines, but nothing displays. I dont know what to do now
|
|
|
|
|
Benniiit wrote: but nothing displays
Does an Excel window open? Do you mean the worksheet has no data in it?
Happiness will never come to those who fail to appreciate what they already have. -Anon
|
|
|
|
|
Thanks for the message, big bro. Yes, an excel window opens and the worksheet has no data in it.
|
|
|
|
|
Well, I guess then you have to run it through the debugger and make sure your DataGridView actually has data in it.
Happiness will never come to those who fail to appreciate what they already have. -Anon
|
|
|
|
|
I hope you should just be able to use it as is:
Sub datagridToExcel(filename As String, source As DataGridView, header As String, footer As String, convertToXls As Boolean)
' use a static file path for the moment
'Dim BackupPath As String
Dim datatable As DataTable = CType(source.DataSource, Data.DataTable)
Dim Headers As String = String.Empty
Dim TempRow As String = String.Empty
Dim SeparationMarker As String = ControlChars.Tab
'Dim FolderBrowserDialog1 As New System.Windows.Forms.FolderBrowserDialog()
'FolderBrowserDialog1.ShowDialog()
'BackupPath = FolderBrowserDialog1.SelectedPath
If filename <> String.Empty Then
If File.Exists(filename) Then
File.Delete(filename)
End If
Dim SW As New System.IO.StreamWriter(filename, False, System.Text.Encoding.Unicode)
' get the column header names and add to the backup file
Dim i As Integer = 0
If Not header = String.Empty Then
SW.WriteLine(header)
SW.WriteLine(String.Empty)
End If
' add the headers
For i = 0 To source.Columns.Count - 1
Headers &= source.Columns(i).Name & SeparationMarker
Next
SW.WriteLine(Headers.ToString)
' add each row
For Each row As DataGridViewRow In source.Rows
For a As Integer = 0 To datatable.Columns.Count - 1
'check if it's null
If IsDBNull(row.Cells(a)) Then
TempRow &= String.Empty & SeparationMarker
Else
Dim fmt As String = String.Empty
For gridIdx As Integer = 0 To source.ColumnCount - 1
If source.Columns(gridIdx).Name = datatable.Columns(a).ColumnName AndAlso source.Columns(gridIdx).DefaultCellStyle.Format() <> String.Empty Then
fmt = "{0:" + source.Columns(gridIdx).DefaultCellStyle.Format() + "}"
Exit For
End If
Next
If fmt <> String.Empty Then
Dim tmpdat As String = String.Format(fmt, row.Cells(a).Value)
tmpdat = tmpdat.ToString.Replace(SeparationMarker, String.Empty)
TempRow &= tmpdat & SeparationMarker
Else
TempRow &= row.Cells(a).Value.ToString.Replace(SeparationMarker, String.Empty) & SeparationMarker
End If
End If
Next
TempRow = TempRow.Replace(vbCrLf, String.Empty).Replace(vbCr, String.Empty).Replace(vbLf, String.Empty)
SW.WriteLine(TempRow.ToString)
TempRow = String.Empty
Next
If Not footer = String.Empty Then
SW.WriteLine(String.Empty)
SW.WriteLine(footer)
End If
SW.Close()
If convertToXls Then
Try
If File.Exists(filename.Substring(0, filename.Length - 4) + ".xlsx") Then
File.Delete(filename.Substring(0, filename.Length - 4) + ".xlsx")
End If
Dim xlsapp As New Microsoft.Office.Interop.Excel.Application
Dim xlswb As Microsoft.Office.Interop.Excel.Workbook
xlsapp.Visible = False
xlswb = CType(xlsapp.Workbooks.Open(filename), Excel.Workbook)
CType(xlswb.Worksheets(1), Excel.Worksheet).Activate()
xlsapp.ActiveWindow.SplitColumn = 0
xlsapp.ActiveWindow.SplitRow = 1
xlsapp.ActiveWindow.FreezePanes = True
CType(xlswb.Worksheets(1), Excel.Worksheet).Cells.AutoFilter(1)
CType(xlswb.Worksheets(1), Excel.Worksheet).Columns.AutoFit()
xlswb.SaveAs(filename.Substring(0, filename.Length - 4) + ".xlsx", Excel.XlFileFormat.xlOpenXMLWorkbook, Nothing, Nothing, Nothing, Nothing, Excel.XlSaveAsAccessMode.xlExclusive, Nothing, Nothing, Nothing, Nothing, Nothing)
xlswb.Close()
xlsapp.Quit()
File.Delete(filename)
MsgBox("data exported to:" + vbNewLine + filename.Substring(0, filename.Length - 4) + ".xlsx", vbInformation, "exported to excel")
Catch ex As Exception
End Try
Else
MsgBox("Data exported to:" + vbNewLine + filename, vbInformation, "Exported to text file")
End If
Else
MsgBox("Data not exported.", vbInformation, "Exported Cancelled")
End If
End Sub
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
|
|
|
|
|
Hi all,
I'm looking for a good library that is able to read/detect barcodes inside a pdf file.
Until now I only found paying ones who are quite expensive and the customer where I work for is a small company that starts with digital archiving but where we need to be able to detect barcodes in tiff and pdf files.
Can anybody advise me a component for this that is not paying ?
Kind regards,
Vincent
|
|
|
|
|
Something like this[^]?
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
I need a help to add a DTPicker in a column of VSFlexGrid or grid in vb6.0.
Is anyone have suggestion or answer ? Plzz reply as soon as possible.
Thank You.
|
|
|
|
|
VB6 is deprecated; it is no longer actively supported.
If memory serves correctly, you'd have to position the control by hand to have it on the location in the grid where you want it.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
How To Set H-Scrolling And V-Scrolling With AspxpivoteGrid Devexpress ver 12.1
|
|
|
|
|
Have you tried the DevExpress forums? It is a commercial control, and they have a helpdesk to support their stuff.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
This is related to my post in the database forum from yesterday.
So I took the knowledge from my post below, and applied it to all my other stuff that required it and everything works fine. But I have a question.
I'm led to believe that you can do multiple Joins, but can only do 1 Group By
I have this sql linq statement, and it would be cool or efficient to just get it all in 1 call.
Right now, I just made another statement to get the vendor info.
I not even sure if it's possible to do.
Here's what I have right now.
I thought Group By pi.PartNumber, vi.VendorID Into productsSold would do the trick
perhaps it should be Group By { pi.PartNumber, vi.VendorID } Into productsSold
But then I understand the first Select product = productsSold, the group,
It seems you can only do it once, or you have to group all the Joins into it somehow. This is the part I'm fuzzy on.
Dim m_startDate As DateTime = New DateTime(2015, 1, 1, 0, 0, 0, 0)
Dim m_stopDate As DateTime = New DateTime(2015, 8, 31, 23, 59, 59, 999)
Dim p_PartNumber as string = "24-FLP44"
using context As New DBContext()
Dim pResult = _
(
From cohc In context.Order_History_Cart
Join pi In context.ProductInfo On cohc.PartNumber Equals pi.PartNumber
Join vi In context.ProductInfo_Vendors On pi.VendorID Equals vi.VendorID
Where cohc.PartNumber = p_PartNumber _
And cohc.OrderDate >= m_startDate _
And cohc.OrderDate <= m_stopDate _
Order By cohc.PartNumber
Group By pi.PartNumber Into productsSold = Group,
totalQty = Sum(cohc.Qty),
totalCost = Sum(cohc.Qty * cohc.Cost),
totalPrice = Sum(cohc.Qty * cohc.Price)
Select product = productsSold.Select(Function(m) m.pi).FirstOrDefault(), totalQty, totalCost, totalPrice
Select
product.PartNumber,
product.MPartNumber,
product.Cost,
product.Price,
totalQty,
totalCost,
totalPrice,
vendor.contactname,
vendor.streetAddress1
vendor.streetAddress2
)
pResult.Dump()
end using
|
|
|
|
|
This is a different problem, but basically the same as I posted in Database.
The difference is that my issues are VB based, and not really database related.
So I can sort of see how to do it in C#, but it doesn't translate to VB EF 6
I can't figure it out. I'm close on it.
So I wrote this years ago, works fine in TSQL. It's an Order Table, that has a SalesTax State Code and SalesTax collected columns. It joins a Table called SalesTaxStates, on the StateCode, and the TSQL Sums up the Order SubTotal and SalesTax, and then gets the StateName, TaxRate.
"DECLARE @startDate AS DATE; " & _
"DECLARE @stopDate AS DATE; " & _
"SET @startDate = DATETIMEFROMPARTS(@Year, 1, 1, 0, 0, 0, 0); " & _
"SET @stopDate = DATETIMEFROMPARTS(@Year, @LastMonth, @LastDay, 23, 59, 59, 999); " & _
"SELECT " & _
" sts.StateCode " & _
", sts.StateName " & _
", sts.HasSalesTax " & _
", SUM(oh.SubTotal) AS SubTotal " & _
", sts.StateTaxRate " & _
", SUM(oh.SalesTax) AS SalesTax " & _
" From ORDER_HISTORY oh " & _
"LEFT JOIN Shipping_TaxStates sts " & _
"ON sts.StateCode = oh.SalesTaxState " & _
"WHERE oh.OrderDate > @startDate " & _
"AND ohOrderDate < @stopDate " & _
"GROUP BY sts.StateCode, sts.StateName, sts.StateTaxRate, sts.HasSalesTax " & _
"ORDER BY sts.StateName "
I have tried many things today.
I tried a Group Join Into xxx = group
I tried Group By oh = new With / Select New With group.Sum(function(m) m.Qty)
In each case, I can the oh stuff and the sums, but I can't access the Join columns for taxRate
So below is a stable version that works with oh. I figure I'll post something that works rather than complete slop.
Dim m_startDate As DateTime = New DateTime(p_Year, 1, 1, 0, 0, 0, 0)
Dim m_stopDate As DateTime = New DateTime(p_Year, p_LastMonth, p_LastDay, 23, 59, 59, 999)
Dim context As New DBContext()
pResults = _
(
From oh In context.Order_History
Where oh.OrderDate >= m_startDate _
And oh.OrderDate <= m_stopDate _
And oh.OrderStatus = "COMPLETED"
Group By oh = New With
{
oh.SalesTaxState,
oh.SubTotal,
oh.SalesTax
} Into Group
Select New With
{
.StateCode = oh.SalesTaxState,
.subTotal = Group.Sum(Function(m) m.SubTotal),
.salesTax = Group.Sum(Function(m) m.SalesTax)
}
)
|
|
|
|
|
Something like this should work:
From oh In context.Order_History
Join sts In context.Shipping_TaxStates On oh.SalesTaxState Equals sts.SalesTaxState
Where oh.OrderDate >= m_startDate And oh.OrderDate <= m_stopDate And oh.OrderStatus = "COMPLETED"
Group By sts.SalesTaxState Into GroupedHistory = Group, SubTotal = Sum(oh.SubTotal), SalesTax = Sum(oh.SalesTax)
Select State = GroupedHistory.Select(Function(m) m.sts).First(), SubTotal, SalesTax
Select State.StateCode, State.StateName, State.HasSalesTax, SubTotal, State.StateTaxRate, SalesTax
Order By StateName
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Oh I see what I was missing now.
The Select statement, that selects the group of records first from the Group
And then do the select.
I was baffled, kept getting missing type, missing ), conversion failed for Decimal, etc.
I would of never figured it out, not many vb examples online, plus the VB was slightly different like = Group.
Thanks Richard!
|
|
|
|
|
Hello,
I am looking for a way for excel to read the cpu usage of a particular application
|
|
|
|
|
|
This is what I was looking for... Thanks for your help
Not sure why I got downvoted either. Weird
|
|
|
|
|
Member 11915345 wrote: Not sure why I got downvoted either.
Probably because you did not show that you had attempted anything, reversed.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Mika Wendelius wrote: As a side-note, not sure why you got downvoted...
I down-voted the question, and having slept on it, I stand by my decision.
The OP has clearly made no effort to answer their own question. If you paste the question into Google, the answer appears within the first 10 results.
But why bother asking Google, when you can paste your question into a forum and get someone else to ask Google, sift through the search results, and present the answer to you on a silver plate? After all, this site was created to be a human search-engine, providing help to those who can't be bothered to help themselves, wasn't it? 
And no, I didn't post an explanation for the down-vote. In my experience, that only leads to insults or arguments from the OP. I have yet to see anyone accept the criticism and adjust their approach to the site.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Thanks for the explanation!
I agree that the OP could have Googled and tried to find solutions. However, another aspect is that if you don't quite know what you're looking for where do you start from? For experienced people it's often obvious what search terms to use and what to search for, but what if you're not familiar with the technology in the first place.
Don't get me wrong, I completely agree that people should do the background work properly before asking incomplete questions.
Especially I appreciate that you took time to explain the reason behind the downvote since it got me thinking about the threshold of answering a question at all.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
