[AccessD] Hide datasheet view of table when appending

Jim,

    If you are in a position to name the data range on each excel sheet in style R_01, R_02 etc., sample subroutine named P_ImportFromExcel(), as given below, should enable you to import the data from all relevant sheets into a common access table named T_Import.

    It is presumed that first row of excel data range consists of column headings. While creating the table T_Import, it should be ensured that all column headings existing in excel are included in field names, with correct data type. Ordinal position of fields visa vis sequence of excel columns is not important. If desired, access table can even have extra fields (over & above the number of columns in excel data range).

Best wishes,
A.D.Tejpal
------------

' Sample subroutine (P_ImportFromExcel())
' For importing data from multiple sheets in excel
' to table named T_Import
' It is to be ensured that column headings in first row of each
' data range are covered by field names in access table
' (The number and sequence of columns in excel need not 
' necessarily match the number & sequence of fields in 
' access table)
'=======================================
Sub P_ImportFromExcel(ExcelFilePath As String, _
                        NumberOfSheetRanges As Long)
    Dim Cnt As Long
    
    For Cnt = 1 To NumberOfSheetRanges
        DoCmd.TransferSpreadsheet acImport, , _
                    "T_Import", ExcelFilePath, True, _
                    "R_" & Format(Cnt, "00")
    Next
End Sub
'=====================================


  ----- Original Message ----- 
  From: Hewson, Jim 
  To: Access Developers discussion and problem solving 
  Sent: Friday, June 13, 2008 02:17
  Subject: [AccessD] Hide datasheet view of table when appending


  I can't seem to figure this out....  
  Access 2003
  I am importing data from an Excel workbook to a table in Access.
  All the worksheets will be imported and they have the same format.
  The Excel files are created from a download from another system. 
  The smallest workbook has 80 worksheets.  One test Excel file has about
  2400 worksheets.
   
  I am able to work on each worksheet, grab the desired range (could be 1
  or 100 rows involved), paste it into Access, and close Excel.
  I need to "hide" the table when the data is being appended.
  What happens is the table is opened and the data is pasted into it -
  everything shows in the database window.
   
  How can I hid the table and still paste the data into it?
  If I minimize it, I get an error message.
  If I hide the table, I get an error message.
   
  Any suggestions?
   
  Thanks,
   
  Jim
   
  Jim H. Hewson