[RESOLVED] conversion from string " " to type 'Double' is not valid
I don't even use Double datatype in my code, even though I assume it come from CDec function.
I set a condition to make the value in it to 0 when it is null (code not included), it still get error
Here is my code.
Code:
Dim di = New DirectoryInfo("E:\ขาย2564\R-HS2564\R-HS64-05")
'I Dim all variable As string, except ef As Boolean
result = "E:\ภาษีขาย64\รายงานภาษีขายอัตโนมัติ.xlsx"
ExcelApp2 = CreateObject("Excel.Application")
ExcelWorkbook2 = ExcelApp2.Workbooks.Open("C:\Users\ACER\Desktop\ต้นแบบ\แบบฟอร์มรายงานภาษีขาย.xlsx")
For Each fi In di.EnumerateFileSystemInfos()
If (fi.Attributes & FileAttributes.Directory) = FileAttributes.Directory Then
Console.WriteLine(fi.FullName + " is a directory")
Else
pathfile = fi.FullName
checkfile = Dir$(pathfile)
ExcelApp = CreateObject("Excel.Application")
ExcelWorkbook = ExcelApp.Workbooks.Open(pathfile)
'MessageBox.Show(fi.FullName + " is a file")
'check vat not equal to real price
If ExcelApp.Worksheets("R-HS").Range("K31").Value = ExcelApp.Worksheets("SO").Range("E26").Value - ExcelApp.Worksheets("SO").Range("E27").Value Then 'yes vat equal no prob
Else 'vat not equal we got a prob
ExcelApp.Worksheets("R-HS").Range("K30").Value = CDec(ExcelApp.Worksheets("R-HS").Range("K30").Value) + CDec(ExcelApp.Worksheets("SO").Range("E26").Value) - CDec(ExcelApp.Worksheets("SO").Range("E27").Value) - CDec(ExcelApp.Worksheets("R-HS").Range("K31").Value)
End If
For n As Integer = 2 To 4 'ExcelApp2.Worksheets.Count 'page 'find free page and position
'if consider for page 2 or more
tp = "หน้า" & n.ToString
For r As Integer = 9 To 55 'only 45 list per page
If ExcelApp2.Worksheets(tp).Range("A" & r).Value Is Nothing Then
freepage = n.ToString
freeline = r.ToString
ef = True 'find target line set ef for loop exit
Exit For
End If
Next
If ef = True Then
Exit For
End If
Next
If freepage = 2 Then
ExcelApp2.Worksheets("หน้า" & freepage).Range("A" & freeline).Value = freeline - 8
ElseIf freepage = 3 Then 'page 3
ExcelApp2.Worksheets("หน้า" & freepage).Range("A" & freeline).Value = freeline - 8 + 44
ElseIf freepage = 4 Then 'page 4
ExcelApp2.Worksheets("หน้า" & freepage).Range("A" & freeline).Value = freeline - 8 + 88
End If
ExcelApp2.Worksheets("หน้า" & freepage).Range("B" & freeline).Value = ExcelApp.Worksheets("SO").Range("E6").Value
ExcelApp2.Worksheets("หน้า" & freepage).Range("C" & freeline).Value = Split(fi.Name, ".")(0)
ExcelApp2.Worksheets("หน้า" & freepage).Range("D" & freeline).Value = ExcelApp.Worksheets("SO").Range("B7").Value
If ExcelApp.Worksheets("SO").Range("D8").Value IsNot Nothing Then
ExcelApp2.Worksheets("หน้า" & freepage).Range("E" & freeline).Value = ExcelApp.Worksheets("SO").Range("D8").Value
Else : ExcelApp2.Worksheets("หน้า" & freepage).Range("E" & freeline).Value = "-"
End If
ExcelApp2.Worksheets("หน้า" & freepage).Range("F" & freeline).Value = "สำนักงานใหญ่"
ExcelApp2.Worksheets("หน้า" & freepage).Range("G" & freeline).Value = "-"
ExcelApp2.Worksheets("หน้า" & freepage).Range("H" & freeline).Value = ExcelApp.Worksheets("R-HS").Range("K29").Value
ExcelApp2.DisplayAlerts = False
ExcelApp2.Worksheets("หน้า1").SaveAs(filename:=result)
ExcelApp2.DisplayAlerts = True
ExcelApp.DisplayAlerts = False
ExcelApp.Quit()
ExcelApp.DisplayAlerts = True
End If
Next
MessageBox.Show("d o n e")
I try to find the root of problem all day, I think I am dead end now.
Thank you
Re: conversion from string " " to type 'Double' is not valid
We shouldn't have to trawl through your code, hoping to find a location that could throw that exception. The exception itself tells you exactly where it was thrown. At the very least you should be providing us with that information but you should be using it to debug your code yourself. Set a breakpoint before where the exception is thrown, step through the code and see what data is in use when the exception is thrown and, if necessary, debug further to see where it came from. There's a lot that you can and should be doing before posting here. Even if you still can't solve the problem yourself, you'll have a lot more relevant information to provide to us.
Re: conversion from string " " to type 'Double' is not valid
Quote:
Originally Posted by
fubao
I don't even use Double datatype in my code, even though I assume it come from CDec function.
I set a condition to make the value in it to 0 when it is null (code not included), it still get error
If you are calling CDec than you are using doubles, so presumably somewhere you are reading what looks like an empty string, or a single space and trying to convert it to a double - that is what is failing.
Checking for null and setting it to 0 would work if you are actually getting a null, if it is anything other than a null or a double you will still get an error.
My advice would be to look at using something like Double.TryParse or even String.IsNullOrWhiteSpace to check your values and act accordingly.
Re: conversion from string " " to type 'Double' is not valid
Uh, isn't CDec for the Decimal data type? CDbl is for the Double data type.
Re: conversion from string " " to type 'Double' is not valid
Quote:
Originally Posted by
Peter Swinkels
Uh, isn't CDec for the Decimal data type? CDbl is for the Double data type.
Good point, it has been so long since I used any of the Cxxx functions I completely forgot which was which...
In that case I would probably also suggest putting Option Strict On as well to catch these implicit conversions.
Re: conversion from string " " to type 'Double' is not valid
here you have something :
Quote:
If ExcelApp2.Worksheets(tp).Range("A" & r).Value Is Nothing Then
you should have r.tostring
to know where is the error would help a lot...
Re: conversion from string " " to type 'Double' is not valid
I just noticed the string concatenation operator ("&") is being used to build strings.
Can be changed to:
This is done using string interpolation. This goes for just about any other use of the concatenation operator (EDIT: or ("+")) as well.
Re: conversion from string " " to type 'Double' is not valid
Quote:
Originally Posted by
Delaney
you should have r.tostring
Actually, the & operator is defined for types String and Integer. The issue arises if you use + to concatenate strings rather than &, because adding two types that have different definitions of addition is fraught. There's only one definition of concatenation so there's no ambiguity.
Re: conversion from string " " to type 'Double' is not valid
Re: conversion from string " " to type 'Double' is not valid
Sorry for late reply, I was struggling with another problem for couple of days.(excel.quit still got exce.exe, and some loop problem)
But they are solved now.
Quote:
Originally Posted by
PlausiblyDamp
If you are calling CDec than you are using doubles, so presumably somewhere you are reading what looks like an empty string, or a single space and trying to convert it to a double - that is what is failing.
Checking for null and setting it to 0 would work if you are actually getting a null, if it is anything other than a null or a double you will still get an error.
My advice would be to look at using something like Double.TryParse or even String.IsNullOrWhiteSpace to check your values and act accordingly.
Absolutely, sir. This is exactly where the problem is. It is a single space, I didn't even think about that, All I thought is Null. I kept finding Null and Double
Likewise, This give me a headache, At first, I pay attention in Double which doesn't exist, Until you tell
me sir, CDec does give Double when option strict is off.
Thank you for your help, pointing directly into the root of prob.
Re: conversion from string " " to type 'Double' is not valid
Quote:
Originally Posted by
Delaney
here you have something :
you should have r.tostring
to know where is the error would help a lot...
Quote:
Originally Posted by
Peter Swinkels
I just noticed the string concatenation operator ("&") is being used to build strings.
Can be changed to:
This is done using string interpolation. This goes for just about any other use of the concatenation operator (EDIT: or ("+")) as well.
There is something here also, it seldom seem going wrong, but I don't really know what exactly it is now. But I use bad code but working for temporary solution now.
I am considering your both advice in advance.
Thank you, sir , Delaney, Peter Swinkels.
Re: conversion from string " " to type 'Double' is not valid
Quote:
Originally Posted by
jmcilhinney
We shouldn't have to trawl through your code, hoping to find a location that could throw that exception. The exception itself tells you exactly where it was thrown. At the very least you should be providing us with that information but you should be using it to debug your code yourself. Set a breakpoint before where the exception is thrown, step through the code and see what data is in use when the exception is thrown and, if necessary, debug further to see where it came from. There's a lot that you can and should be doing before posting here. Even if you still can't solve the problem yourself, you'll have a lot more relevant information to provide to us.
ีuhmm. Normally, If debug it in coding PC, it throws exception directly to where problem occurs. But this case, I test on another PC(which really use program but no IDE). So it just throw error but don't tell where.
Should I use "windbg" or something similar ?
By the way, you reply me a couple thread. I do remember to add what you tell me to do before posting to my to do list. So I may post again, if there is new stuff I should do, you can always tell me.
By another way, yesterday I solve several big problems(to me) without posting threat ! haha.
Re: conversion from string " " to type 'Double' is not valid
Quote:
Originally Posted by
fubao
ีuhmm. Normally, If debug it in coding PC, it throws exception directly to where problem occurs. But this case, I test on another PC(which really use program but no IDE). So it just throw error but don't tell where.
Should I use "windbg" or something similar ?
You should log the exceptions thrown by your application. VB.NET applications have an UnhandledException event that lets you log the exception and close the app cleanly instead of crashing. You then have access to all the information the exception provides. If you deploy the PDB file(s) with your EXE, I believe that you can get the line number for the exception, even for a Release build.
Re: conversion from string " " to type 'Double' is not valid
OK, I remember it and will do that next times.
Thank you, Sir.
