Import .csv with different filenames using a macro

[kdibricida]

I have a database that i need to import a .csv file into every day. The .csv file is a different name every day. Does anyone know how to have a macro prompt for the file to be imported? Or does anyone have a better idea on how to do this?

 

[CautionMP]

You can use an API call to show the user the standard windows open dialog box. You would use it like:

strFileToImport = GetCSVLocation
If Len(strFileToImport) > 0 Then
  'Import your file here
Else
  'The user did not select a filename
End If

And here is the API (copy into a new or existing module):

Private Declare Function GetOpenFileName Lib "comdlg32.dll" Alias _
"GetOpenFileNameA" (pOpenfilename As OPENFILENAME) As Long

Private Type OPENFILENAME
    lStructSize As Long
    hwndOwner As Long
    hInstance As Long
    lpstrFilter As String
    lpstrCustomFilter As String
    nMaxCustFilter As Long
    nFilterIndex As Long
    lpstrFile As String
    nMaxFile As Long
    lpstrFileTitle As String
    nMaxFileTitle As Long
    lpstrInitialDir As String
    lpstrTitle As String
    flags As Long
    nFileOffset As Integer
    nFileExtension As Integer
    lpstrDefExt As String
    lCustData As Long
    lpfnHook As Long
    lpTemplateName As String
End Type

Function GetCSVLocation(Optional DefaultDirectory As String = "C:\") As String
    Dim OpenFile As OPENFILENAME
    Dim lReturn As Long
    Dim sFilter As String
    
    sFilter = "Comma Seperated Value (*.csv)" & Chr(0) & "*.csv"
    With OpenFile
      .lStructSize = Len(OpenFile)
      .hwndOwner = Application.hWndAccessApp
      .lpstrFilter = sFilter
      .nFilterIndex = 1
      .lpstrFile = String(257, 0)
      .nMaxFile = Len(OpenFile.lpstrFile) - 1
      .lpstrFileTitle = OpenFile.lpstrFile
      .nMaxFileTitle = OpenFile.nMaxFile
      .lpstrInitialDir = DefaultDirectory
      .lpstrTitle = "Select a file to import"
      .flags = 0
    End With
    lReturn = GetOpenFileName(OpenFile)
    If lReturn = 0 Then
      MsgBox "A file was not selected!", vbInformation, "Import Error"
    Else
      GetCSVLocation = Trim(OpenFile.lpstrFile)
    End If
End Function

Hope this helps,
CMP

(GMT-07:00) Mountain Time (US & Canada)

 

[kdibricida]

This is new to me and i am not following you at all

 

[DrSimon]

CautionMP's reply is a purist one that should work, but I can understand that you have a problem understanding it. If you want to use a macro, you can, but to give it variable input you will need some code to go with it.
To enter the code, create a new module object and insert the code below into it:

 Function ImportFileName() As String
Dim FileStr As String
ImportFileName = InputBox("Enter File Name")
End Function

Save that module and use the default name

Now create a macro with the Action of TransferText using the values below:
TransferType: ImportDelimited
Table Name : <whatever table you want to import to>
File Name : =ImportFileName()
N.B. the '=' and the '()'
Has Field Names: <depends on your import file>

This assumes that you want to add to the same table each day. If you want to import to a different table, create another function called say 'TableName' which is all but identical to the code above. and use that function as the entry to the Table Name field.

You can now run that macro and it will prompt you to enter the csv file name every time you run it.
Simon Rouse

 

[kdibricida]

Thank you this worked for me. Is there anyway to have a browse box pop-up instead of an input box?

 

[DrSimon]

Yes ...use CautionMP's reply !!!!
If you've managed to do what I suggested, you can use his code. Delete my function and insert the API code above. Now insert the new function below underneath which calls CautionMP's API.

 Function ImportFileName() As String
Strfiletoimport = GetCSVLocation
If Len(Strfiletoimport) > 0 Then
  ImportFileName = Strfiletoimport
Else
  MsgBox "The user did not select a filename"
End If
End Function

 

[CautionMP]

The API call is pretty ugly, but the beauty is once you add it to a project you never have to look at it again, just use it.

If you are using Office 2002 or newer you could also look at the [tt]FileDialog[/tt] class. Microsoft seemed to notice the need for common dialogs and added the functionality across the Office suite. I don't have much experience with them (I still work primarily in Office 2k) but they are purported to be easier to use than the API's.

Hope this helps,
CMP

(GMT-07:00) Mountain Time (US & Canada)

 

[kdibricida]

Thanks again. That works for me. But I have just one more question. Not a real big deal, but is there a way to have the browse box default to a certain path on the network?

 

[CautionMP]

GetCSVLocation([b]Optional DefaultDirectory As String = "C:\"[/b])

You can change the [tt]"C:\"[/tt], or you can feed the new default directory when you call the function [tt]GetCSVLocation("n:\YourDirectory")[/tt]

CMP

(GMT-07:00) Mountain Time (US & Canada)