Pieter，Tom，
Thany you very much for your selfless help. I 'll try first, may come
here again when encounter problems. thx.
"(E-Mail Removed)" wrote:
> On Mar 22, 8:36 pm, Dawn <D...@discussions.microsoft.com> wrote:
> > Thx,Tom,it's a new way out of my box. But since I'm a green hand, can you put
> > out some coding for reference? Very thx
> >
> > "Tom van Stiphout" wrote:
> > > On Sun, 21 Mar 2010 20:13:01 -0700, Dawn
> > > <D...@discussions.microsoft.com> wrote:
> >
> > > You can iterate over the files in a folder using the Dir function.
> > > Then I would attach the file, run an append query to copy the data
> > > from Excel to Access, remove the attachment, and loop.
> >
> > > -Tom.
> > > Microsoft Access MVP
> >
> > > >Dear all,
> > > > The directory for saving files is determined while the number of files
> > > >in the directory every month is not certain, and the format of each file is
> > > >the same, the difference lies in the file names and the records in each files.
> > > > I want to write a Sub to read all records in each file in the directory
> > > >into an access table. How to achieve this? Thx for your support in advance.
> >
> > > .
>
> Private Sub cmdPopulateCombo_Click()
> Dim strDirectory As String
> Dim strFile As String
>
> ' Requires the BrowseFolder API from Access web... Here:
> strDirectory = BrowseFolder("Which folder contains the files you
> want?")
> strFile = Dir(strDirectory & "\*.XLS")
>
> Do While strFile <> ""
> 'NOTE: xlsEmpInfo is the name of my linked Excel spreadsheet
>
> '---Set link to the strFile (the filename returned by the
> Dir() function
> currentdb.TableDefs("xlsEmpInfo").Connect = "Excel
> 5.0;HDR=YES;IMEX=2;DATABASE=" & strFile
>
> '---run the append query to write the contents of the excel
> file to your table. (build the query first... manually attach an excel
> file)
> CurrentDb.Execute "qappMyAppendQuery", dbFailOnError
>
> '--- get the next file
> strFile = Dir
> Loop
>
> End Sub
>
>
> You will need the top two API calls, most likely... at least the
> BrowseFolder API (so you can choose the folder the Excel files are in)
>
>
> Private Sub ImportXLFiles()
> Dim strDirectory As String
> Dim strFile As String
> Dim i As Integer
>
> strDirectory = BrowseFolder("Which folder contains the files you
> want?")
> strFile = Dir(strDirectory & "\*.XLS")
>
> Do While strFile <> ""
> DBEngine(0)(0).TableDefs("MyAttachedExcelFile").Connect =
> "Excel 5.0;HDR=YES;IMEX=2;DATABASE=" & strFile
> Loop
> End Sub
>
> This code is probably buggy as all get out... I haven't tested it...
> Will fix it further later today if you need it.
>
> Pieter
> .
>
|
Similar Threads
