Visual Basic

data connection to ms excel

12-Jan-05 3:54

This question carries the stench of homework.

2D is the hexadecimal value for 45, not 45.36. Getting the hex value of any number is easy, just use the Hex() function. Note: You'll get the Hexadecimal value of the integer version of the number, not the whole floating point value.

Dim hexString As String
hexString = Hex(45.36)

RageInTheMachine9532
"...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome

abdulla hamid11-Jan-05 18:16

abdulla hamid

11-Jan-05 18:16

hi yall
i am a student and am finding it difficult to connect to ms excel. pls assist

Re: data connection to ms excel

Ritesh123411-Jan-05 21:15

Ritesh1234

11-Jan-05 21:15

Hi ,

Dim DS As System.Data.DataSet
Dim MyCommand As System.Data.OleDb.OleDbDataAdapter
Dim MyConnection As System.Data.OleDb.OleDbConnection

MyConnection = New System.Data.OleDb.OleDbConnection( _
"provider=Microsoft.Jet.OLEDB.4.0; " & _
"data source=C:\myData.XLS; " & _
"Extended Properties=Excel 8.0;")
' Select the data from Sheet1 of the workbook.
MyCommand = New System.Data.OleDb.OleDbDataAdapter( _
"select * from [Sheet1Name]", MyConnection)

DS = New System.Data.DataSet()
MyCommand.Fill(DS)
MyConnection.Close()

Hope This code will help u............:->

Reagrds,
Ritesh

Re: data connection to ms excel

Britnt712-Jan-05 9:14

Britnt7

12-Jan-05 9:14

Is this possible?

copy coversheet1 from test.xls in c:\test directory

paste or create coversheet1 to test2.xls in c:\test2 directory

both excel documents exist but I need to transfer a sheet from one document to the other.

I am using visual basic 6.

Any help would be greatly appreciated.

Thanks

Beginner in VB.Net

Re: ReadFile not working in VB.NET

Dave Kreskowiak11-Jan-05 15:49

Re: ReadFile not working in VB.NET

11-Jan-05 15:49

Roman Nurik wrote:
I am befuddled as to why API functions (I had similar problems calling ZLib DLL functions from VB.NET) don't work properly in VB.NET.

They work perfectly in VB.NET when done properly.

Without seeing more of your code, we really can't determine what's wrong. I've used the API functions myself about a year ago and never had a problem like what your having.

RageInTheMachine9532
"...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome

Roman Nurik11-Jan-05 18:17

Roman Nurik

11-Jan-05 18:17

hey, i got it to work.. the problem was with my api declaration... the last parameter, i was declaring ByRef lpOverlapped As Overlapped... apparently that isnt right (i dont really know why) and all i did was i changed it to ByVal lpOverlapped as IntPtr and set it to zero (since i dont use overlapped)

not sure why it works, but it works

thanks for the replies Smile | :)

r -€

Re: ReadFile not working in VB.NET

Dave Kreskowiak12-Jan-05 1:06

problem with visual studio .net and the oledb namespace

12-Jan-05 1:06

Glad you got it to work!

ByVal will work so long as you never pass in an OVERLAPPED structure and always pass in a 0 for this parameter. If you ever do pass in an OVERLAPPED structure, then you have to change the declaration back to ByRef lpOverlapped As OVERLAPPED and properly declare the OVERLAPPED structure in your code.

RageInTheMachine9532
"...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome

Anonymous11-Jan-05 14:57

Anonymous

11-Jan-05 14:57

hey all. i'm a college student. in my college visual studio has a tab called "data" and under this tab are objects such as oledbconnection and other oledb and sql objects. anyways, i installed visual studio .net 2003 today (the same version thats installed in my college) and i dont see that tab at all. i tried adding the tab manually, and then adding the oledb objects to the tab, but i dont see any items at all in the oledb namespace present in the list of items i can add. however, when i open a project i did in school that has oledb objects in it, it shows up in the designer, and i can click on it, and its properties will appear in the property window and i can change them no problem like i would in school.

does anyone know what the problem could be?

Re: problem with visual studio .net and the oledb namespace

Dave Kreskowiak12-Jan-05 1:01

Re: problem with visual studio .net and the oledb namespace

12-Jan-05 1:01

Are you sure you don't have the Academic edition of VS? I've never seen the Academic edition, it would be cheap compared to the Profession edition. The Pro edition and above all have this tab. I'm not sure about the Academic edition.

RageInTheMachine9532
"...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome

Anonymous12-Jan-05 4:21

Anonymous

12-Jan-05 4:21

i'm not sure if its professional or academic, but my friends have the same version and i'm the only 1 with this problem

Re: problem with visual studio .net and the oledb namespace

Dave Kreskowiak12-Jan-05 11:58

File Delete problem in Windows Service

12-Jan-05 11:58

Well, there are a couple of possibilities. I couldn't find anything that says there is a known problem, so, we're left with the stupid things to check.

1) The Data tab will only show up when there is a designable form selected. If your looking at a code window, the Data tab disappears.

2) Make sure that the components update ran correctly before you installed Visual Studio .NET. You might want to try downloading MDAC 2.8 from MSDN. I think the URL is http://msdn.microsoft.com/data[^].

3) Uninstall VS.NET and try reinstalling it.

RageInTheMachine9532
"...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome

P Holroyd11-Jan-05 11:02

11-Jan-05 11:02

Hi,

Basically I have a program that reads emails out of a drop directory and writes them to a SQL Table. The Program then deletes the Email with a 'File.Delete' command.

If I run this in a normal one click test app it deletes the files fine and they disapear out of the drop directory, however when I put the same code into a NTService the files do not disappear straight away and cause a problem the next time my service comes to read the drop directory.

I'm assuming that the files are marked for deletion and locked somehow.

Is this is because the file delete is a lower priority somehow in the service?

Thanks in advance.

Dave Kreskowiak11-Jan-05 15:47

11-Jan-05 15:47

P Holroyd wrote:
I'm assuming that the files are marked for deletion and locked somehow.
Is this is because the file delete is a lower priority somehow in the service?

There's no such things as being "tagged" for delete. It's either deleted or it's not. There is also no lower priority placed on deletions.

What you should be looking at is the account that the service is running under. If it's the Local System account, it's entirely possible it doesn't have the appropriate rights to delete the files. Try changing the account that the service runs under to a user account you create specifically for the service. Make sure you give this account the appropriate NTFS Security rights to the folder it needs to read/write/delete date from.

RageInTheMachine9532
"...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome

P Holroyd11-Jan-05 21:01

11-Jan-05 21:01

Yeah I'll try that again, I've tried giving it a different user account but it didn't seem to work. It's possible I didn't set it up right.

The files do disapear when the service stops but that's not much use to me.

I was thinking of maybe creating an exe or a dll to do the delete but would they just inherit the access rights from the service?

Dave Kreskowiak12-Jan-05 0:58

12-Jan-05 0:58

P Holroyd wrote:
The files do disapear when the service stops but that's not much use to me.

?? WTF | :WTF:

?? Then you've got code/problems in there you haven't told us about. I've done this very thing myself without a problem. All I did was create a user account with the appropriate rights to the folder where all the files were copied to (log files from about 140 servers), parse those files up and dump the relevant information into an SQL Server, then delete the files.

RageInTheMachine9532
"...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome

Anonymous12-Jan-05 1:37

Anonymous

12-Jan-05 1:37

Ok so it gets stranger and stranger, I deployed the program on a Win2000 server and it started working ok. The files come in get logged and then deleted ok.

I'm thinking you might be right because a local system service on a server will have greater access rights?

Here's a copy of the code :-

<br />
Private Sub ReadWrite()<br />
        Try<br />
<br />
            Dim iDropDir As New CDO.DropDirectory<br />
            Dim pMsgs As CDO.IMessages<br />
            Dim pAttachments As CDO.IBodyParts<br />
            Dim i As Integer<br />
<br />
            pMsgs = iDropDir.GetMessages(Directory.GetDirectoryRoot _     (AppSettings("Drop Dir")) & AppSettings("Drop Dir"))<br />
<br />
            For i = 1 To pMsgs.Count<br />
                pAttachments = pMsgs(i).Attachments()<br />
                If LogUpdateMsg(pMsgs(i), 2, pAttachments) <> False Then _<br />
                Kill(iDropDir.GetMessages.FileName(pMsgs(i)))<br />
               Next<br />
            <br />
        Catch ex As Exception<br />
              ExceptionManager.Publish(ex)<br />
        End Try<br />
    End Sub<br />

I'm picking up the Emails/Attachments here and calling a stored procedure in 'LogUpdateMessage' to log them, if the log is ok I kill the file.

On my test machine the program loops once, logs the message ok but doesn't delete the message from the drop directory. When the loop executes again the messages are picked up again and I get the following exception when I try to read the attachments :-

General Information <br />
*********************************************<br />
Additional Info:<br />
ExceptionManager.MachineName: STU<br />
ExceptionManager.TimeStamp: 12/01/2005 12:43:54<br />
ExceptionManager.FullName: Microsoft.ApplicationBlocks.ExceptionManagement, Version=1.0.1795.22054, Culture=neutral, PublicKeyToken=null<br />
ExceptionManager.AppDomainName: smsservice.exe<br />
ExceptionManager.ThreadIdentity: <br />
ExceptionManager.WindowsIdentity: NT AUTHORITY\SYSTEM<br />
<br />
1) Exception Information<br />
*********************************************<br />
Exception Type: System.UnauthorizedAccessException<br />
Message: Access is denied.<br />
<br />
TargetSite: CDO.IBodyParts get_Attachments()<br />
HelpLink: NULL<br />
Source: CDO.Message.1<br />
<br />
StackTrace Information<br />
*********************************************<br />
   at CDO.MessageClass.get_Attachments()<br />
   at SMSService.Service1.ReadWrite()

But like I say the same code running on the server is ok and this exception never gets raised.

P Holroyd12-Jan-05 1:46

12-Jan-05 1:46

Ooops the above post was me btw.

Dave Kreskowiak12-Jan-05 11:51

12-Jan-05 11:51

Anonymous wrote:
I'm thinking you might be right because a local system service on a server will have greater access rights?

Not so. There is no rights difference between the SYSTEM accounts from one edition of Windows 2000 to the next. There ARE differences between the CDO libraries though.

Your code uses the .GetMessages function of the CDO DropDirectory CoClass. I'm suspecting that there is a difference between the versions installed on your systems. Specifically, I'm suspecting that .GetMessages() is not releasing it's file handles immediately while enumerating the message files. This would cause an "Access Denied" exception when you tried to delete the files. What you might want to try is calling the .Dispose() method on your iDropDirectory object before you delete the files in that folder.

RageInTheMachine9532
"...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome

P Holroyd13-Jan-05 7:17

13-Jan-05 7:17

OK I'll try that as well, that sounds logical. It sounds like best practice even if it's not the root of my problems.

I'll report back on the results Smile | :)

P Holroyd17-Jan-05 23:35