Sample Excel 2003 VBA Program

[mmignot]

Hello all,

Does anyone know where I can find a sample Excel 2003 VBA macro that will read one or more excel files from a specific folder location, retrieve data from certain cells of each excel file, and write the data one row at a time to a new excel file?

Many thanks
MM

 

[SkipVought]

hi,

Tha is a very specific requirement. Doubt that anyone has code lying around that does exactly that.

You can use the FileSystemObject (search for FOLDERS in VB Help) to access files in a folder.

Do you have any code that you are trying to use? Please post where you are having problesm.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[mmignot]

Thanks for the reply Skip,

No, I haven't started coding yet, I just know what I'd like to do. I've done something very similar in the past, where I've read data from a SQL server table and wrote the data out into individual sheets in a workbook, then saved the workbook to a designated folder location. It was quite awhile ago that I wrote this (2004), and I'm not able to find my original code. I haven't used VBA since, so I'm going to be a little rusty at this. Bottom line though, is if I can just get a small sample VBA macro that just reads cells from a sheet in a workbook, I can figure our the rest. I just need a starter program.

Thanks for any help you can provide me!!
MM

 

[SkipVought]

With Workbooks.Open(FileName)
   debug.print Sheets(1).Cells(1,1).Value
   .close
End with

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[fumei]

I can just get a small sample VBA macro that just reads cells from a sheet in a workbook"



Well Skip, you can't get much smaller than that.


unknown

 

[SkipVought]

I was never tall.

But are you saying, "Skip, that was small of you?" ;-)

I might Stu in that one!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[fumei]

Anyway you like it buddy.


unknown

 

[xlbo]

Here's some sample stuff for what you are looking at doing:
requires a reference to MS Scripting Runtime in Tools>References

Sub ListFilesInFolder(SourceFolderName As String)

Dim FSO As Scripting.FileSystemObject

Dim Fldr As Scripting.Folder

Dim theFile As Scripting.File

Dim wb As Workbook

   
    Set FSO = New Scripting.FileSystemObject
    
    Set Fldr = FSO.GetFolder(SourceFolderName)
        
    fCount = Fldr.Files.Count
        
    fc = 1
        
    For Each theFile In Fldr.Files
        
        Application.StatusBar = "Processing File " & fc & " of " & fCount
        
               
        Set wb = Workbooks.Open(SourceFolderName & theFile.Name)
        
        With wb.Sheets("SheetName")
            
             messagebox .cells(1,1).value

        End With

        Set wb = nothing
    
    Next

    Set Fldr = nothing

    Set FSO = nothing

End Sub

Call it with:

Sub runner()
ListFilesInFolder ("\\Full\Folder\Path\")
End Sub

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

Ahhh, the word from Down Under.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[xlbo]

the word from down under today is "WET!!"

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question