Need help with using access to open outside Excel files

  • Thread starter Thread starter BTU_needs_assistance_43
  • Start date Start date
B

BTU_needs_assistance_43

I'm still new to VB and Access but I'm trying to make a database that will
automatically update anytime a new Excel report is filed on the network. I
already have the program written to the point that it searches for and puts
into cells the full file name/locations of recently modified Excel files in
the specific folders I want. My Access database then imports those file names
into a table and displays those names in a list box on a form. What i need to
do now, is make Access take the file names its given and go into them and
extract certain cells from the Excel reports on the network and bring them
into another table in Access and store that information in the database.

That certainly is where I need help, but also if anyone could tell me how to
write code into my Excel VB program so that it will update its list of
recently added files automatically without me having to open it and refresh
it every day. This below is the code I have so far... Thanks for your help I
really need it!

Private Sub FileSearch_Click()

With Application.FileSearch
.NewSearch
.LookIn = "X:\File Folder\Sub Folder"
.SearchSubFolders = True
.LastModified = msoLastModifiedThisWeek
.FileType = msoFileTypeExcelWorkbooks
If .Execute > 0 Then
Worksheets("sheet1").Activate
Worksheets("sheet1").Range("A2").Select
For i = 1 To .FoundFiles.Count
ActiveCell.Value = .FoundFiles(i)
ActiveCell.Offset(rowOffset:=1, columnOffset:=0).Activate
Next i
Else
End If
End With

End Sub
 
Hi,

don't have time to look into the excel code for now, but here you have a
snip of example code how to handle excel files from within access::

----------------------------------------------------
Public Function Import(strFileName as string) As Boolean
On Error GoTo Err_Import

Dim appExcel As Excel.Application
Dim bksBooks As Excel.Workbooks
Dim wkbBook As Excel.Workbook
Dim wksSheet As Excel.Worksheet
................... other declarations
...........................................
Dim rstConts As New ADODB.Recordset
Dim cnn As New ADODB.Connection

'Initiate all objects and variables
Set appExcel = GetObject(, "Excel.Application")
Set bksBooks = appExcel.Workbooks
Set wkbBook = bksBooks.Open(strFilename)
Set wksSheet = wkbBook.Sheets(1)
wksSheet.Activate


Set cnn = CurrentProject.Connection
'check for a valid excel file
lngNrCats = Nz(appExcel.Range("IV2"), 0)
lngC = 0
While lngC <= lngNrCats - 1
strImpCat = Nz(appExcel.Range("CATS").Offset(-1, lngOffset).Value, "")
If strImpCat = "Import" Then

……… importcode …………………………


End if
lngC= lngC + 1
wend
wkbBook.Close
Import = True


Exit_ImportTournament:
Exit Function

Err_ImportTournament:
If Err.Number = 429 Then 'excel is not running
Set appExcel = CreateObject("excel.application")
Resume Next
Else
Import = False
Call myErrorHandling
End If
Resume Exit_Import


End Function
 
Back
Top