Ignorance is not bliss

[Jaybird]

Howdy. Brand new to the excel groups... Hope you'll forgive me. I'm
traditionally an Access user. My new employer doesn't have it and
isn't likely to get it. We have a situation that is very time
consuming and I'd like to hear the opinion of the experts. At my work
we basically scan barcodes from product into an excel spreadsheet.
After a long list of about 300 to 500, we are to check those barcodes
against an online database of reliable parts. Great, except that the
interface for that website will only allow the data from one barcode
at a time. Right now we must copy and paste each one. Very time
consuming. In Access, I could probably dream up a way to accomplish
this using VBA, and I'm sure the solution is similar in Excel, but I'm
not sure. Can anyone point me in the right direction?

 

[Barb Reinhardt]

You could probably do something very similar with VBA in Excel. I've not
used Access, so I don't know what the similarities/differences are with VBA.
If you have some specific questions, don't hesitate to ask.

 

[Jaybird]

Thanks. The interface is completely different in Excel, so I'm not
sure where to turn... I'm assuming that I have to create a button
that starts a macro or a VBA module. Some of the posters have done
similar things but I don't know the terminology, so my searches of the
discussions are hit and miss. I have read references to a web query
that could conceivably upload the information one scan at a time, but
I'm unfamiliar with the web design of the site, and can't seem to get
it to work. Does anybody know what it is that I might be looking for?

 

[Jaybird]

I suggest doing a little more research on the website to see if it is
possible to download the part numbers then compare or even better see if you
can get a backdoor connection via SQL at which point you could querry the
database behind the web interface.

Hmm... I haven't tried an ODBC connection. Maybe that's possible.
But considering how little support we get from IT at this level, I
doubt I have the permissions. I was trying to do and end run around
them.

 

[Don Guillett]

would an external query to the website help. Give the url and what you want.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

I suggest doing a little more research on the website to see if it is
possible to download the part numbers then compare or even better see if
you
can get a backdoor connection via SQL at which point you could querry the
database behind the web interface.

Hmm... I haven't tried an ODBC connection. Maybe that's possible.
But considering how little support we get from IT at this level, I
doubt I have the permissions. I was trying to do and end run around
them.

 

[Jaybird]

Hey, thanks... Here's the url:

https://report.converge.com/dell/internal/check_battery.php

Perhaps if you can figure out what kind of query will work, you can
point me in the right direction. I would very much like to figure
this stuff out, so I'm moving one step at a time and asking for help
when I need it. Thank you for your help.

 

[Don Guillett]

PPID

 

[Jaybird]

Sorry. I didn't understand the response.

 

[Don Guillett]

The url asked to fill in PPID (password)

 

[Alan Beban]

But then Thomas Gray never said it was; what he said was

"Where ignorance is bliss, 'Tis folly to be wise."

Alan Beban

 

[Jaybird]

The PPID is actually the prompt for the barcode scan. It's not very
user friendly.

 

[Jaybird]

Thanks for that... I think.

 

[Don Guillett]

You are not helping me to help you so I quit.

 

[Jaybird]

I beg your pardon. Perhaps if you were to tell me what it is that you
would like me to tell you, I could move things along a little better.
I apologize if I didn't give you enough information. I simply don't
know what I can tell you that will help.

 

[Jaybird]

Mr. Guillet! My apologies! My earlier comments were in response to
Alan Beban's comments about Thomas Gray... They were not directed at
you! I hope you weren't offended!

 

[Peter T]

OK, so the idea is you enter a barcode, then what do you expect in return,
description, stock availability, price, etc.

I entered 123456789 and got a plain message back
"entered PPID: 123456789: KEEP AT CONVERGE!"

Web queries are not my speciality but there are there are some around here
who are pretty adept with XL-url stuff. Describe how you store and select
the barcode(s) you want to submit into that PIDD, and what you expect to get
back and what you want to do with it. Keep your fingers crossed!

Regards,
Peter T

 

[Jaybird]

Thank you, Peter. Basically, all we need to do is keep entering
PPID's. The response, "Keep at Converge" is desireable. It means
that we don't have to segregate it from the rest of the product. If
the product has a known problem, a different message will appear.
I've never run across it, although I've spent multiple hours cutting
and pasting PPID's. (This is the main reason I would like to find a
different method.) Basically, we start with a pallet of product. The
pieces are uniquely identified by their PPIDs. We separate all the
pallet containers, open them up, and scan the individual PPIDs into a
spreadsheet. We need to keep a record of the PPIDs for verification
and inventory purposes, or we would simply scan the barcodes directly
to the website. The big problem is that we sometimes have thousands
of scans to enter into the website. It's tedious and time consuming.
I'm looking for a way to automate the entering of PPIDs into the
website and for recording the response from the website in the
spreadsheet. I used to have a program that I used to hunt down and
delete corrupted records from an Access database, so I have a "little"
VBA experience. I'm not a programmer by any stretch, so I'm hoping
that someone will be able to give me some suggestions and maybe
outline what needs to happen.

 

[Tim Williams]

This works for me, but suspiciously all barcodes I checked returned the
exact same message, which makes me think this is a bogus search page.

Tim

'*****************************************
Function GetPPID(PPID) As String
Const URL As String =
"https://report.converge.com/dell/internal/check_battery.php?ppid="
Const FRAG1 As String = "'green'>"
Const FRAG2 As String = "</FONT>"
Dim msxml As Object
Dim rV, tmp, pos1, pos2

rV = ""

If PPID <> "" Then
Set msxml = CreateObject("Microsoft.XMLHTTP")
msxml.Open "Get", URL & PPID, False
msxml.send
tmp = msxml.responseText
pos1 = InStr(tmp, FRAG1)
pos2 = InStr(tmp, FRAG2)
If pos2 > 0 And pos2 > 0 Then
rV = Left(tmp, pos2 - 1)
rV = Right(rV, Len(rV) - (pos1 + Len(FRAG2)))
End If
Set msxml = Nothing
End If

GetPPID = rV

End Function

 

[Peter T]

Hi Tim,
I'm pleased I prompted the OP for a little more, I've learnt something!

suspiciously all barcodes I checked returned the
exact same message

Perhaps OK if "Keep at Converge" is the default response for barcodes that
do not indicate some problem. Which leads me to wonder if other responses
will arrive in not 'green' font but say 'red'. If so the parse bit would
need to be tweaked.

If pos2 > 0 And pos2 > 0 Then

I assume one of these should read 'pos1'

The first time I ran the function I got the IE certificate warning, had to
press OK.

Regards,
Peter T

 

[Jaybird]

Sorry it took me so long to respond... Busy day. Thank you so much
for your help. I don't have time to try it out right now, but I will
tonight. And, you're right. The website seems bogus to me to as
well. It is a requirement that we submit the PPID's before releasing
the product, however. Ah, if I were king...