Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 141
  • Last Modified:

Find a database and run a query

I want to create a macro that search certain folder for mdb files. If it finds the file, the macro saves the path and name of the mdb and runs a query.
0
Aiysha
Asked:
Aiysha
  • 6
  • 6
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>I want to create a macro that search certain folder for mdb files. If it finds the file, the macro saves the path and name of the mdb and runs a query.

a certain filename or any .mdb file?

also, where should it save the name of the mdb file?
0
 
AiyshaAuthor Commented:
all mdbs in path "c:\program files\CompanyDatabase"..the results to be saved in xls file at "c:\"

Thank you .
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
can you be please a bit clearer of what query you speak of, and how you want the results to be saved into the .xls?
1 sheet per mdf found for example?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
AiyshaAuthor Commented:
no the resultin sheet should have

Colum1         colum2
Name of the database
0
 
AiyshaAuthor Commented:
sorry, for the post it was not complete the second column should have the path of the database.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
Thanks:

dim appExcel as object
set appExcel = CreateObject("Excel.Application")
dim objBook as object
set objBook = appExcel.Workbooks.Add
dim objSheet as object
set objSheet = objBook.Sheets(1)

dim fso as object
set fso = createobject("Scripting.FilesystemObject")
dim folder as object
set folder = fso.GetFolder("c:\program files\CompanyDatabase")
dim file as object
dim lngRow as long

for each file in folder.files
  if upper(right(file.name,3)) = "MDB" then
     lngRow = lngRow + 1
     objSheet.Cells(lngRow,1) = file.name
     objSheet.Cells(lngRow,2) = file.path
  end if
next

objBook.Save "C:\mdb.xls"
objBook.close
set objBook = nothing
appExcel.Quit
set appExcel = nothing

set file = nothing
set folder = nothing
set fso = nothing
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
small correction:
instead of objBook.Save it should be objBook.SaveAs
0
 
AiyshaAuthor Commented:
I should have mentioned this in my question, will the above code search the sub folders under CompanyDatabase?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>I should have mentioned this in my question, will the above code search the sub folders under CompanyDatabase?
no. you need a recursive function for that:

function RecursiveSearchFiles ( folder as object, objBook as object , byref lngRow as long )
dim file as object
dim sfolder as object

for each file in folder.files
  if upper(right(file.name,3)) = "MDB" then
     lngRow = lngRow + 1
     objSheet.Cells(lngRow,1) = file.name
     objSheet.Cells(lngRow,2) = file.path
  end if
next

for each sfolder in folder.subfolders
  RecursiveSearchFiles sfolder, objBook, lngRow
next

end function

and the working sub that you have to call:

Public Sub SearchAllMDB()
dim appExcel as object
set appExcel = CreateObject("Excel.Application")
dim objBook as object
set objBook = appExcel.Workbooks.Add
dim objSheet as object
set objSheet = objBook.Sheets(1)

dim fso as object
set fso = createobject("Scripting.FilesystemObject")
dim folder as object
set folder = fso.GetFolder("c:\program files\CompanyDatabase")
dim lngRow as long

RecursiveSearchFiles folder, objBook, lngRow

objBook.Save "C:\mdb.xls"
objBook.close
set objBook = nothing
appExcel.Quit
set appExcel = nothing

set file = nothing
set folder = nothing
set fso = nothing

End Sub
0
 
AiyshaAuthor Commented:
I get the following error on the line upper(right(file.name,3)) = "MDB" then

"sub or function not defined" is there a library I need to be referring to ?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:

I get the following error on the line ucase(right(file.name,3)) = "MDB" then
0
 
AiyshaAuthor Commented:
angelIII  convert the function into sub and use the call method..and also declare excel variables as public..it will work..
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 6
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now