Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2008
    Posts
    189

    Question Unanswered: Batch import XLS to Access Table

    Hello,
    As usual, I want to do something strange.

    I need to import some Excel 2003 files to an Access 2003 table.

    I know the Excel File names, but I don't know the Sheet names.
    I know that I need to import from sheet 2 to end.

    I was thinking about something like:

    The import function:
    Code:
    Option Compare Database
    
    Sub sImportarExcel(vNombreArchivo As String, vNombreHoja, vNombreTabla As String)
    'http://www.mvps.org/access/general/gen0008.htm
    DoCmd.TransferSpreadsheet transfertype:=acImport, SpreadsheetType:=5, _
            tablename:=vNombreTabla, FileName:=vNombreArchivo, _
            Hasfieldnames:=True, Range:=vNombreHoja
            'This will import the range B1 through B11
            'The Spreadsheet type = 5 specifies an Excel 5.0/7.0 file
            'format
    End Sub
    The function that goes inside the "FOR i=..."
    Code:
    Sub sTempImportar()
    Dim vNombreArchivo As String
    'Dim vNombreHoja As Range
    Dim vNombreTabla As String
    
    vNombreArchivo = "C:\XLS\File1.xls"
    vNombreHoja = Worksheets(1).Range("A:F") '"Hoja11!A:F"
    vNombreTabla = "tTemp"
    Call sImportarExcel(vNombreArchivo, vNombreHoja, vNombreTabla)
    End Sub
    Ok, my problem is on the Sheet name.
    I know that I should import the columns A to F. But the sheet name... is my issue.

    How can I set a "non text" sheet name so I can do a For each ws on Sheets?

    Any ideas?! Thank you!!

    Saludos,
    pepemosca

  2. #2
    Join Date
    Apr 2008
    Posts
    189

    Wink

    Ok, I did this... but is not a "great" solution.

    Code:
    Option Compare Database
    
    Sub sImportar()
    Dim vNombreArchivo As String
    Dim vNombreHoja As Variant
    Dim vNombreTabla As String
    
    Set rsArchivos = CurrentDb.OpenRecordset("SELECT * FROM [tArchivos a importar];")
        rsArchivos.MoveFirst
    
    'Recorro los elementos de la query
    Do While Not rsArchivos.EOF
        vNombreArchivo = Application.CurrentProject.Path & "\" & rsArchivos![Nombre de archivo]
        vNombreTabla = "tImportacionAutomatica"
        Call sImportarExcel(vNombreArchivo, vNombreTabla)
    Loop
    End Sub
    and...

    Code:
    Sub sImportarExcel(vNombreArchivo As String, vNombreTabla As String)
    'http://accessblog.net/2005/04/how-to-import-password-protected-excel.html
    'Abro el Excel
    Dim oExcel As Object, oWb As Object
    Set oExcel = CreateObject("Excel.Application")
    Set oWb = oExcel.Workbooks.Open(FileName:=vNombreArchivo)
    
    'Leo las hojas
    Dim Hojas(50) As String
    i = 0
    For Each Hoja In oWb.Sheets
       Hojas(i) = Hoja.Name
       i = i + 1
    Next Hoja
    
    'Cierro todo
    oWb.Close SaveChanges:=False
    oExcel.Quit
    Set oExcel = Nothing
    
    'Recorro las hojas
    For j = 1 To i - 1 'Salteo la primer hoja, pues tiene las definiciones
        'Nombre de la hoja
        vNombreHoja = Hojas(j) & "!A:Y"
        
        'http://www.mvps.org/access/general/gen0008.htm
        'Importo
        DoCmd.TransferSpreadsheet transfertype:=acImport, SpreadsheetType:=5, _
            tablename:=vNombreTabla, FileName:=vNombreArchivo, _
            Hasfieldnames:=False, Range:=vNombreHoja
    Next j
    End Sub
    Any better idea?
    Thanks!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •