Excel Import Question

[colmtourque]

I have seen several hints about how to do this, but I have not found a good answer. Sorry my vna coding sucks, do web and some VB but little vba.

I am trying to import an excel spreedsheet that has blanks in some rows as well as a lot of pretty formating, such as some lines are centered etc.... I tried using:

Private Sub Command0_Click()
DoCmd.TransferSpreadsheet acImport, 8, "Table1", "M:\teststuff\mtd.XLS", True, "Sheet1!b2:q48"
End Sub

But it appears that due to all the formatting it can't read the column headings properly.

Is there any advice on a way to do this.

To be a little more specefic I am actually trying to import only certain columns and in one column only certain data (theres a name and id number and I just want the number).

 

[colmtourque]

Found Something else

As a note I also found this
Option Compare Database
Option Explicit
Const ExcelImp = "Import.xls"

'------------------------------------------------------------------------------
' Procedure: subImport
' Author: Dave Swanton
' Purpose: Read in new data from an excel file
' Dependencies: None
' Parameters: None
'-------------------------------------------------------------------------------

' Notes
' Assumptions: 1) The filename to read is in the Const ExcelFile above
' 2) The file will be in the current database path
' 3) The file will be in the specified/agreed format

Public Sub subImport()
On Error GoTo Err_subImport

Dim stDocName As String
Dim fs As FileSearch
Dim ifn As String
Dim sql As String
Dim today As String
Dim fso As Scripting.FileSystemObject
Dim oktogo As Boolean
Dim specname As String
Dim repdate As String
Dim myfile As Scripting.TextStream
Dim i As Long
Dim y As Integer

DoCmd.SetWarnings False
oktogo = False


'Find the file for import


ifn = CurrentProject.Path & "\" & ExcelImp 'Path of Import.xls file

Set fso = CreateObject("Scripting.FileSystemObject")
If fso.FileExists(ifn) Then
oktogo = True
Else
MsgBox "Please ensure that the source file is present and try again" & vbCr _
& "Required file and location: " & vbCr & ifn, vbExclamation + vbOKOnly, "Input File Missing"
End If

If oktogo Then




sql = "SELECT tbl_import.* INTO tbl_temp_import " _
& "FROM tbl_import "



DoCmd.RunSQL sql 'Create a temp table based on the structure of the main table to import into to check consistency

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tbl_temp_import", ifn, True 'Import file into table


sql = "INSERT INTO tbl_import " _
& "SELECT tbl_temp_import.* " _
& "FROM tbl_temp_IntellectImport "


DoCmd.RunSQL sql 'Insert into main table




subArchiveImport ifn 'Archive the report

'MsgBox "Import Complete.", vbInformation + vbOKOnly + vbApplicationModal 'Info message





End If

Exit_subImport:
' Turn warning messages back on
DoCmd.SetWarnings True
Exit Sub

Err_subImport:
MsgBox Err.Description
Resume Exit_subImport

End Sub


'------------------------------------------------------------------------------
' Procedure: subArchiveIntell
' Author: Dave Swanton
' Purpose: Archive Intellect Import into Archived Intellect Folder
' Dependencies: None
' Parameters: None
'-------------------------------------------------------------------------------


Public Sub subArchiveImport(src As String)

On Error GoTo Err_subArchiveImport
Dim dest As String
Dim today As String

today = Format(Date, "dd-mm-yyyy")
dest = Left(src, InStrRev(src, "\")) & "Archived Imports\" & today & " - " & Right(src, Len(src) - InStrRev(src, "\"))
Name src As dest

Exit_subArchiveImport:
Exit Sub

Err_subArchiveImport:
MsgBox Err.Description
Resume Exit_subArchiveImport

End Sub

But he says to reference the MS OFfice objects and scripting libraries, can't find them to refenence. Any idea's? I'd like to see if this will work.