Import CVS :Prompt the user to select the file

[mfaqueiroz]

Hi everyone

I want to do one vba module that prompt the user to select the CSV file that i want to extract to one table in my DataBase.

I'm trying to write this code, but it only works with xls files...could you please help me to understand what i'm doing wrong..?

MODULE:


Function selectFile()
Dim fd As FileDialog, FileName As String

On Error GoTo ErrorHandler


Set fd = Application.FileDialog(msoFileDialogFilePicker)

fd.AllowMultiSelect = False

If fd.Show = True Then
If fd.SelectedItems(1) <> vbNullString Then
FileName = fd.SelectedItems(1)
End If
Else


End
End If


selectFile = FileName

Set fd = Nothing

Exit Function

ErrorHandler:
Set fd = Nothing
MsgBox "Error " & Err & ": " & Error(Err)

End Function

FORM

Private Sub cmdImport_Click()

'---------TAB 1

'Unset warnings
DoCmd.SetWarnings False

'SQL apaga
DoCmd.RunSQL "DELETE * FROM Tab1"

MsgBox "Select Tab1"
'Import
oApp.DoCmd.TransferText acImportDelim, , "Tab1", selectFile, False

'Confirmação
MsgBox "Os dados da Tabela de Instalações foram importados com sucesso"

DoCmd.SetWarnings True

End Sub


I really appreciate your help

!

 

[The_Doc_Man]

but it only works with xls files

Can you be more specific about what it says when it DOESN'T work? Or in what way you believe that it doesn't work? Without knowing a bit more, I'm guessing. Below is my best guess in the absence of more information.

As a "gut" reaction, I would suspect that the problem is in the setup of your fd object. You might want to look at fd properties such as .InitialFileName and at adding filter names using the fd.Filters.Add method to specify what names you would accept. Rather than trying to explain the exact syntax of this method, I suggest that you simply consult the Google brain for Office FileSystemObject Filters. Then you could provide an input parameter to your call such that you could specify what class of files you really wanted to see out of this call.

 

[Ranman256]

Private Sub cmdImport_Click()
 '---------TAB 1
 'Unset warnings
DoCmd.SetWarnings False
 'SQL apaga
DoCmd.RunSQL "DELETE * FROM Tab1"
 selectFile  = UserPick1File("c:\folder\")
 if SelectFile <> "" then
   MsgBox "Select Tab1"
 'Import
  DoCmd.TransferText acImportDelim, , "Tab1", selectFile, False
  'Confirmação
  MsgBox "Os dados da Tabela de Instalações foram importados com sucesso"
 DoCmd.SetWarnings True
endif
End Sub
  
 Public Function UserPick1File(Optional pvPath)
Dim strTable As String
Dim strFilePath As String
Dim sDialog As String, sDecr  As String, sExt As String
 '===================
'YOU MUST ADD REFERENCE : Microsoft Office xx.0 Object Library, in vbe menu, TOOLS, REFERENCES
'===================
 With Application.FileDialog(msoFileDialogFilePicker)   
    .AllowMultiSelect = False
    .Title = "Locate a file to Import"
    .ButtonName = "Import"
    .Filters.Clear
    .Filters.Add "CSV Files", "*.CSV"
    .Filters.Add "All Files", "*.*"
    .InitialFileName = "c:\"
    .InitialView = msoFileDialogViewList    'msoFileDialogViewThumbnail
    
        If .Show = 0 Then
           'There is a problem
           Exit Function
        End If
    
    'Save the first file selected
    UserPick1File = Trim(.SelectedItems(1))
End With
End Function

 

[mfaqueiroz]

Thank you !

I've wrote this code, I have my "Tab1" in access with exactly the same columns as the Tab1 That i want to import, when i run the code it stops at DoCmd.TransferText acImportDelim, , "Tab1", selectFile, False and appears always this error:

Run-time error 2391. Field F1 doesn't exist in destination table "tab1",

where my F1 is the field "Code" that is wrote in the exact way in the acces and in my import table...do you know how can i solve that?
Thank you!!

 

[arnelgp]

so the column you want to import is F1 and corresponds to column Code in your tab1 table.

 

[mfaqueiroz]

I want to import all columns but the error only refers one problem on F1

 

[arnelgp]

you will need "cleaning/disinfecting" your csv file first, and get rid of column F1 or we have to write code to import only the fields present on your table.