TransferSpreadsheet Question

[ietprofessional]

If I have an excel spreadsheet with multiple tabs and I want to transfer the data from each one of the tabs into a specific access table, how would I go about it. Is there a way of reading the value of the excel tab and putting the data in its corresponding access table?

I'm doing all my vba in Access.

Thanks!

 

[PHV]

Use named ranges.

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

 

[ietprofessional]

The problem with named ranges is that I would have to depend on a different department to create these ranges. Can't you specify the tab you would like to pull the data from in Access?

Thanks!

 

[PHV]

I don't have access at hand for the moment.
While in VBE (F11) open the Object Browser window (F2), search it for transferspreadsheet and then play with the F1 key.

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

 

[ietprofessional]

Yeah, I did this but didn't see an option for pulling from a specific tab. Is there a way of doing this without using TransferSpreadsheet and ranges?

Thanks!

 

[xlbo]

yup - you can use ADO / DAO and recordsets if your SQL is up to it

Rgds, Geoff

"Having been erased. the document thjat you are seeking. Must now be retyped"

Please read FAQ222-2244 before you ask a question

 

[ietprofessional]

How do I learn more about ADO and DAO?

Thanks!

 

[xlbo]

F1
type "ADO" or "DAO"

Google search for ADO / DAO

Rgds, Geoff

"Having been erased. the document thjat you are seeking. Must now be retyped"

Please read FAQ222-2244 before you ask a question

 

[4281967]

Make a form and place a command button on it. Add this to the "on click" event:

    DoCmd.TransferSpreadsheet acImport, 8, "tblImport", "C:\your-path-here\sample.xls", True, "Sheet1!A1:G99"
    DoCmd.TransferSpreadsheet acImport, 8, "tblImport", "C:\your-path-here\sample.xls", True, "Sheet2!A1:G99"
    DoCmd.TransferSpreadsheet acImport, 8, "tblImport", "C:\your-path-here\sample.xls", True, "Sheet3!A1:G99"

In this case, the spreadsheet (sample.xls) has data in three tab's (Sheet1 - Sheet3) and data in the first seven columns (A-G)
and data in rows that varies, but not more than 99 rows. By putting A1:G99 I got data from every col and row that had data. (of course you will need to change these values to accommodate your situation)

What this will do is bring in the data from all three tabs into one table (called tblImport) - although you are saying 99 rows (G99) if for example sheet one has 10 rows, and sheets 2 and 3 each have 20, tblImport will have a total of 50 records.

Hope that helps.

 

[vbap]

If I'm remembering correctly you can use Access to control Excel and visa versa.

Try something like this:

Sub tst()

' Declare variable to hold the reference.
Dim xlApp As Object

Set xlApp = CreateObject("excel.application")
' You may have to set Visible property to True
' if you want to see the application.
xlApp.Visible = True
' Use xlApp to access Microsoft Excel's
' other objects.
With xlApp
.workbooks.open "C:\tstworkbook.xls"
With .activeworkbook
For Each worksheet In .worksheets
Debug.Print worksheet.Name
'Enter code to transfer spreadsheet data here.
Next
End With
End With

'When you finish, use the quit method to close Excel
xlApp.Quit

'the application, then release the reference
Set xlApp = Nothing

End Sub


Anyway...not sure if it would lead you in a direction that might be useful. Good luck.

 

[comaboy]

You can import your data "manually". I prefer using this method because you can control each individual field, validate on the fly, etc...

The following needs code to be in a Module, and you also need to set References to DAO and Excel. It's (very) dirty, but it's commented (including the "bad" bits) and should get you going - it's pretty much a fire-and-forget one-time importer - anything that is used regularly needs to be *FAR* more robust!

Option Compare Database
    ' shouldn't use Gobals like this. Bad practise!
    Dim strMonth As String
    Dim wbk As Excel.Workbook
    Dim sht As Excel.Worksheet
    
Function Import_Costs_for_GMCL_SMEP_to_Aug_05()
    Dim lngFirstEntry As Long
    Set wbk = Excel.Workbooks.Open(Application.CurrentProject.Path & "\YOURWORKBOOK.xls", False) 'or use the full path..
  '  Set wbk = Excel.Workbooks.Open("C:\PATH\WHATEVER\YOURWORKBOOK.xls", False) 'Swap with above if necessary..
    
        ' For loop grabs ALL the sheets. If you know the "Tab Name"...
        ' Set sht = wbk.Worksheets("Tab Name")
    For Each sht In wbk.Worksheets
        sht.Activate
        sht.Visible = xlSheetVisible   ' not strictly necessary, but It lets us see what's happening
        GetStaticRowLoop
    Next sht
    wbk.Close
    Set wbk = Nothing  ' release wbk (Or we'll leak)
End Function

Sub GetStaticRowLoop()
    ' This is where the fun start.
    ' ENSURE You refernece DAO (Tools->References)
    Dim tblMain As DAO.Recordset
    Dim tblProject As DAO.Recordset
    Dim lngRow As Long

    Set tblMain = CurrentDb.OpenRecordset("MyTableName")        '    MYTABLENAME = Your Table Name
    ' I *KNOW* there are <= 5000 rows per sheet.
    ' An assumtion, and bad practise, but it'll be enough to get going...
    For lngRow = 12 To 5000  ' There are heading in rows 1-11
        ' Grab info
        With tblMain
            ' we should really check for valid data before we import
            ' but this IS quick 'n dirty.....
            ' import Cols A:C to named fields
            .AddNew 'Add a new blank record to our table
            .Fields("Name") = sht.Range("A" & lngRow).Text    'NOTICE Text and Value. This can be usefull for importing
            .Fields("Field2") = sht.Range("B" & lngRow).Value ' Dates, Percentages, etc ... Experiment!
            .Fields("Salary") = sht.Range("C" & lngRow).Value
            .Update     ' save changes to our record
        End With
    Next lngRow
    tblMain.Close
    Set tblMain = Nothing
End Sub