|
|
Hi,
How to export Access database to Excel with a button click in a VB project? Thank you.
modified 19-Mar-19 7:38am.
|
|
|
|
|
|
whats wrong with this code? throws COM error. I added interop reference. thanks.
{Retrieving the COM class factory for component with CLSID {00020819-0000-0000-C000-000000000046} failed due to the following error: 80040154 Class not registered (Exception from HRESULT: 0x80040154 (REGDB_E_CLASSNOTREG)).}
Private Sub excel_Click(sender As Object, e As EventArgs) Handles excel.Click
Threading.Thread.CurrentThread.CurrentCulture = Globalization.CultureInfo.CreateSpecificCulture("en-US")
Dim Xa As New Excel.Application
Dim Wb As New Excel.Workbook
Dim Ws As New Excel.Worksheet
Wb = Xa.Workbooks.Add
Ws = Wb.Worksheet("Sheet1")
Ws.Cells(0, 0) = "Column_1"
Ws.Cells(0, 1) = "Column_2"
Ws.Cells(1, 0) = "Value_1"
Ws.Cells(1, 1) = "Value_2"
Xa.Visible = True
Ws = Nothing : Wb = Nothing : Xa = Nothing
End Sub
|
|
|
|
|
The message is telling you that you do not have Excel installed on that system.
|
|
|
|
|
Excel is installed in system
|
|
|
|
|
You can check that the correct CLSID is registered on the system. Run regedit and navigate to KKEY_CLASSES_ROOT/CLSID. You can then check that the GUID above is present.
|
|
|
|
|
Or that message can come up if your app is compiled/running as 64-bit and Office 32-bit is installed.
Or the other way around.
|
|
|
|
|
I double checked and both are x64.
|
|
|
|
|
As I said, use EPPlus[^] instead of Office Interop.
EPPlus will work even if Excel isn't installed. Or if you run into the problem of trying to automate 32-bit Excel from a 64-bit application (or vice-versa).
It will also work in an unattended application like ASP.NET or a Windows service - unlike Office Interop:
Microsoft does not currently recommend, and does not support, Automation of Microsoft Office applications from any unattended, non-interactive client application or component (including ASP, ASP.NET, DCOM, and NT Services), because Office may exhibit unstable behavior and/or deadlock when Office is run in this environment.
Free programs like LibreOffice will happily open .xlsx files, but won't work with Office Interop. By using EPPlus, you could save the cost of an Office license.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
That's a great way to fix this. Thank you Richard Deeming.
In meantime I managed to generate Excel export with below codes and wanted to share:
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
Dim xlApp As Excel.Application
Dim xlWorkBook As Excel.Workbook
Dim xlWorkSheet As Excel.Worksheet
Dim misValue As Object = System.Reflection.Missing.Value
Dim i As Integer
Dim j As Integer
xlApp = New Excel.Application
xlWorkBook = xlApp.Workbooks.Add(misValue)
xlWorkSheet = xlWorkBook.Sheets("Sheet1")
For i = 0 To dgvData.RowCount - 2
For j = 0 To dgvData.ColumnCount - 1
xlWorkSheet.Cells(i + 1, j + 1) = dgvData(j, i).Value.ToString()
Next
Next
xlWorkSheet.Columns.AutoFit()
xlWorkSheet.Rows.AutoFit()
xlWorkSheet.SaveAs("C:\xxx\xxx.xlsx")
xlWorkBook.Close()
xlApp.Quit()
releaseObject(xlApp)
releaseObject(xlWorkBook)
releaseObject(xlWorkSheet)
MessageBox.Show("xxx C:\xxx\xxx.xlsx", "xxx", MessageBoxButtons.OK, MessageBoxIcon.Information)
End Sub
Private Sub releaseObject(ByVal obj As Object)
Try
System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
obj = Nothing
Catch ex As Exception
obj = Nothing
Finally
GC.Collect()
End Try
End Sub
End Class
There are 14 columns and around 2000 row at current Access db I use.
It tooks 4-5 mins to generate Excel report with 8th gen i5 processor/16gb ram PC.
|
|
|
|
|
Interesting. So how did you resolve the original problem?
|
|
|
|
|
Hi I actually didnt do anything different than first code block I have shared. I double checked interop .dll and basicly it was there.
|
|
|
|
|
textbox allowing the user to enter more htan one number
|
|
|
|
|
Did you want to describe a problem you're having or ask a question?
What you posted makes no sens and comes across like a fast food order.
|
|
|
|
|
Hi Guys,
How to type into 2 different Textbox at same time ?
Basicly I want to type into textbox1 and it reflects to textbox2 at same time.
Sorry if I couldn't elaborate further since I am new to this.
Thank you.
|
|
|
|
|
Take take TextChanged-Event from TextBox1.
Inside this Handler-Method you write the content of TextBox1 to TextBox2.
|
|
|
|
|
I am sending information between two vb.net applications using named pipes; serializing the information object, sending it to the second app, then deserializing it. All works well, but I am finding that I need to send more than one type of object. However, I cannot tell the type of serialized object that has been received until *after* it has been deserialized.
Is there a method of identifying the object type *before* I deserialize it so I can then cast it into the proper object type when received?
|
|
|
|
|
Perhaps you should what you have done and how you have done it (provide some code) and then we could discuss the possibilities ...
|
|
|
|
|
GetType.ToString, and send that before you send your serialized stuff. How you gonna cast it, is beyond me, I just woke up
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
"If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
|
|
|
|
|
In situations where people jump into sending objects "over the wire" for the first time, especially where noobs start sending images, is that they just go straight for sending the object or image. Well, if you just picked up the phone and dialed a number, the other side answered, and you just said "this image", the person on the other end of the call isn't going to know what you're talking about or what to do with that information. You haven't set up any context for a conversation. You don't have any protocol in place to describe what you're doing.
You have to build your own communication protocol for setting up and maintaining both a conversation and subject context.
The conversation should not start with shoving any old object onto the wire. You have to setup a context for conversation, or what you're going to be talking about. For example, start with "I'm going to send you 'such and such' object. The next x number of bytes will be the data for that object". Obviously, you don't send the full text of what I just posted. You send data that means what is in the quotes. Now you have a context to work with. The receiver will have a better idea of what it's looking at when that data shows up and what to do with it.
Now, you also have to fill in the details for what to do when the receiver either gets too many bytes, or not enough, and other communication failures.
|
|
|
|
|
Gents,
Please I need your help to code the subtotal sum in Textbox for in case you have a client that is buying multiples product with in one bill.
I been trying this for long period.
regards
|
|
|
|
|
Member 14179211 wrote: I been trying this for long period. Trying what? Please edit your question and add some proper detail; you have not explained which framework you are using or what is the structure of the data that you have to create the sum.
|
|
|
|
|
Hi Guys,
How to add 3 IF conditions to below code part ?
else
If txtFullname.Text <> txtFullname.Tag Then
strSQL = _
" SELECT Count(tblContact.Fullname) AS CountFullname FROM tblContact " & _
" WHERE Fullname = " & "'" & txtFullname.Text & "'"
If DuplicateName(strSQL) Then
MessageBox.Show("test.", "warning",
MessageBoxButtons.OK, MessageBoxIcon.Warning)
txtFullname.Focus()
Exit Sub
End If
End If
|
|
|
|
|
RedPandinus wrote: How to add 3 IF conditions to below code part ? What do you mean, and what conditions are you referring to? You should also learn how to use proper parameterised queries for SQL. Using string concatenation is dangerous.
|
|
|
|