I have to read data from an Excel Sheet. Using Microsoft Office Interop and
Automation I create an Excel object
Dim xl As New Excel.Applicati on, wkbk As Excel.Workbook, rng as Excel.Range
I open a workbook and then read the data from a worksheet using the Sheet's
UsedRange property into an Excel Range object I call rng. Then I loop
through all the cells in this rng object. This works fine except if there is
no data in a particular cell.
Try
....
For i As Integer = 1 to rng.Rows.Count
For j As Integer= 1 to rng.Columns.Cou nt
str1 = Ctype(rng(i, j), Excel.Range).Va lue.ToString
Next
Next
....
Catch ex1 As Exception
....
End Try
The loop aboves works fine if there is data in a given cell/range. But
bombs if there is no data (crashes the app) even with the Try/Catch. So I
added another Try/Catch inside the loop
For i As Integer = 1 to rng.Rows.Count
For j As Integer= 1 to rng.Columns.Cou nt
Try
str1 = Ctype(rng(i, j), Excel.Range).Va lue.ToString
...
Catch ex As Except
str1 = "**"
...
End Try
Next
Next
In the system error trap (after the app crashed without the inner
try/catch) the message said the value which crashed the app was 'undefined'.
I tried
If ctype(rng(i, j), Excel.Range).va lue Is Nothing Then ...
but this did not keep the app from crashing. Is there a way to trap for
'Undefined' besides Try/Catch or is Try/Catch the desired method?
Thanks,
Rich
4  4336 
I forgot to mention that the Inner Try/Catch did keep the app from crashing.
So the inner Try/Catch did work! My question is if there is a way to trap
for the 'Undefined' value instead of having to use an Inner Try/Catch block.
"Rich" wrote:
I have to read data from an Excel Sheet. Using Microsoft Office Interop and
Automation I create an Excel object
Dim xl As New Excel.Applicati on, wkbk As Excel.Workbook, rng as Excel.Range
I open a workbook and then read the data from a worksheet using the Sheet's
UsedRange property into an Excel Range object I call rng. Then I loop
through all the cells in this rng object. This works fine except if there is
no data in a particular cell.
Try
...
For i As Integer = 1 to rng.Rows.Count
For j As Integer= 1 to rng.Columns.Cou nt
str1 = Ctype(rng(i, j), Excel.Range).Va lue.ToString
Next
Next
...
Catch ex1 As Exception
...
End Try
The loop aboves works fine if there is data in a given cell/range. But
bombs if there is no data (crashes the app) even with the Try/Catch. So I
added another Try/Catch inside the loop
For i As Integer = 1 to rng.Rows.Count
For j As Integer= 1 to rng.Columns.Cou nt
Try
str1 = Ctype(rng(i, j), Excel.Range).Va lue.ToString
...
Catch ex As Except
str1 = "**"
...
End Try
Next
Next
In the system error trap (after the app crashed without the inner
try/catch) the message said the value which crashed the app was 'undefined'.
I tried
If ctype(rng(i, j), Excel.Range).va lue Is Nothing Then ...
but this did not keep the app from crashing. Is there a way to trap for
'Undefined' besides Try/Catch or is Try/Catch the desired method?
Thanks,
Rich
I think you have to use the inner try catch. I may be a bit of a newbie,
but otherwise if you do not use the "inside try catch" by my opinion, your
for next loops will end instantly.
Using the inside for next loops, traps the 1 cell that has errors but allows
the other cells to continue to import.
Miro
"Rich" <Ri**@discussio ns.microsoft.co mwrote in message
news:3E******** *************** ***********@mic rosoft.com...
>I forgot to mention that the Inner Try/Catch did keep the app from
crashing.
So the inner Try/Catch did work! My question is if there is a way to
trap
for the 'Undefined' value instead of having to use an Inner Try/Catch
block.
"Rich" wrote:
>I have to read data from an Excel Sheet. Using Microsoft Office Interop
and
Automation I create an Excel object
Dim xl As New Excel.Applicati on, wkbk As Excel.Workbook, rng as
Excel.Range
I open a workbook and then read the data from a worksheet using the
Sheet's
UsedRange property into an Excel Range object I call rng. Then I loop
through all the cells in this rng object. This works fine except if
there is
no data in a particular cell.
Try
...
For i As Integer = 1 to rng.Rows.Count
For j As Integer= 1 to rng.Columns.Cou nt
str1 = Ctype(rng(i, j), Excel.Range).Va lue.ToString
Next
Next
...
Catch ex1 As Exception
...
End Try
The loop aboves works fine if there is data in a given cell/range. But
bombs if there is no data (crashes the app) even with the Try/Catch. So
I
added another Try/Catch inside the loop
For i As Integer = 1 to rng.Rows.Count
For j As Integer= 1 to rng.Columns.Cou nt
Try
str1 = Ctype(rng(i, j), Excel.Range).Va lue.ToString
...
Catch ex As Except
str1 = "**"
...
End Try
Next
Next
In the system error trap (after the app crashed without the inner
try/catch) the message said the value which crashed the app was
'undefined'.
I tried
If ctype(rng(i, j), Excel.Range).va lue Is Nothing Then ...
but this did not keep the app from crashing. Is there a way to trap for
'Undefined' besides Try/Catch or is Try/Catch the desired method?
Thanks,
Rich
Rich wrote:
I open a workbook and then read the data from a worksheet using the Sheet's
UsedRange property into an Excel Range object I call rng. Then I loop
through all the cells in this rng object. This works fine except if there is
no data in a particular cell.
For i As Integer = 1 to rng.Rows.Count
For j As Integer= 1 to rng.Columns.Cou nt
str1 = Ctype(rng(i, j), Excel.Range).Va lue.ToString
What other properties/methods does the Range object support?
Is there one that would indicate the lack of a value?
From the /little/ I remember of Excel's VBA, doesn't .FormulaR1C1 come
back as an empty string?
HTH,
Phill W.
Not completely sure about .FormulaR1C1
I will have to try it out in Excel and see what I get. Might be an idea.
But you gave me another idea - this may be more an Excel Question than a
vb.net question. Maybe an Excel person has some suggestions. I will have to
try that NG.
Thanks all for your replies.
"Phill W." wrote:
Rich wrote:
I open a workbook and then read the data from a worksheet using the Sheet's
UsedRange property into an Excel Range object I call rng. Then I loop
through all the cells in this rng object. This works fine except if there is
no data in a particular cell.
For i As Integer = 1 to rng.Rows.Count
For j As Integer= 1 to rng.Columns.Cou nt
str1 = Ctype(rng(i, j), Excel.Range).Va lue.ToString
What other properties/methods does the Range object support?
Is there one that would indicate the lack of a value?
From the /little/ I remember of Excel's VBA, doesn't .FormulaR1C1 come
back as an empty string?
HTH,
Phill W.
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: Erik Cruz |
last post by:
Hi.
I have read several articles recommending avoid to raise exceptions when
possible, since exceptions are expensive to the system. Removing code from
Try... Catch blocks can help performance? The following 2 first lines of
code - I think - can't raise exceptions:
Try
Dim str As String
Dim intVar As Integer
|
by: Joona I Palaste |
last post by:
We all know that this:
void *p;
if (p=malloc(1)) {
free(p);
p;
}
causes undefined behaviour if malloc() succeeds. But what about this?
|
by: Mantorok Redgormor |
last post by:
#include <stdio.h>
struct foo { int example; struct bar *ptr; };
int main(void)
{
struct foo baz;
baz.ptr = NULL; /* Undefined behavior? */
return 0;
|
by: Nitin Bhardwaj |
last post by:
Well, i'm a relatively new into C( strictly speaking : well i'm a
student and have been doing & studying C programming for the last 4
years).....and also a regular reader of "comp.lang.c"
I don't have a copy of ANSI C89 standard,therefore i had to post this
question:
What is the difference between "unspecified" behaviour & "undefined"
behaviour of some C Code ??
|
by: Ken Turkowski |
last post by:
The construct
(void*)(((long)ptr + 3) & ~3)
worked well until now to enforce alignment of the pointer to long
boundaries. However, now VC++ warns about it, undoubtedly to help things
work on 64 bit machines, i.e. with 64 bit pointers.
In the early days of C, where there were problems with the size of int
being 16 or 32 bits, the response was that an int was guaranteed to hold
a pointer (yes, there were 64Kb address spaces at one time!)....
| | |
by: Keith Kowalski |
last post by:
I anm opening up a text file reading the lines of the file that refer to a
tif image in that file, If the tif image does not exist I need it to send an
email stating that the file doesn't exist then skip this file and move onto
the next file (line).
If file is there then move to a sirectory.
Here is the code I have (Feel free to make corrections as needed. If
possible make changes in red)
|
by: p_cricket_guy |
last post by:
Please see the code below
-- start listing is_it_ub.c --
#include <stdio.h>
#include <stdlib.h>
int main (void)
{
unsigned char buff;
|
by: thamizh.veriyan |
last post by:
Hi,
I am new to this community. I have a doubt regarding trap
representations.
I read in IBM's website that something like this is legal:
int main(){
int x=3;
{
|
by: coolguyaroundyou |
last post by:
Will the following statement invoke undefined behavior :
a^=b,b^=a,a^=b ;
given that a and b are of int-type ??
Be cautious, I have not written a^=b^=a^=b ; which, of course, is
undefined. I am having some confusion with the former statement!
Also, state the reason for the statement being undefined!
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| | |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
| | |
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
