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?

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.
--
HTH,
Barb Reinhardt



"Jaybird" wrote:

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?

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?

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.

"Jaybird" wrote:

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?

On May 5, 10:12*am, dmoney wrote:
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.

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Jaybird" wrote in message
...
On May 5, 10:12 am, dmoney wrote:
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.

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

https://report.converge.com/dell/int...ck_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.

PPID


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Jaybird" wrote in message
...
Hey, thanks... Here's the url:

https://report.converge.com/dell/int...ck_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.

Sorry. I didn't understand the response.

The url asked to fill in PPID (password)

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Jaybird" wrote in message
...

Sorry. I didn't understand the response.

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

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

Alan Beban

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

Thanks for that... I think.

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Jaybird" wrote in message
...
The PPID is actually the prompt for the barcode scan. It's not very
user friendly.

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.

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!

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" wrote in message
...
The PPID is actually the prompt for the barcode scan. It's not very
user friendly.

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.

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...

Mr. Williams, thank you for your generous help... I've no doubt that
your code will work (a few modifications notwithstanding), but I may
as well admit that I don't understand how it works. Even more basic,
I don't understand how to implement it. I was assuming that it could
be run with a command button, but I may be wrong. Please forgive my
limited understanding... If what I know is reliable, pos1 and pos2
are the barcode scans that are to be input? Or is that actually the
response? You've already been a great help, so I hate to bug you
further. Just so you know, I've decided to bite the bullet and take
some programming classes, but don't know when I'll have the chance.

Place the following in a module. Later you can assign PPIDtest to a button
but for now just run it with F5. Better still, while figuring it out put the
cursor in PPIDtest and keep pressing F8.

The first time I had to be patient and eventually a security certificate
prompt popped up that I had to dismiss. First time suggest you only select
one cell with a barcode


Sub PPIDtest()
Dim sCode As String
Dim sReply As String
Dim rng As Range

On Error Resume Next
Set rng = Application.InputBox("Select PPID cells in a column", _
"PPIDtest", _
Selection.Address, _
Type:=8)
On Error GoTo 0

If rng Is Nothing Then Exit Sub ' user cancelled
If rng.Columns.Count 1 Then Exit Sub '2+ columns selected
If rng.Rows.Count 100 Then
'best to set some aribtary limit
MsgBox "Limit set at 100"
Exit Sub
End If

On Error GoTo errH

For Each cel In rng
sCode = cel.Text
sReply = GetPPID(sCode)
cel.Offset(0, 1).Value = sReply
Next

Exit Sub
errH:
MsgBox Err.Description

End Sub

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 pos1 0 And pos2 0 Then
' rV = Left(tmp, pos2 - 1)
' rV = Right(rV, Len(rV) - (pos1 + Len(FRAG2)))
' End If

rV = tmp ' to be parsed later

Set msxml = Nothing
End If

GetPPID = rV

' example of return string to be parsed
' [center][BR][BR][BR][BR][FONT COLOR='green']
' PPID: THE-BARCODE-HE KEEP AT CONVERGE![/font][BR]
' [BR][HR][BR][BR][FORM ACTION = 'check_battery.php']PPID:
' [INPUT TYPE='text' NAME = 'ppid'][BR][BR][INPUT TYPE='SUBMIT'
' VALUE = ' SUBMIT '][/FORM]

End Function

You will need to decide how to parse potential return strings for yourr own
needs, presumably you won't want the entire string as in the above example.

Regards,
Peter T

"Jaybird" wrote in message
...
Mr. Williams, thank you for your generous help... I've no doubt that
your code will work (a few modifications notwithstanding), but I may
as well admit that I don't understand how it works. Even more basic,
I don't understand how to implement it. I was assuming that it could
be run with a command button, but I may be wrong. Please forgive my
limited understanding... If what I know is reliable, pos1 and pos2
are the barcode scans that are to be input? Or is that actually the
response? You've already been a great help, so I hate to bug you
further. Just so you know, I've decided to bite the bullet and take
some programming classes, but don't know when I'll have the chance.

You have been an enormous help, sir. I wish I understood the code
better, but I expect that will be a long journey. My previous
experience with VBA was very simplistic. I have noticed that there
seems to be distinct 'styles' of script, which makes a difference when
trying to learn. In any case I appreciate it very much. Today, I got
an 'attaboy' at work for coming up with a solution for a different
problem. Rest assured, I credited all the help I received at this
discussion group. Hopefully, I will be able to return the favor
someday.