|
|
I have a visual basic application developed to read an EXCEL file. It is written with late binding because my development machine has Microsoft.Office.Interop.EXCEL 14 and the production machines uses Microsoft.Office.Interop.EXCEL 12.
We get an error opening the workbook on the production machine but not on the development machine.
It only happens for files that have the "originated from an internet" ADS.
Is there an EXCEL or FILE setting I have missed?
Dim oXL As Object = CreateObject("Excel.Application")
Dim oWBK As Object = oXL.workbooks
Dim oWS As Object = CreateObject("Excel.Sheet")
oXL.AutomationSecurity = 3
oXL.DisplayAlerts = False
oXL.Visible = False
oXL.AskToUpdateLinks = False
oWBK = oXL.Workbooks.Open(Path.Combine(l_s_SourcePath, l_s_SourceFileName), UpdateLinks:=False, ReadOnly:=True, CorruptLoad:=1)
|
|
|
|
|
Which version Excel file is causing the problem?
|
|
|
|
|
|
No, I asked which version Excel files, .xls or .xlsx
|
|
|
|
|
doesn't matter. They both fail in production, both open no problem in dev.
|
|
|
|
|
Sorry, without more information it is impossible to guess. I would recommend either downgrading the dev machine to interop 12, or upgrading production to 14. As long as they remain out of sync then there is a potential for problems.
|
|
|
|
|
And here is why you NEVER USE EXCEL (or any office product) in a production environment. You are going to have to sort out your versioning of the office product (rinse and repeat every time the is a new office version) so all systems are using the same version. OR use a more robust transport method/tool.
Never underestimate the power of human stupidity -
RAH
I'm old. I know stuff - JSOP
|
|
|
|
|
I am trying to install a VB Net project with a SQL database on another computer and it does not find the connection. The setup loads all the components. The program works on my computer with no problems. What do I need to do to load the database to other computers?
Connecting string
Dim connectionString As String = "Data Source=(LocalDB)\MSSQLLocalDB;Initial Catalog=C:\USERS\Name\ONEDRIVE\DOCUMENTS\MyDataBaseName.MDF;Integrated Security=True"
|
|
|
|
|
|
What is the error message on the Client's machine ?
|
|
|
|
|
I feel like I painted myself into a corner here. But hopeful Linq was well designed to pull this off. I don't even know what to call it to look it up on the internet.
So I have a List of Items sold for the year, and each item has a cost, price, qty, invoice number, shipdate.
My objective is to group those items by invoice number. But as I group, I need to do the following ...
Say invoice 1000 has 5 items for example.
For each item assigned to invoice 1000,
I need ....
FExtCost = (FSHIPQTY * FCOST)<br />
fExtPrice = (FSHIPQTY * FPRICE)
What I have so far is the remnants of my design mistake, when I realized my numbers were wrong after fact checking the report. Like I just need the first instance of the inv number, first instance of a item description, but to total the cost, price so I can calculate the margin of the invoice.
Yes I'm asking for help writing code. Or a link to some examples I can study.
Dim gInvoicesAll As List(Of MarginCustomerInvoicesR1) = cTpAll.OrderBy(Function(ob) ob.FINVNO).GroupBy(Function(inv) inv.FINVNO).Select(Function(cl) New MarginCustomerInvoicesR1() With {
.FCustNo = cl.First().FCUSTNO,
.FInvNo = cl.First().FINVNO,
.FShipDate = cl.First().FSHIPDATE,
.FDescript = cl.First().FDESCRIPT,
.FAmount = cl.Sum(Function(priceTotal) priceTotal.FAMOUNT),
.FExtCost = 0,
.FExtPrice = 0,
.FExtMargin = 0,
.FPercent = 0
}).ToList()
Along time ago, Richard gave me a sophistacated solution for price, so I tried to build on it
.FPriceMin = cl.OrderByDescending(Function(i) If(i.FPrice = 0, 0, 1)).ThenByDescending(Function(i) i.FShipDate).First().FPrice,
If it ain't broke don't fix it
Discover my world at jkirkerx.com
modified 3-Apr-20 14:21pm.
|
|
|
|
|
Perhaps the answer is to just perform the calc after the grouping.
Group the Invoices, then loop them, and do another linq to go back to the original and grab the items.
'Use Linq to Group the Invoices Together
Dim gInvoicesAll As List(Of MarginCustomerInvoicesR1) = cTpAll.OrderBy(Function(ob) ob.FINVNO).GroupBy(Function(inv) inv.FINVNO).Select(Function(cl) New MarginCustomerInvoicesR1() With {
.FCustNo = cl.First().FCUSTNO,
.FInvNo = cl.First().FINVNO,
.FShipDate = cl.First().FSHIPDATE,
.FDescript = cl.First().FDESCRIPT,
.FAmount = cl.Sum(Function(sum) sum.FAMOUNT),
.FExtCost = 0,
.FExtPrice = 0,
.FExtMargin = 0,
.FPercent = 0
}).ToList()
Dim rsDataRow As DataRow
For Each pI As MarginCustomerInvoicesR1 In gInvoicesAll
'Instead of using Linq, I'll just iterate the original data before grouping
Dim items = cTpAll.Where(Function(item) item.FINVNO.Equals(pI.FInvNo)).ToList()
For Each item In items
pI.FExtCost += If(item.FCOST <> 0 And item.FSHIPQTY <> 0, item.FCOST * item.FSHIPQTY, 0)
pI.FExtPrice += If(item.FPRICE <> 0 And item.FSHIPQTY <> 0, item.FPRICE * item.FSHIPQTY, 0)
pI.FExtMargin += pI.FExtPrice - pI.FExtCost
Next
If it ain't broke don't fix it
Discover my world at jkirkerx.com
|
|
|
|
|
How about:
.Select(Function(cl) New MarginCustomerInvoicesR1() With {
.FCustNo = cl.First().FCUSTNO,
.FInvNo = cl.First().FINVNO,
.FShipDate = cl.First().FSHIPDATE,
.FDescript = cl.First().FDESCRIPT,
.FAmount = cl.Sum(Function(item) item.FAMOUNT),
.FExtCost = cl.Sum(Function(item) item.FCOST * item.FSHIPQTY),
.FExtPrice = cl.Sum(Function(item) item.FPRICE * item.FSHIPQTY),
.FExtMargin = cl.Sum(Function(item) (item.FPRICE - item.FCOST) * item.FSHIPQTY),
.FPercent = 0
}).ToList()
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
So use this within my invoice loop?
Hmm ...
If it ain't broke don't fix it
Discover my world at jkirkerx.com
|
|
|
|
|
No, in the outer query:
Dim gInvoicesAll As List(Of MarginCustomerInvoicesR1) = cTpAll.OrderBy(Function(ob) ob.FINVNO).GroupBy(Function(inv) inv.FINVNO).Select(Function(cl) New MarginCustomerInvoicesR1() With {
.FCustNo = cl.First().FCUSTNO,
.FInvNo = cl.First().FINVNO,
.FShipDate = cl.First().FSHIPDATE,
.FDescript = cl.First().FDESCRIPT,
.FAmount = cl.Sum(Function(item) item.FAMOUNT),
.FExtCost = cl.Sum(Function(item) item.FCOST * item.FSHIPQTY),
.FExtPrice = cl.Sum(Function(item) item.FPRICE * item.FSHIPQTY),
.FExtMargin = cl.Sum(Function(item) (item.FPRICE - item.FCOST) * item.FSHIPQTY),
.FPercent = 0
}).ToList()
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Interesting, your code is spot on, like your are working along side with me on this.
So your saying technically, this will produce the result I'm looking for?
Or is it just in theory it should?
hmm...
I ended up with this. I wanted to take special care of negative numbers, and avoid divide by zero errors as well. So if an item was given away for free at $0 price, I would deduct that from the margin total.
Dim gInvoicesAll As List(Of MarginCustomerInvoicesR1) = cTpAll.OrderBy(Function(ob) ob.FINVNO).GroupBy(Function(inv) inv.FINVNO).Select(Function(cl) New MarginCustomerInvoicesR1() With {
.FCustNo = cl.First().FCUSTNO,
.FInvNo = cl.First().FINVNO,
.FShipDate = cl.First().FSHIPDATE,
.FDescript = cl.First().FDESCRIPT,
.FAmount = cl.Sum(Function(sum) sum.FAMOUNT),
.FExtCost = 0,
.FExtPrice = 0,
.FExtMargin = 0,
.FPercent = 0
}).ToList()
Dim rsDataRow As DataRow
For Each pI As MarginCustomerInvoicesR1 In gInvoicesAll
Dim items = cTpAll.Where(Function(item) item.FINVNO.Equals(pI.FInvNo)).ToList()
For Each item In items
pI.FExtCost += If(item.FCOST <> 0 And item.FSHIPQTY <> 0, item.FCOST * item.FSHIPQTY, 0)
pI.FExtPrice += If(item.FPRICE <> 0 And item.FSHIPQTY <> 0, item.FPRICE * item.FSHIPQTY, 0)
Select Case True
Case item.FPRICE.Equals(0)
pI.FExtMargin -= If(item.FCOST <> 0 And item.FSHIPQTY <> 0, item.FCOST * item.FSHIPQTY, 0)
Case Else
pI.FExtMargin += pI.FExtPrice - pI.FExtCost
End Select
Next
Dim fPercent = If(pI.FExtCost <> 0 And pI.FExtPrice <> 0, Math.Round((pI.FExtPrice - pI.FExtCost) / pI.FExtCost, 4), 0)
If it ain't broke don't fix it
Discover my world at jkirkerx.com
|
|
|
|
|
jkirkerx wrote:
If(item.FCOST <> 0 And item.FSHIPQTY <> 0, item.FCOST * item.FSHIPQTY, 0)
If FCOST or FSHIPQTY is equal to 0, then FCOST * FSHIPQTY will return 0. There's no need for the conditional here, or any of the similar conditionals.
jkirkerx wrote:
Select Case True
Case item.FPRICE.Equals(0)
pI.FExtMargin -= item.FCOST * item.FSHIPQTY
Case Else
pI.FExtMargin += (item.FCOST - item.FPRICE) * item.FSHIPQTY
End Select
If FPRICE is equal to 0, then FCOST * FPRICE is equal to FCOST. Both cases are trying to perform the same calculation, so the Select Case is not required.
I say "trying to", because you're actually inflating the margin. For each item in the invoice which has a price, you're adding the cumulative difference between the price and the cost, rather than the difference between the price and the cost of that item.
Eg:
Items:
------
1: Price = 10; Cost = 5; Qty = 1
2: Price = 10; Cost = 5; Qty = 1
3: Price = 10; Cost = 5; Qty = 1
Your calculation:
-----------------
1: FExtPrice = 10; FExtCost = 5; FExtMargin = 0 + 10 - 5 = 5
2: FExtPrice = 20; FExtCost = 10; FExtMargin = 5 + 20 - 10 = 15
3: FExtPrice = 30; FExtCost = 15; FExtMargin = 15 + 30 - 15 = 30
Total margin: 30 = 100% !
Correct calculation:
--------------------
1: FExtPrice = 10; FExtCost = 5; FExtMargin = 0 + 10 - 5 = 5
2: FExtPrice = 20; FExtCost = 10; FExtMargin = 5 + 10 - 5 = 10
3: FExtPrice = 30; FExtCost = 15; FExtMargin = 10 + 10 - 5 = 15
Total margin: 15 = 50%
This code should produce the correct results in a single statement, without needing to manually loop over the items:
Dim gInvoicesAll As List(Of MarginCustomerInvoicesR1) = cTpAll.OrderBy(Function(ob) ob.FINVNO).GroupBy(Function(inv) inv.FINVNO).Select(Function(cl) New MarginCustomerInvoicesR1() With {
.FCustNo = cl.First().FCUSTNO,
.FInvNo = cl.First().FINVNO,
.FShipDate = cl.First().FSHIPDATE,
.FDescript = cl.First().FDESCRIPT,
.FAmount = cl.Sum(Function(item) item.FAMOUNT),
.FExtCost = cl.Sum(Function(item) item.FCOST * item.FSHIPQTY),
.FExtPrice = cl.Sum(Function(item) item.FPRICE * item.FSHIPQTY),
.FExtMargin = cl.Sum(Function(item) (item.FPRICE - item.FCOST) * item.FSHIPQTY),
.FPercent = If(cl.Sum(Function(item) item.FCOST * item.FSHIPQTY) = 0, 0, Math.Round(cl.Sum(Function(item) (item.FPRICE - item.FCOST) * item.FSHIPQTY) / cl.Sum(Function(item) item.FCOST * item.FSHIPQTY), 4))
}).ToList() If it doesn't, then give me an example where it goes wrong.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Your case is solid here.
I'll do a case study here with this new info and facts, and fact check both methods and see what the totals are.
I really didn't think that group statement would iterate the items on the invoice and group it.
Let me see what happens ... Give me a day.
If it ain't broke don't fix it
Discover my world at jkirkerx.com
|
|
|
|
|
I went ahead and tested that code and it works good. I did the fact or math test and the numbers are spot on now. Don't know why I doubted the first revision of my code, which was close to what you worked on. Guess my math was off and I blamed it on the group function.
Thanks for sticking with it, and proving that your suggestion did the same thing, while being smaller and more compact.
If it ain't broke don't fix it
Discover my world at jkirkerx.com
|
|
|
|
|
Hi Team,
I have an Excel file that has data and a table in SQL server.
I am doing it by excel VBA code in "Audit Query Report" Consolidation.
Hence, I want to do a Consolidated report by all Users in the SQL Table. Each User data to Update\Insert in DB once click the macro button in excel, Even I existing data to replaced, New Data should add in DB.
I googled it, unfortunately, there is no related code as my per requirement, Kindly advise best way code to make a consolidation DB, am finance Background, am not much familiar SQL code through VBA.
|
|
|
|
|
Member 14784416 wrote: there is no related code as my per requirement,
Actually, there are probably millions of related codes out there on the internet. But you can't search for that whole task. You have to break it into pieces and look for code examples of the pieces.
But it's hard to understand exactly what you are asking. It sounds like you just need to be able to save data from excel into sql using vba. Here is one example, sql server - Export Excel to SQL using VBA - Stack Overflow[^]
Member 14784416 wrote: am not much familiar SQL code through VBA.
If you do not know how to code then you need to hire someone. There is likely no complete solution that you can download based on your requirements, it will have to be built.
Social Media - A platform that makes it easier for the crazies to find each other.
Everyone is born right handed. Only the strongest overcome it.
Fight for left-handed rights and hand equality.
|
|
|
|
|
Thanks for your reply, ok i will do my own part, if any error,let you know 
|
|
|
|
|
I have a richtextbox named
input RTB that I want to associate
its event input RTB.ChangedText to a handler programmatically.
and then it want to use
addhandller and removehandller
to enable the event and to disable it.
can someone help me to achieve
this.
Thank you very much.
|
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
