Pull specific cells from multiple Excel wb to Access table

[smithn293]

Hello,

I am trying to set up the following automated process triggered by a button in a Form:

1) Let me select a folder where multiple excel files (xls) are located
2) For each file, select specific cells (hard-coded in the VBA) to create a single record that will be added to a single Access table (each file adds a new record to the same Access table)
3) Copy all files to a subfolder named with the current date (format: YYYY-MM-DD) of the folder I originally selected
4) Add a "-x" to the end of the filenames (i.e. File "myfile.xls" becomes "myfile-x.xls)

I have a macro in Excel that does this, but I can't get it to work as Access-triggered code.

Any help is greatly appreciated.

Thanks!

 

[xenou]

If the macro is already written in Excel, simplest is to just open the excel workbook from access and run it as is.

Here's sample code to run an Excel macro from Access:

[COLOR="Navy"]Sub[/COLOR] Bar()
[COLOR="SeaGreen"]'//Opens an Excel workbook from Access and runs a subroutine in the XL Workbook[/COLOR]
[COLOR="SeaGreen"]'//This is a sub in an Access standard module.[/COLOR]
[COLOR="Navy"]Dim[/COLOR] XL [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Object[/COLOR]
[COLOR="Navy"]Dim[/COLOR] wb [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Object[/COLOR]
[COLOR="SeaGreen"]'----------------------------------------------------------------------------------------------[/COLOR]
[COLOR="SeaGreen"]'//Change these constants as needed[/COLOR]
[COLOR="Navy"]Const[/COLOR] wbPath [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR] = "C:\myTemp\Book1.xls" [COLOR="SeaGreen"]'//Full path to workbook with macro[/COLOR]
[COLOR="Navy"]Const[/COLOR] wbName [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR] = "Book1.xls" [COLOR="SeaGreen"]'//Name of workbook with macro, including file extension[/COLOR]
[COLOR="Navy"]Const[/COLOR] ProcedureName [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR] = "Foo" [COLOR="SeaGreen"]'//Excel macro name (public procedure in standard module)[/COLOR]
[COLOR="SeaGreen"]'----------------------------------------------------------------------------------------------[/COLOR]

[COLOR="Navy"]Set[/COLOR] XL = CreateObject("Excel.Application")
[COLOR="Navy"]With[/COLOR] XL
    .Visible = True
    [COLOR="Navy"]Set[/COLOR] wb = .Workbooks.Open("C:\myTemp\Book1.xls")
    [COLOR="Navy"]Call[/COLOR] XL.Application.Run(wbName & "!" & ProcedureName)
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]

My_Exit:
[COLOR="Navy"]If[/COLOR] [COLOR="Navy"]Not[/COLOR] wb [COLOR="Navy"]Is[/COLOR] [COLOR="Navy"]Nothing[/COLOR] [COLOR="Navy"]Then[/COLOR]
    wb.Close False
    [COLOR="Navy"]Set[/COLOR] wb = [COLOR="Navy"]Nothing[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
[COLOR="Navy"]If[/COLOR] [COLOR="Navy"]Not[/COLOR] XL [COLOR="Navy"]Is[/COLOR] [COLOR="Navy"]Nothing[/COLOR] [COLOR="Navy"]Then[/COLOR]
    XL.Quit
    [COLOR="Navy"]Set[/COLOR] XL = [COLOR="Navy"]Nothing[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
[COLOR="Navy"]Exit[/COLOR] [COLOR="Navy"]Sub[/COLOR]

ErrHandler:
MsgBox "Error: " & Err.Description
[COLOR="Navy"]GoTo[/COLOR] My_Exit

[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

 

[smithn293]

Sorry, my post was a bit mis-stated. I have a macro that will compile a list into a new workbook, but I don't have any logic that will save the files as I noted or pull the compiled list into Access.

Any examples of how to do those 2 things would be great.

Thanks.

 

[xenou]

Start first with one workbook only. Then generalize it so it will work on multiple workbooks.