|
See my reply to your repost of this message in the Java forum.
|
|
|
|
|
I am writing a code in VBA that opens up all files in a document and copies and pastes the information from each document. User save to raw date report in path without open it, ( No one edit or modifiy Financial Number in Raw Date Download file) So I want to check each Workbook Date Created Time vs Date Modified Time both should match, then allow Copy and paste else show the Message box to user, File has been Modified, kindly revisit and download raw date" Here we can allow 20 minutes buffer time for downloading Report ie 24-Apr-2020 2.30 P.M vs 24-Apr-2020 2.50 P.M time will allow.
My code is below and any help or suggestion would be much appreciated. Thanks
Dim Ref As Object, CheckRefEnabled%
CheckRefEnabled = 0
With ThisWorkbook
For Each Ref In .VBProject.References
If Ref.Name = "Scripting" Then
CheckRefEnabled = 1
Exit For
End If
Next Ref
If CheckRefEnabled = 0 Then
.VBProject.References.AddFromGUID "{420B2830-E718-11CF-893D-00A0C9054228}", 1, 0
End If
End With
Dim objFSO As Scripting.FileSystemObject
Dim myFolder As Scripting.Folder
Dim myFile As Scripting.File
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set myFolder = objFSO.GetFolder("C:\ME Financial Report\")
For Each myFile In myFolder.Files
DCreated = myFile.DateCreated
DCreated = Strings.Format(myFile.DateCreated, "DD-MM-YY,HH:MM")
DModified = myFile.DateLastModified
DModified = Strings.Format(myFile.DateLastModified, "DD-MM-YY, HH:MM")
DModifiedAdd = DateAdd("n", 20, DModified)
DModifiedAdd = Strings.Format(DModifiedAdd, "DD-MM-YY, HH:MM")
Debug.Print DCreated, DModified, DModifiedAdd
If DCreated >= DModifiedAdd Then
MsgBox "File has been Modified, kindly revisit and download raw data - " & myFile, vbCritical
Exit Sub
Else
End If
Next myFile
|
|
|
|
|
|
Hi,
I'm starting to learn coding with vb .net and I'm having and issue while moving through registers using bindingnavigator. Let me explain:
I have a function (already tested and works fine) that receives an idNumber from a textbox and returns a file path.
If I call that function using
Private Sub BindingNavigatorMoveNextItem_Click(sender As Object, e As EventArgs) Handles BindingNavigatorMoveNextItem.Click
changeUser(usuarioTextBox.Text)
End Sub
it calls the function with the textbox data before moving to the next register, and that's not what I would like to do.
Any clues to solve this? Maybe I'm using and incorrect event?
Thanks in advance
|
|
|
|
|
What do you mean by "register"? It's not a common term in the context of VB. And the "navigator" is typically used to move position in a "set of records". Moving between "text boxes" is usually via the Tab key. You want to do something with text in a Text Box? It's usually done in response to a "button click"; e.g. to query or save something.
It was only in wine that he laid down no limit for himself, but he did not allow himself to be confused by it.
― Confucian Analects: Rules of Confucius about his food
|
|
|
|
|
Hi,
Sorry for not using the right terms, I'm not familiar with them and got confused translating.
I'm using the navigator to move between records. What I was trying is to change a PictureBox Image every time the user moves using the navigator buttons. The path of the image is stored in db and contained in a textbox.
The problem I have is that using MoveNext event, I get the value of the textbox prior to move; so I don't get the right image path.
Thanks
|
|
|
|
|
Be aware that the movenext event MAY fire twice once on leaving a record and once on entering a record (I have not tested this but found it with other events). You may need to test the content of the object passed to the event.
Never underestimate the power of human stupidity -
RAH
I'm old. I know stuff - JSOP
|
|
|
|
|
I have service that is non-interactive that set values for these reg values, so that a computer can auto login:
AutoAdminLogon (1)
DefaultDomainName
DefaultUserName
Default Password
ForceAutoLogon (1)
This account automatically displays a form. When you click on a button, it will change the AutoAdminLogon and ForceAutoLogon reg values from a 1 to a 0, so a user can log in. After a minute if no one logs on, the reg values changes back from a 0 to a 1, so that it can automatically log back on, but it's not happening. I have a SCCM agent on the computer. If I push a script to run logoff.exe or a tsdiscon.exe, the autologin account will log back in. If I run the same commands from the service at the Ctrl + Alt + Delete screen, nothing happens. I do understand that because the service is non-interactive it won't work. Any suggestions on how to force the autologin account to log back on at the Ctrl + Alt + Delete screen? Thanks.
|
|
|
|
|
|
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
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
