|
Hello, I'm creating a macros in Excel that allows a person to export large amounts of data from the excel page and save to html. I'm using an example from the book VBA and Macros for Microsoft Excel by Bill Jelen, Tracy Syrstad. Except I've added a custom form that allows the user to determine how many rows of data to extract per html page as well as other features so that it will work with different files of different columns and column names.
My problem is this. I have an excel field called IMAGE_URL. It contains a normal URL to a product picture on the web. Instead of letting my html pages point to that picture online, I'd rather copy the picture and save it into the same folder as my HTML files so that when I upload the pages to the web they point to the pics in the folder and not elsewhere on the web.
Does anyone have any advice? I basically need to know how to access the picture with the given link and then save it in my folder but I need to automate it within the macros code. I'm trying to use createobject() but have yet to come up with anything.
To calrify exactly what I am doing. I have several excel files with thousands of rows of data. The fileds are like
ProductID, ProductName, Brand, Description, Price, Image_URL
I've created the macros that allows me at the click of a button to send that data to html files depending on how many rows of products I want to go into an html page. Inside the excel file I have place the html tags such as 'Print #1, "" etc so all the formatting is predefined through out each html page. I just need away to save the images from the web inside the same folder instead of point to the images elsewhere on the web.
|
|
|
|
|
I adapted the code here[^] to do something very similar in VBScript. I was downloading an .EXE file, but the concept applies to what you're doing. Let's see, where did I put that script...
GetBinaryFile "http://sourceURL", "destinationFilePath"
Private Function NavigateToPage(objIE, url)
attempts = 0
timeout = 5000
i = 0
While attempts < 5
objIE.Navigate2 url
While (objIE.ReadyState < READYSTATE_COMPLETE) And (i < timeout)
WScript.Echo "Waiting... " & i
WScript.Sleep 100
i = i + 100
Wend
If i >= timeout Then
WScript.Echo "Retrying..."
attempts = attempts + 1
i = 0
Else
' Domain should not be blank if the page worked!
If objIE.Document.Domain <> "" Then
NavigateToPage = True
Exit Function
Else
NavigateToPage = False
Exit Function
End If
End If
Wend
NavigateToPage = False
End Function
Private Sub GetBinaryFile(uri, filepath)
Const adTypeBinary = 1
Const adSaveCreateOverWrite = 2
Const adSaveCreateNotExist = 1
Set xml = CreateObject("Microsoft.XMLHTTP")
xml.Open "GET", uri, False
WScript.Echo "Downloading " & uri & "..."
xml.Send
Set oStream = CreateObject("Adodb.Stream")
oStream.Type = adTypeBinary
oStream.Open
oStream.Write xml.responseBody
WScript.Echo "Saving to file " & filepath & "..."
oStream.SaveToFile filepath, adSaveCreateOverWrite
oStream.Close
set oStream = Nothing
Set xml = Nothing
End Sub
Dave Kreskowiak
Microsoft MVP - Visual Basic
|
|
|
|
|
That's exactly what I'm trying to do but I still can't get it to work. All I need is this part of the code you provided.
Const adTypeBinary = 1
Const adSaveCreateOverWrite = 2
Const adSaveCreateNotExist = 1
Dim uri
uri = "http://www.csdn.net/Images/logo_csdn.gif"
MyPath = ThisWorkbook.Path
ThisFile = MyPath & Application.PathSeparator
Set xml = CreateObject("Microsoft.XMLHTTP")
xml.Open "GET", uri, False
xml.Send
Set oStream = CreateObject("Adodb.Stream")
oStream.Type = adTypeBinary
oStream.Open
oStream.Write xml.responseBody
oStream.SaveToFile ThisFile, adSaveCreateOverWrite
oStream.Close
Set oStream = Nothing
Set xml = Nothing
When I get to the line
oStream.SaveToFile ThisFIle, adSaveCreateOverWrite
I get an error saying: Run-time error '3004' Write to file failed. Basically I just want the image to be stored in the same folder as my excel file. I may have an error in my file path.
I've tried another example that does work and is very similar to the one you provided but contains a minor problem.
Const adTypeBinary = 1
Const adSaveCreateNotExist = 1
Const TemporaryFolder = 2
Dim oStream ' ADODB.Stream
Dim oFSO ' Scripting.FileSystemObject
Dim xml ' Microsoft.XMLHTTP
Dim sURL
Dim oFil
Dim sImagePath
' create objects
Set oFSO = CreateObject("Scripting.FileSystemObject")
Set xml = CreateObject("Microsoft.XMLHTTP")
Set oStream = CreateObject("Adodb.Stream")
sURL = "http://www.csdn.net/Images/logo_csdn.gif"
' get the temp file...
MyPath = ThisWorkbook.Path
ThisFile = MyPath & Application.PathSeparator & oFSO.GetTempName
xml.Open "GET", sURL, False
xml.Send
oStream.Type = adTypeBinary
oStream.Open
oStream.Write xml.responseBody
' Do not overwrite an existing file
oStream.SaveToFile ThisFile, adSaveCreateNotExist
oStream.Close
' cleanup...
'oFil.Delete
Set oStream = Nothing
Set xml = Nothing
Set oFSO = Nothing
Set oFil = Nothing
The problem with this code is
ThisFile = MyPath & Application.PathSeparator & oFSO.GetTempName
The oFSO.GetTempName creates and stores a .tmp file which is no good to me.
If you could help me figure this out I'd be very grateful
|
|
|
|
|
I got your example to work. Had the filename messed up.
Many thanks for the code!
You saved my life.
|
|
|
|
|
Hello,
I would like to create a program that can unpackprograms that have been packed. In other words I would like to create a program that does the opposite of UPX. For now I just would like to unpack UPX Packed files. Could someone point me to a tutorial or give me some advice on how to do this ??
Thanks
Al968
Avast Antivirus - http://www.avast.com
|
|
|
|
|
Hi all,
im trying to add something to my database if a particular form is open. ive embedded the following IF statement inside a larger if statement.
If form2.Visible = True Then
dsNewRow.Item("UseCaseID") = "2"
End If
The if statement is on form3. The above code doesnt seem to work for some reason, was hoping you guys may be able to help.
Thanks in advance
Jaidev
|
|
|
|
|
It doesn't work because your instance of Form3 doesn't have a reference to the instance of Form2. Your trying to look at the value of Visible as if it were a static property on a class. This is not VB6, where every form existed automatically and globally. Form3 needs a reference to an existing Form2 object passed to it before this will work.
Dave Kreskowiak
Microsoft MVP - Visual Basic
|
|
|
|
|
How do I do this?
I have around 15 different forms, ideally I would like them all to exist automatically and globally, is there a way in which I can do this? so that I can use different forms like in my code.
Thanks in advance.
Jaidev
|
|
|
|
|
The VB6 way is nasty, that's why it's history.
The best way to do it, is to use delegates to pass information between forms. The other way, is to pass your form2 instance in to your form3 instance, so it has a reference to it. Finally, the Application object has a collection of open forms, which you can look through, I believe.
Christian Graus - Microsoft MVP - C++
Metal Musings - Rex and my new metal blog
|
|
|
|
|
You can create a static (shared in VB) property in each form class where you store the current instance of the form. In the constructor of the form you store the reference to the instance:
This of course means that you can only create a single form from each form class.
---
single minded; short sighted; long gone;
|
|
|
|
|
Hi,
Thanks for your replies, however I dont fully understand the jargon, would it be possible for you guys to send me example codes on how this can work?
Cheers
Jaidev
|
|
|
|
|
If you don't understand what shared is, you should buy a VB.NET book and work through it. Delegates are widely documented on the web, if you google any of the 'jargon' we've given you, you'll get more detailed examples than you will find in a forum post.
Christian Graus - Microsoft MVP - C++
Metal Musings - Rex and my new metal blog
|
|
|
|
|
What ive done is created a new class (class1) in which ive stated my variable.
then ive referenced this class, but its still not working.
form3.
Dim var As New class1()
---------
If var.numberofcalls = 1 Then
dsNewRow.Item("UseCaseID") = "2"
End If
form1.
Dim var As New class1() --> seems to work
class1.
Public Class class1
Public numberofcalls = 0
End Class
Am i pointing in the right direction?
Jaidev
|
|
|
|
|
Perhaps, the problem is, you didn't make the variable 'shared'. Shared means, there's only one instance of the variable across all class instances, which means you can set it in one class, and get it in another, without having to pass an instance around. In C#, it's called 'static'. Google these terms and you should find good examples of how it works.
Christian Graus - Microsoft MVP - C++
Metal Musings - Rex and my new metal blog
|
|
|
|
|
How do I create an ESRI shapefile for my Dundas map control
|
|
|
|
|
Don't Dundas have support forums specific for this kind of thing?
|
|
|
|
|
Hi all,
I built client server application which using tcp protocol
example code :
listener = New Sockets.TcpListener(System.Net.IPAddress.Any, PORT_NUM) ...
If im the server and I behind router, i cannot communicate with a client side only if i open a port in my router.
I wonder how all multiplayer's games work if no one needs to open any port?
Can it be the server side is a not a home computer but a real server that control many clients?
Should i need to run a thread in the server that manage all players (all players are the clients?) ?
if this the way (using a thread that always run in the server and manage all players from home computers which are the clients)
What will happened if the thread stuck or the server shutdown, this will kill all users(players).
Ill appreciate any help building multiplayer's gaming manage by server side (or any other good technology for multiplayer's games using router)
|
|
|
|
|
As the game server has to work as a server, it has to have a port open in the firewall, regardless if it's a real server or just a regular computer acting as a server.
The server will have an application running that handles the traffic from the player's computers. How you choose to use threading depends on how you communicate with the player's computers, and what else the application should do. A web server for example uses a thread pool and handles each page request in a separate thread.
---
single minded; short sighted; long gone;
|
|
|
|
|
how could an image be captured from an avi file in vb.net?
|
|
|
|
|
There is at least one article on this site that uses the DirectShow wrapper to do it in C#. you could use the DLL and call it from VB.
Christian Graus - Microsoft MVP - C++
Metal Musings - Rex and my new metal blog
|
|
|
|
|
hello all,
I have a data grid in vb.net 2003 connected to the database
what I need is to select specific data by choosing the employeeID using comboBox
e.g: comboBox: a.srour
datagrid: view a.srour details ....
|
|
|
|
|
There are definately articles on the site for putting a combo box inside a data grid.
Christian Graus - Microsoft MVP - C++
Metal Musings - Rex and my new metal blog
|
|
|
|
|
thank u Christian Graus, but what i want that the combobox outside the datagrid, it is a kind of filter.
select a value from comboBox for its details to be viewed in the datagrid
srour_costavo@hotmail.com
|
|
|
|
|
Oh, OK. That's even easier. You just handle the index changed event on the combo, and run fresh SQL to fill the grid based on the selection. The combobox lets you store a value that's associated with each string entry, so you can hide the Id you need in there.
Christian Graus - Microsoft MVP - C++
Metal Musings - Rex and my new metal blog
|
|
|
|
|
ok man but the real problem is in the syntax, the concept is understood,
the help I need is syntax help would u do that
srour_costavo@hotmail.com
|
|
|
|