TransferSpreadsheet

[haveacup]

new to the access area...hoping to find a bit of help..I've been importing from excel 2 access...sheet by sheet...so I wanted to see if this was possible????

When creating the transferspreadsheet macro (to get from excel to db), can wildcards be used?? like in the filename can I specify the path / file name and rt " & x & ".xls"

to transfer all workbooks (single worksheet) w/ in a folder at once..or do they have to be imported / transferred individually???

 

[Darrylles]

Hi have,

You'll have to specify sheet names specifically.

However, you may be able to use vba to interrogate the Excel file to get all worksheet names into a list, and then use that to step through each sheet programmatically.

I doubt that this can be done via macro (and I wouldn't want to try in any case).

Try starting another thread in 'Access Modules (VBA Coding))'.

ATB

Darrylle








Never argue with an idiot, he'll bring you down to his level - then beat you with experience.

 

[Remou]

Here is a sketch:

Dim objXLS As Object
Dim wks As Excel.Worksheet

On Error GoTo ErrorHere
strFile = "C:\Docs\Tek-Tips.xls"

Set objXLS = GetObject(, "Excel.Application")
objXLS.Visible = True
objXLS.Workbooks.Open strFile
        
For Each wks In objXLS.Worksheets
    'Debug.Print wks.Name
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, wks.Name, strFile, True, wks.Name & "!"
Next

ExitHere:
Exit Sub

ErrorHere:
If Err.Number = 429 Then
    Set objXLS = CreateObject("Excel.Application")
    Err.Clear
    Resume Next
Else
    MsgBox Err.Number & "  " & Err.Description
End If

 

[Darrylles]

Hi,

Haveacup - give remou a star.

ATB

Darrylle

Never argue with an idiot, he'll bring you down to his level - then beat you with experience.

 

[PHV]

Have a look at the Dir function.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[haveacup]

Thanks a bunch for all the help...I've tried a few of the suggestions, and one that I worked on my own..and they all provide the basically the result I was looking for....