Open File Dialog Problems

[stallzy123]

Hi all,

I'm new to the forum and relatively new to access programming. I've got a real headache with opening file dialog to import a text file.

What I'm trying to achieve is to write code to import several text files where the suffix of the filename changes daily due to run dates/times, e.g. table_mapping_20130121_1500.csv.

Problem is I keep getting type mismatches even though the file I'm using to test the import is the same file I manually imported and created the import specification with.

Also, my debug print of the file dialog is creating an error too.

Any help would be greatly appreciated.

Here's the code I'm using (please disregard the function as I've not completely finished developing this as I've been stuck trying to get the import to work!)

Sub Import_files_Click()
Dim fd As Object
Dim strFilter As String
'Dim lngItems As Long
Dim table_array(20) As String        'An array containing the names of the tables with imported data
Dim counter As Integer              'Used as a counter in the For loop
 
 
 
If fProceed = True Then
 
 
 
table_array(1) = "asl_mapping"
table_array(2) = "collected_liabilities"
table_array(3) = "dynamic_fof"
table_array(4) = "dynamic_fund"
table_array(5) = "dynamic_previous_price_series"
table_array(6) = "dynamic_series"
table_array(7) = "efm_emx_data"
table_array(8) = "efm_fund_manager"
table_array(9) = "efm_static"
table_array(10) = "fof_holding_transactions"
table_array(11) = "fof_inflight_trades"
table_array(12) = "life_mapping"
table_array(13) = "reconciliation"
table_array(14) = "static_base"
table_array(15) = "static_box_rules"
table_array(16) = "static_calendar"
table_array(17) = "static_feeder_funds"
table_array(18) = "static_feeder_series"
table_array(19) = "static_fof"
table_array(20) = "static_series"
'table_array(21) = "HIPO1"
'table_array(22) = "FSVALU"
'table_array(23) = "HIPO3"
'table_array(24) = "HIPO4"
'table_array(25) = "HIPO5"
 
For counter = 1 To 20 'amend number here!
 
 
Const msoFileDialogOpen As Long = 3
Const msoFileDialogViewDetails As Long = 2
 
    Set fd = FileDialog(msoFileDialogOpen)
 
    With fd
        .AllowMultiSelect = False
        .InitialView = msoFileDialogViewDetails
        .InitialFileName = "X:\Data\Current"
        .Title = "Open " & table_array(counter) & " current data"
        .Filters.Clear
        .Filters.Add "All Files", "*.*"
        .Show
    End With
 
         Debug.Print fd.SelectedItem
 
        DoCmd.TransferText acImportDelim, "Import-" & table_array(counter), table_array(counter), fd.SelectedItem.Count, False
Next counter
MsgBox "Current data loaded", vbInformation
 
Else
MsgBox "Some files weren't empty or deleted. Please check", vbCritical
End If

Thanks,
S.

 

[pr2-eugin]

Hello S, Welcome to AWF..

I am not sure id the code is logically right.. Try setting the constants First.. Do not have to change them.. Set the object as FileDialog.. Think this might need a Microsoft Office Object library reference.. You can get the value only if the Shoe is True.. So try the following..

Sub Import_files_Click()
    Dim fd As FileDialog[COLOR=Red][B], showValue As Integer[/B][/COLOR]
    Dim strFilter As String
    'Dim lngItems As Long
    Dim table_array(20) As String        'An array containing the names of the tables with imported data
    Dim counter As Integer              'Used as a counter in the For loop
    
    Const msoFileDialogOpen As Long = 3
    Const msoFileDialogViewDetails As Long = 2

    If fProceed = True Then
        table_array(1) = "asl_mapping"
        table_array(2) = "collected_liabilities"
        table_array(3) = "dynamic_fof"
        table_array(4) = "dynamic_fund"
        table_array(5) = "dynamic_previous_price_series"
        table_array(6) = "dynamic_series"
        table_array(7) = "efm_emx_data"
        table_array(8) = "efm_fund_manager"
        table_array(9) = "efm_static"
        table_array(10) = "fof_holding_transactions"
        table_array(11) = "fof_inflight_trades"
        table_array(12) = "life_mapping"
        table_array(13) = "reconciliation"
        table_array(14) = "static_base"
        table_array(15) = "static_box_rules"
        table_array(16) = "static_calendar"
        table_array(17) = "static_feeder_funds"
        table_array(18) = "static_feeder_series"
        table_array(19) = "static_fof"
        table_array(20) = "static_series"
        'table_array(21) = "HIPO1"
        'table_array(22) = "FSVALU"
        'table_array(23) = "HIPO3"
        'table_array(24) = "HIPO4"
        'table_array(25) = "HIPO5"
     
        For counter = 1 To 20 'amend number here!
            Set fd = Application.FileDialog(msoFileDialogOpen)
            With fd
                .AllowMultiSelect = False
                .InitialView = msoFileDialogViewDetails
                .InitialFileName = "X:\Data\Current"
                .Title = "Open " & table_array(counter) & " current data"
                .Filters.Clear
                .Filters.Add "All Files", "*.*"
                [COLOR=Red][B]showValue = [/B][/COLOR].Show
            End With
            If [COLOR=Red][B]showValue[/B][/COLOR] Then
                Debug.Print fd.SelectedItem
                DoCmd.TransferText acImportDelim, "Import-" & table_array(counter), table_array(counter), fd.SelectedItem.Count, False
            End If
        Next counter
        MsgBox "Current data loaded", vbInformation
    Else
        MsgBox "Some files weren't empty or deleted. Please check", vbCritical
    End If
End Sub

What is that you are trying to do, in simple plain words..

 

[stallzy123]

Thanks, Paul.

I'll give that a try now.

What I'm trying to do is somewhat stated above. I'm trying to import text files into a DB on a regular basis but the names and folder locations change, so I thought it would be easier for the user to select the file to import. As you can see, there are 20 files to import to their corresponding table names on a regular basis, so automating this as much as possible is best, however, I've got some time constraints and I just need to find the fastest way to simplify this process other than manually importing these every few days. I'm happy with the user selecting files rather than completely automating the process, but I'm happy to take suggestions on how to fully automate! i was originally thinking I could use some kind of wildcard for the file names, but as stated, the folder directories change too depending on who is SFTP'ing these folders from source (which is out of my control).

 

[stallzy123]

hi Paul,

I've just tried the code and received Run time error '438': object doesn't support this property or method.

I've got Microsoft office 12.0 Access database engine Object library checked.

Any ideas as to why i'm getting this mismatch?

 

[pr2-eugin]

No not that reference.. There should be another Microsoft Office 12.0 Object Library, Which line does the error occur?

 

[stallzy123]

Got that one checked too

The error is received on the Debug.Print line. If I remove that line, the same message received on the import line DoCmd.TransferText...

 

[pr2-eugin]

Ahhh.. that's because, the method is SelectedItems, and it takes in an argument.. Try..

fd.SelectedItems(1)

 

[stallzy123]

Paul,

You're a superstar! It was the (1) that I needed!

I changed the SelectedItems to ..Item as I amended the code from AllowMultiSelect true to false, so thought items might be a letter too many as was trying different ways to use that method.

thanks so much for your help, you've made my day, mate!

 

[pr2-eugin]

Glad to have helped.. Good Luck !!

 

[stallzy123]

Only wish I could transfer some beers your way, mate!

thanks again.

 

[stallzy123]

One last question, Paul. Do I have to mark this thread as solved - if so, how?

 

[pr2-eugin]

It would help if you did mark it as solved..

 

[stallzy123]

So elated with a solution that I didn't even see it there right in front of me!

thanks again...

S.