Prev: Remove specific items from file name in folder
Next: VBS Signature for Outlook - Containing a Hyperlink
From: cjake2299 on 17 Dec 2009 12:49
All-

I have an excel spreadsheet containing 7509 rows (Products), and I
have a directory containing 16,421 image files. The images are all
named by the Product Number, which is also a row in my excel
spreadsheet. I need to query the excel spreadsheet to get all the
product numbers, then check my current directory for a
<productNumber>.jpg file. If the file exists, I need to COPY it to a
new directy. If it does not exist, I need to write the product number
to a text file that I can go over after the fact to obtain the image
for the products missing photos.
From: cjake2299 on 17 Dec 2009 13:00
On Dec 17, 9:49 am, cjake2299 <cjake2...(a)gmail.com> wrote:
I should also note that I have absolutely no idea how to query the
excel spreadsheet. I can tell you I need to query 12072009.xls Sheet1
Column "A", begining at row 2 (as row 1 is the header row). I also do
not have any starting script or script in the works at this time, but
am working on it feverishly.
From: Pegasus [MVP] on 17 Dec 2009 14:53


"cjake2299" <cjake2299(a)gmail.com> schrieb im Newsbeitrag
news:9968e839-e4fc-4b8a-9075-3408b17e006d(a)s21g2000prm.googlegroups.com...
> All-
>
> I have an excel spreadsheet containing 7509 rows (Products), and I
> have a directory containing 16,421 image files. The images are all
> named by the Product Number, which is also a row in my excel
> spreadsheet. I need to query the excel spreadsheet to get all the
> product numbers, then check my current directory for a
> <productNumber>.jpg file. If the file exists, I need to COPY it to a
> new directy. If it does not exist, I need to write the product number
> to a text file that I can go over after the fact to obtain the image
> for the products missing photos.

Here are the three steps you need to code:
Step 1: Read the first cell of each of your 7509 rows.
Step 2: Use the oFSO FileExists method to see if the file exists. The
helpfile script56.chm which you can download from the Microsoft site will
show you how it's done.
Step 3: If the file exists, use the oFSO copyfile method to copy the file,
as shown in script56.chm. If the file does not exist, use the oFSO writeline
method to record the fact.

Here is a basic example for Step 1:

sListname = "d:\Test.xls"
Set oExcel = CreateObject("Excel.Application")
oExcel.Visible = True
Set oWorkbook = oExcel.Workbooks.open(sListname)
Set oSheet = oWorkbook.Worksheets(1)

i = 2
Do
sImagename = oSheet.cells(i, 1)
if sImageName = "" then Exit Do
WScript.echo sImagename
i = i + 1
Loop
oWorkbook.close
oExcel.application.quit


 | 
Pages: 1
Prev: Remove specific items from file name in folder
Next: VBS Signature for Outlook - Containing a Hyperlink