Need help in VB

Craigbob · Joined: 04 Oct 2007 Posts: 3

Hi,

I'm new here and it has been several years since I've done any serious VB
coding. I used to do a lot of VB6 development and have not really touched it
in about 8 years. .NET is brand new to me as well.

I need to create a quickie application either in VB6 or preferably VB.NET
that does the following:

Reads through a directory of .XLS files reads the contents of 2 cells and
renames the file based on the contents of those 2 cells.

In the old day's I'd have whipped this out in a couple of hours, but I've
discovered that I've forgotten a lot of my VB syntax etc...

Can any one help by providing me a sample to get me started?

Thanks

Craig

Archived from group: microsoft>public>vb>enterprise

priyanka · Joined: 04 Oct 2007 Posts: 2

hi,
see u can get easily the code to read .XLS file in VB6.0 but what do u
mean by changing the name of the directory according to the content of
the cells?

Craigbob wrote:
> Hi,
>
> I'm new here and it has been several years since I've done any serious VB
> coding. I used to do a lot of VB6 development and have not really touched it
> in about 8 years. .NET is brand new to me as well.
>
> I need to create a quickie application either in VB6 or preferably VB.NET
> that does the following:
>
> Reads through a directory of .XLS files reads the contents of 2 cells and
> renames the file based on the contents of those 2 cells.
>
> In the old day's I'd have whipped this out in a couple of hours, but I've
> discovered that I've forgotten a lot of my VB syntax etc...
>
> Can any one help by providing me a sample to get me started?
>
> Thanks
>
> Craig

Craigbob · Joined: 04 Oct 2007 Posts: 3

I don't want to change the name of the directory, I want to rename the file
based on teh value of 2 cells. So I would go through each file in the folder
read teh value of 2 cells and do a filemove to a new name based on the
content found in those cells. If it would create a duplicate filename, I'd
append a number to the end to avoid dupes.

Craig

"priyanka" wrote:

> hi,
> see u can get easily the code to read .XLS file in VB6.0 but what do u
> mean by changing the name of the directory according to the content of
> the cells?
>
> Craigbob wrote:
> > Hi,
> >
> > I'm new here and it has been several years since I've done any serious VB
> > coding. I used to do a lot of VB6 development and have not really touched it
> > in about 8 years. .NET is brand new to me as well.
> >
> > I need to create a quickie application either in VB6 or preferably VB.NET
> > that does the following:
> >
> > Reads through a directory of .XLS files reads the contents of 2 cells and
> > renames the file based on the contents of those 2 cells.
> >
> > In the old day's I'd have whipped this out in a couple of hours, but I've
> > discovered that I've forgotten a lot of my VB syntax etc...
> >
> > Can any one help by providing me a sample to get me started?
> >
> > Thanks
> >
> > Craig
>
>

boaz · Joined: 04 Oct 2007 Posts: 259

You open the XLS file just like any MDB file using ADO. The name of the
sheet is the name of the table. The name of the column is the name of the
field in a table.

So...

1) Open connection to the XLS file

My_Connection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & & ";" & _
"Extended Properties=""Excel 8.0;"""

2) Point the Recordset to the Sheet in the XLS file

My_Recordset.Open "Select * From [Sheet1$] Where....something something"

3) Loop through the rows in the Sheet to get the values in the columns

While Not My_Recordset.EOF
From_Name = My_Recordset(0)
To_Name = My_Recordset(1)
rename...
Wend

"Craigbob" wrote in message @microsoft.com...
> Hi,
>
> I'm new here and it has been several years since I've done any serious VB
> coding. I used to do a lot of VB6 development and have not really touched
> it
> in about 8 years. .NET is brand new to me as well.
>
> I need to create a quickie application either in VB6 or preferably VB.NET
> that does the following:
>
> Reads through a directory of .XLS files reads the contents of 2 cells and
> renames the file based on the contents of those 2 cells.
>
> In the old day's I'd have whipped this out in a couple of hours, but I've
> discovered that I've forgotten a lot of my VB syntax etc...
>
> Can any one help by providing me a sample to get me started?
>
> Thanks
>
> Craig

Craigbob · Joined: 04 Oct 2007 Posts: 3

THANKS< But I did get it solved. Your solution woulf not work for me since
the data in the workbook was not in column/row format. It was in a form type
format. But all of them were consistant so I knoew what cells I had to read.

I do have another question you might have an answer to.

Since i've renamed these files I want to move them to a directory on a
mapped network drive. The directory structure is such that the target folder
can be 3 or 4 levels deep on a drive. It will have the same name as a known
portion of the file name. I can pull that part of the file name out, but once
I have that I'm at a loss of how to find the actual directory it should go
to.

How can I search a drive for a particular folder name? I'm thinking VB.NEET
is a better choice here due to the filesystem objects.

Any ideas you can share?

Thanks

Craig

"boaz" wrote:

> You open the XLS file just like any MDB file using ADO. The name of the
> sheet is the name of the table. The name of the column is the name of the
> field in a table.
>
> So...
>
> 1) Open connection to the XLS file
>
> My_Connection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
> "Data Source=" & & ";" & _
> "Extended Properties=""Excel 8.0;"""
>
>
> 2) Point the Recordset to the Sheet in the XLS file
>
> My_Recordset.Open "Select * From [Sheet1$] Where....something something"
>
>
> 3) Loop through the rows in the Sheet to get the values in the columns
>
> While Not My_Recordset.EOF
> From_Name = My_Recordset(0)
> To_Name = My_Recordset(1)
> rename...
> Wend
>
>
>
>
>
>
>
>
>
> "Craigbob" wrote in message
> @microsoft.com...
> > Hi,
> >
> > I'm new here and it has been several years since I've done any serious VB
> > coding. I used to do a lot of VB6 development and have not really touched
> > it
> > in about 8 years. .NET is brand new to me as well.
> >
> > I need to create a quickie application either in VB6 or preferably VB.NET
> > that does the following:
> >
> > Reads through a directory of .XLS files reads the contents of 2 cells and
> > renames the file based on the contents of those 2 cells.
> >
> > In the old day's I'd have whipped this out in a couple of hours, but I've
> > discovered that I've forgotten a lot of my VB syntax etc...
> >
> > Can any one help by providing me a sample to get me started?
> >
> > Thanks
> >
> > Craig
>
>
>

bz · Joined: 04 Oct 2007 Posts: 55

The file system object is the same in VB6.
I have the sample couple posts below. Try look for it. It has been couple
months ago, I don't remember what I typed.

Basically, you use CreateObject to create the FileSystemObject. And then
you call the functions in VB6.
I think the object creates a tree kind of stuff in memory and then there are
function to search through it.

"Craigbob" wrote in message @microsoft.com...
> THANKS< But I did get it solved. Your solution woulf not work for me since
> the data in the workbook was not in column/row format. It was in a form
> type
> format. But all of them were consistant so I knoew what cells I had to
> read.
>
> I do have another question you might have an answer to.
>
> Since i've renamed these files I want to move them to a directory on a
> mapped network drive. The directory structure is such that the target
> folder
> can be 3 or 4 levels deep on a drive. It will have the same name as a
> known
> portion of the file name. I can pull that part of the file name out, but
> once
> I have that I'm at a loss of how to find the actual directory it should go
> to.
>
> How can I search a drive for a particular folder name? I'm thinking
> VB.NEET
> is a better choice here due to the filesystem objects.
>
> Any ideas you can share?
>
> Thanks
>
> Craig
>
> "boaz" wrote:
>
>> You open the XLS file just like any MDB file using ADO. The name of the
>> sheet is the name of the table. The name of the column is the name of
>> the
>> field in a table.
>>
>> So...
>>
>> 1) Open connection to the XLS file
>>
>> My_Connection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
>> "Data Source=" & & ";" & _
>> "Extended Properties=""Excel 8.0;"""
>>
>>
>> 2) Point the Recordset to the Sheet in the XLS file
>>
>> My_Recordset.Open "Select * From [Sheet1$] Where....something
>> something"
>>
>>
>> 3) Loop through the rows in the Sheet to get the values in the columns
>>
>> While Not My_Recordset.EOF
>> From_Name = My_Recordset(0)
>> To_Name = My_Recordset(1)
>> rename...
>> Wend
>>
>>
>>
>>
>>
>>
>>
>>
>>
>> "Craigbob" wrote in message
>> @microsoft.com...
>> > Hi,
>> >
>> > I'm new here and it has been several years since I've done any serious
>> > VB
>> > coding. I used to do a lot of VB6 development and have not really
>> > touched
>> > it
>> > in about 8 years. .NET is brand new to me as well.
>> >
>> > I need to create a quickie application either in VB6 or preferably
>> > VB.NET
>> > that does the following:
>> >
>> > Reads through a directory of .XLS files reads the contents of 2 cells
>> > and
>> > renames the file based on the contents of those 2 cells.
>> >
>> > In the old day's I'd have whipped this out in a couple of hours, but
>> > I've
>> > discovered that I've forgotten a lot of my VB syntax etc...
>> >
>> > Can any one help by providing me a sample to get me started?
>> >
>> > Thanks
>> >
>> > Craig
>>
>>
>>

Ken Halter · Joined: 04 Oct 2007 Posts: 4150

"Craigbob" wrote in message @microsoft.com...
>
> How can I search a drive for a particular folder name? I'm thinking
> VB.NEET
> is a better choice here due to the filesystem objects.
>
> Any ideas you can share?

Yeah... stop listening to the kool-aid ads Despite the marketting
hype,... and, maybe a "prettier" object model, dotNet has zero advantages
over VB6 when it comes to file I/O.

vbneet, eh? LOL! VBSluggish, maybe ... thanks... I needed a laugh.

Seriously though.... There's a bunch of samples here....

Searched PlanetSourceCode.com for "Excel automation"
http://www.planetsourcecode.com/vb/scripts/BrowseCategoryOrSearchResults.asp?lngWId=1&txtCriteria=Excel+automation&blnWorldDropDownUsed=TRUE&txtMaxNumberOfEntriesPerPage=10&blnResetAllVariables=TRUE&optSort=Alphabetical

--
Ken Halter - Please keep all discussions in the groups..
In Loving Memory - http://www.vbsight.com/Remembrance.htm