Re: find files and open db

Tech-Archive recommends: Fix windows errors by optimizing your registry

I don't speak Access (that's the .mdb file, right), but Ron de Bruin shared
this:

Sub test()
Dim ac as object

Set ac = nothing
On Error Resume Next
Set ac = GetObject(, "Access.Application")
on Error goto 0

If ac Is Nothing Then
Set ac = GetObject("", "Access.Application")
end if
ac.OpenCurrentDatabase "C:\My Documents\db1_2K.mdb"
ac.UserControl = True
'AppActivate "Microsoft Access"
End Sub

As for getting the list of files from a folder, maybe this'll get you started.

Option Explicit
Sub testme()

Dim myNames() As String
Dim fCtr As Long
Dim myFile As String
Dim myPath As String
Dim myCell As Range
Dim myRng As Range

With Worksheets("sheet1")
Set myRng = .Range("a2", .Cells(.Rows.Count, "A").End(xlUp))

For Each myCell In myRng.Cells
myPath = myCell.Value
If Right(myPath, 1) <> "\" Then
myPath = myPath & "\"
End If

myFile = ""
On Error Resume Next
myFile = Dir(myPath & "*.xls")
On Error GoTo 0

If myFile = "" Then
myCell.Offset(0, 1).Value = "No files!"
Else
'get the list of files for that folder
'clean up existing names
Erase myNames
fCtr = 0
Do While myFile <> ""
fCtr = fCtr + 1
ReDim Preserve myNames(1 To fCtr)
myNames(fCtr) = myFile
myFile = Dir()
Loop

If fCtr > 0 Then
If fCtr > (.Columns.Count - 1) Then
'it won't fit, what should happen??
MsgBox "not enough columns!"
Else
myCell.Offset(0, 1).Resize(1, fCtr).Value = myNames
End If
End If
End If
Next myCell
End With
End Sub


Rivers wrote:

hi dave brilliant

my version of excell is 2003 the mdb is just to open it not link to it

the other with the list of file names in a list what im looking for is my
user to select a folder location from a list i provide and then on a
work*** the list of filenames in that folder appear on that row im looking
for the code that can view the folder and attatch the names of the files into
an array then reverse the array back into a work*** for the users to see.

"Dave Peterson" wrote:

#1. Do you mean you want to start your database program or import your data
from that database into an excel work***?

And I would think it would depend on what your database program is and even the
version of excel that you're using.

#2. Yep. But if you're creating a userform to open a file, you may want to
look at application.getopenfilename. It'll show the folders, but it's very easy
to implement.

Rivers wrote:

hi all
i have two questions
1 how do i open a database from inside excel
2 is it possible to populate a listbox with all available files from a
specified folder i.e all files in mydocuments but not including the folders.

thanks alot

this site has helped me no end of times

--

Dave Peterson


--

Dave Peterson
.

Quantcast