guide to auto import tables

[jerry28ph]

I would like to ask some guide on to automatic importing of table from a specific location of file. I would say \\3dif_avaya\jsmdr is the server path. I want to have a form to enter whether i want to create new table or save into existing table. Im thinking of DoCmd.TransferText but I really dont know where to start. Another confusing part is the Specification how am i going to create it.

Please need your advise.

 

[Alansidman]

Here is some code that I have used to import to a new table an excel file called gemcap.xls which is located on my desktop. I fire this code using a command button on a form. All data in this particular file is text.

Hope this helps you with your issues.

Private Sub Command1_Click()
'---------------------------------------------------------------------------------------
' Procedure : ImportXLSheetsAsTables
' Author    : jed
' Date      : 1/16/2009
' Purpose   : To import all worksheets in a specified workbook into
'             individual tables in Access.
' Tables get names: Tbl_ + name of the worksheet
'
' NOTE: Must have a reference to the Microsoft Excel Object Library
'---------------------------------------------------------------------------------------
'
Dim appExcel As Excel.Application
Dim wb As Excel.Workbook
Dim sh As Excel.Worksheet
Dim strValue As String
   On Error GoTo ImportXLSheetsAsTables_Error
Set appExcel = CreateObject("Excel.Application")
Set wb = appExcel.Workbooks.Open("C:\Documents and Settings\" & Environ("UserName") & "\Desktop\GEMCAP.xls")
For Each sh In wb.Sheets
Debug.Print sh.Name
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tbl_" & sh.Name, "C:\Documents and Settings\" & Environ("UserName") & "\Desktop\GEMCAP.xls", True, sh.Name & "!"
Next
wb.Close
appExcel.Quit
   MsgBox "SpreadSheets Imported.  Continue"
    
   On Error GoTo 0
   Exit Sub
ImportXLSheetsAsTables_Error:
    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure ImportXLSheetsAsTables of Module Module9"
End Sub

Alan

 

[NZArchie]

Is there a way to use VBA once you have opened the sheet to read the sheet and extract the relevant information? My csv file has a consistent format but a lot of gaps too so I don't think a straight import will work?