Using VBA to load a series of files in one go 3

[Ramy27]

Hi everyone,
I have a rather tricky task to accomplish using VBA. Any suggestions will be welcome.

I have the folder (G;\Timesheets) that contains sub folders/ fileslike this:

G:\Timesheets\George Smith\Timesheets_2005.xls
G:\Timesheets\Lesley Grant\Timesheets_2005.xls
G:\Timesheets\Pat Anderson\Timesheets_2005.xls
……..
……..
……..
……..

The program should:
1) go to the folder G:\Timesheets
2) extract the list of the names (eg. George Smith)
3) Then read all these timesheets into a database (I have code for this part)
4) The tricky thing is that the folder G:\Timesheets has other files in it as well. But all the folders are named after people.








---------------------------------
Your help is much appreciated, R.
---------------------------------

 

[BrainB]

I would pick up the employee name from a list somewhere. If you really want a list of folders I suggest you do a search for 'FileSystemObject'.

This should give you the 'bones'

Sub test()
    Dim BaseFolder As String
    Dim MyName As String
    Dim FileName As String
    Dim FullFileName As String
    '---------------------------
    '- make file name
    BaseFolder = "G:\Timesheets\"
    MyName = "George Smith"
    FileName = "Timesheets_2005.xls"
    '- open workbook
    FullFileName = BaseFolder & MyName & "\" & FileName
    Workbooks.Open FileName:=FullFileName
End Sub

Regards
BrianB
Use CupOfCoffee to speed up all windows applications.
It is easy until you know how.
================================

 

[Ramy27]

Thanks. That will get me started.

---------------------------------
Your help is much appreciated, R.
---------------------------------

 

[Ramy27]

Are file names case sensitive in VBA?

---------------------------------
Your help is much appreciated, R.
---------------------------------

 

[TonyJollans]

Hi Ramanesh,

VBA doesn't do anything with the case - it passes the names as is to the operating system which is hosting the files so, assuming that's Windows, you shouldn't have any problems.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[Ramy27]

Sorry, i asked teh wrong question.

The question should have been:
"Is the opertaing system (Windows 2000) case sensitive"?

If I have a folder called
G:\Timesheets\John Smith\

and I call it
G:\timeSheets\john SMITH\
in VBA code by mistake, would it still find the right folder?

---------------------------------
Your help is much appreciated, R.
---------------------------------

 

[PHV]

With any FileSystem method, it should.
If with string comparaison, it shouldn't unless you use LCase(or UCase) on the 2 sides.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[TonyJollans]

Hi Ramanesh,

I don't have a copy loaded at present to double check but, AFAIK, file names are not case sensitive in any version of Windows (but they are in Unix based systems). You should have no trouble but why not check it out - it's surely easier than posting (or don't you have access)?

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[Ramy27]

At the time of posting this message, i didn't have access. But I have managed to check it out. It is not case sensitive. Thanks very much.

---------------------------------
Your help is much appreciated, R.
---------------------------------