Whats wrong with this code??

[karenros]

Dim fsBLOBFile As System.IO.FileStream
fsBLOBFile = System.IO.File.Open(Pict, FileMode.Open, FileAccess.Read)
Dim br As System.IO.BinaryReader
br = System.IO.BinaryReader(fsBLOBFile.Length)
Dim bytBlobdata() As Byte
byblobdata = br.ReadBytes(fsBLOBFile.Lenght)
br.Close
fsBLOBFile.Close

I am getting a compiler error at Dim fsBLOBFile As System.IO.FileStream


Thanks,
Karen

[Kenneth Hobs]

What reference did you set in the VBE's Tools menu? When you did System., intellisense should have helped if your reference was set and proper.

That looks like vb.net code. You will need to use another method. The file scripting method would work for this most likely.

Some other parts are missing.

Were you wanting to do this in vba or vb.net?

[karenros]

I havent set any reference i am a newbie in Vba... i want to do this in vba for excel.. What reference should i set and how??

Thanks.

[Kenneth Hobs]

Are you a vb.net coder then?

It is better to explain what you need. We can then code a method to do it.

[karenros]

Yeah I am a VB.Net programmer and this is my vb.net code

'Read jpg into file stream, and from there into Byte array. 
Dim fsBLOBFile As New FileStream(strBLOBFilePath, FileMode.Open, FileAccess.Read) 
Dim bytBLOBData As Byte() = New Byte(fsBLOBFile.Length - 1) {} 
fsBLOBFile.Read(bytBLOBData, 0, bytBLOBData.Length) 
fsBLOBFile.Close() 

Basically I am trying to convert a image as a blob.

Thanks,
Karen

[Kenneth Hobs]

It might be easier for you to just write the code as an EXE in vb.net. You can then use Shell() in VBA to execute it. For others to use your EXE, they will have to have the vb.net framework files of course.

If you want to stick with an all VBA method, here is an example using the filescripting object. You will have to set the Reference first as I used an early binding method to allow intellisense to work. You can probably modify it from there.
[vba]Sub text_Parse()
Rem Needs Reference: MicroSoft Script Runtime, scrrun.dll
Rem Instructions: http://support.microsoft.com/default...b;en-us;186118
Dim FSO As New FileSystemObject
Dim fstr As Scripting.TextStream
Dim sData As String

Set fstr = FSO.OpenTextFile("C:\your file.txt")

Do Until fstr.AtEndOfStream
sData = fstr.ReadLine

If Mid(sData, 10, 9) = "Market " Then
sNumber = Trim(Mid(sData, 1, 2))

End If

Loop

End Sub[/vba]

[karenros]

Yeah I like the idea of writing it VB.net and then using Shell to call it... Can you give me some examples using shell in VBA..

[Kenneth Hobs]

Shell is in vb.net. Like vb.net, in the VBE, type Shell and press F1 to browse the help for it.
e.g.
[vba]Sub Test()
ChDir "c:\myfiles\Excel"
Shell "cmd /c dir *.* > c:\temp\test.txt", vbNormal
End Sub[/vba]
You would probably use it something like:
[vba]Sub RunMyEXEProgram()
Shell ThisWorkbook.Path & "\VBNETProgramName.exe", vbNormal
End Sub[/vba]

[karenros]

Well I tried putting that code in a asp.net program and then ran and it worked good and after inserting it into sql server and retreiveing the binary data from sql server it came up to 19 pages in word... and i found out that so much data cannot fit in a cell of a spread sheet. So is there any other work around???

[Kenneth Hobs]

I am not sure how that relates to your question or what data if any that you are importing? Given that it seems that you want to poke alot of data into a cell, then I see three methods. Two methods involve using MSWord. If you need it in Excel, import the data to an MSWord object. Or, just import to MSWord and skip Excel.

For the third method, you would need to parse the data. vbCrLF should be a good character to Split() data. Once the data is in an array, you can easily add it to multiple cells.

There might be other methods but I would have to know the nature of the data and how you want it shown in Excel. I don't do sqlserver but I have done other database query's by various means such as ADO and Data > Import External Data.

[karenros]

Sorry for the confusion.. My ultimate goal is to import a picture from excel to sql server..so i thought if i could get the binary value of the picture and then import the binary value to sql server varbinary datatype and then display it in it a report

[Kenneth Hobs]

Can you not just use the filename for the object in sqlserver? Here's how to save a picture object in Excel to a file.
[VBA]SavePicture Sheet1.Image1.Picture, ThisWorkbook.Path & "\Image1.emf"
[/VBA]

[karenros]

can u explain more about ur last comment.. kinda confused me.

[karenros]

Kenneth,
I am trying to use this code in my spread but getting an error that says Expected function or variable

folderName = SavePicture(ActiveSheet.filename, filename)

filename is a string.. which is path for the File.. and ActiveSheet.filename is the Picture.

any help will be appreciated.

Thanks,
Karen

[Kenneth Hobs]

SavePicture is a Sub so it returns no value.

I would advise against naming objects the same as some other variable.

So, you should probably do:
[VBA]SavePicture ActiveSheet.filename.Picture, filename[/VBA]

You can attach a short example xls if needed.

[karenros]

Thanks for the getting answer..

I am getting an error that says runtime error 438 Object doesnt support this property or Method and its pointing to

SavePicture ActiveSheet.filename.Picture, folderName

[Kenneth Hobs]

What is the value of folderName? Are you sure that the object name for the Picture object is named filename?

A posted xls would show us what is going on.

[karenros]

This is my code

Dim Pict
Dim ImgFileFormat As String
Dim PictCell As Range
Dim Ans As Integer
Dim nextcell
Dim IPict As Variant
'ActiveSheet.Protect True, True, True, True, True
ImgFileFormat = "Image Files (*.jpg),others, tif (*.tif),*.tif, jpg (*.jpg),*.jpg"
GetPict:
Pict = Application.GetOpenFilename(ImgFileFormat)
'Note you can load in any nearly file format
If Pict = False Then End
'Ans = MsgBox("Open : " & Pict, vbYesNo, "Insert Picture")
'If Ans = vbNo Then GoTo GetPict
'Now paste to userselected cell
GetCell:
Set PictCell = Application.ActiveCell  'Application.InputBox("Select the cell to insert into", Type:=8)
If PictCell.Count > 1 Then MsgBox "Select ONE cell only": GoTo GetCell
PictCell.Select
Dim FSO As Object
Set FSO = CreateObject("Scripting.FileSystemObject")
Dim filename As String
Dim folderName As String
filename = FSO.GetBaseName(Pict)
 ActiveSheet.Pictures.Insert(Pict).Select
  
  Selection.Name = filename
  Selection.Height = Application.InchesToPoints(1)
  Selection.Width = Application.InchesToPoints(1.75)
  folderName = "c:\Temp\" & filename
  SavePicture Selection.Name.Picture, folderName