running a loop with transfertext

[mike02]

Hey,



As i had posted about transfer spreadsheet before, and i was able get my form to properly import using that method. I have been trying to manipulate it in using it as a Loop. Now i have The same file names that i would be importing on a regular basis. The trick is that It would save me time to do it in a Loop, so i only have to do one step instead of repeating the step 7 times. Now, I have my form set up for cases, you select case 1, 2 or 3. i call a specified function. so for case 3 i developed this code:

Function txtImportstring()
On Error GoTo Import_Err
  
Dim strFilter As String
Dim strInputFileName As Variant
Dim strtblname As Variant
For Each strtblname In CurrentProject(Array("Address_Information", "Portfolio_Manager_Section", "Fund_Strategy_Section", "Risk_Management", "Service_Providers", "Investment_Desicion", "Supporting Documents"))
Dim strFolderName As String
Call GetFolder
For Each strInputFileName In CurrentProject(Array("Address Information.CSV", "Portfolio Manager_Section.CSV", "Fund Strategy Section.CSV", "Risk Management.CSV", "Service Providers,CSV", "Investment Desicion.CSV", "Supporting Documents.CSV"))
    
  If Len(strInputFileName) > 0 Then
    DoCmd.TransferText acImportDelim, actextTypeCSV12, strtblname, strInputFileName, True, ""
  End If

If Len(strInputFileName) > 0 Then
MsgBox "Imported strtblename"
End If

Next strInputFileName
Next strtblname

Import_Exit:
  Exit Function
  
Import_Err:
  MsgBox Error$
  Resume Import_Exit
End Function

Function GetFolder()
Dim SA As Object
Dim f As Object
Set SA = CreateObject("Shell.Application")
Set f = SA.BrowseForFolder(0, "choose a folder", 16 + 32 + 64, strStarDIR)
If (Not f Is Nothing) Then
  PickFolder = f.items.Item.path
End If
Set f = Nothing
Set SA = Nothing
End Function

Now the error i get is 'the object doesn't support this property or method'

Also, When i get the folder path, i do not know how to get the path to come in for strFileName to see that is the folder to get the files i have listed in the Array.

The objective is to click, my button, and then it prompts me to select the folder where the files are located. I have it set up as an array. Now when i do it individually meaning, i select the table from my combobox, then i prompt for file, and run the same transfer text code as above, it works great. But instead of doing it 7 times individually i want to do it all in one go, so i have been manipulating the code into what i currently have to do the loop. Thanks for the help in the advance,

[June7]

You can't just call the function, need to set a variable.

SourceFolder = GetFolder()

Then:

DoCmd.TransferText acImportDelim, actextTypeCSV12, strtblname, SourceFolder & "\" & strInputFileName, True, ""

However, your function is not returning anything. Change to:

Function GetFolder() As String
Dim SA As Object
Dim f As Object
Set SA = CreateObject("Shell.Application")
Set f = SA.BrowseForFolder(0, "choose a folder", 16 + 32 + 64, strStarDIR)
If (Not f Is Nothing) Then
  GetFolder = f.items.Item.path
End If
Set f = Nothing
Set SA = Nothing
End Function

Where does value for strStarDIR come from? This variable is not declared nor set nor value passed to the function by argument.

You have a comma instead of period in one of the array file names: Service Providers,CSV

Learn debug techniques from the link at bottom of my post.

[mike02]

ok thanks, ill take a look, I removed strStarDIR, as i forgot to fullyremove it, i removed its definition. sry, it slipped me sight, and as to the arrays and the for, next parts, are those set up correct, ive only used it once in excel before, I have never had this before, it doesnt run an error at all, it is just a message box, nodebug or anything, have you seen this before? thanks,

[June7]

The loop looks fine. Did you make suggested corrections?

If you have already run this and no errors thrown, are you seeing the correct results? Test and debug, repeat.

Have I seen what specifically?

[mike02]

hey, i made the corrections, read your debug article, and worked with it, but i just get a msg box like message from access, not error cannot run etc, its wierd how it works, ivenever seen it, the only option i have is ok, It just says, "the object doesn't support this property or method"

[June7]

Need to know what line causes the error.

[mike02]

For Each strtblname In CurrentProject(Array("Address_Information", "Portfolio_Manager_Section", "Fund_Strategy_Section", "Risk_Management", "Service_Providers", "Investment_Desicion", "Supporting Documents"))From where i have tested it, this is where it breaks.

[June7]

I've never seen that syntax for array. How did you develop that code? I assumed you had it from good example. Can you provide link to example?

[mike02]

that is probably the problem, i wrote it in excel with a spreadsheet i have, and i tried to see if it would work in access.

[June7]

Where in a spreadsheet would that code go? These articles present use of arrays in VBA as I have always understood
http://msdn.microsoft.com/en-us/library/wak0wfyt.aspx
http://www.cpearson.com/excel/vbaarrays.htm

[mike02]

i have it used going through a list of worksheets, like i have set it up as tables now. it would then copy the workheet into a new workbook and save it as a CSV file, and then close that new work book, it then goes onto the next ws in the array.
I thought i would be able to set it up similar with listing my tables, like i did with work sheets in the array, adn then match it up with anoter array of the CSV file name. thanks for the links i will take a look.

[mike02]

Im trying to understadn the concept of having one array, work off another. So with mine, i have just set it up the same, but since doesn't have the proper syntax and structure, i obviously need to re-write it. Now in saying so, is there a specified syntax which is necessary to to have my one array selecting the tables i need to append too, with having the other array match up with the selected file name?, thanks

Here is what it is in my excel spreadsheet

For Each ws In wbsrc.Sheets(Array("Address Information", "Portfolio Manager Section", "Fund Strategy Section", "Risk Management", "Service Providers", "Investment Desicion", "Supporting Documents"))

[mike02]

June,

I have re-wrote my arrays, Here is my current code,

Dim strFilter As String
Dim strInputFileName As Variant
Dim strtblname As Variant


strtblname = (Array("Address_Information", "Portfolio_Manager_Section", "Fund_Strategy_Section", "Risk_Management", "Service_Providers", "Investment_Desicion", "Supporting Documents"))
Dim strFolderName As Variant


SourceFolder = GetFolder()
strInputFileName = (Array("Address Information.CSV", "Portfolio Manager Section.CSV", "Fund Strategy Section.CSV", "Risk Management.CSV", "Service Providers.CSV", "Investment Desicion.CSV", "Supporting Documents.CSV"))
    
  If Len(strInputFileName) > 0 Then
    DoCmd.TransferText acImportDelim, actextTypeCSV12, strtblname, SourceFolder & "/" & strInputFileName, True, ""
  End If


If Len(strInputFileName) > 0 Then
MsgBox "Imported strtblename"
End If


Import_Exit:
  Exit Function
  
Import_Err:
  MsgBox Error$
  Resume Import_Exit
End Function


Function GetFolder()
Dim SA As Object
Dim f As Object
Set SA = CreateObject("Shell.Application")
Set f = SA.BrowseForFolder(0, "choose a folder", 16 + 32 + 64)
If (Not f Is Nothing) Then
    GetFolder = f.items.Item.path
End If
Set f = Nothing
Set SA = Nothing
End Function

It gives me a type mismatch. however it does stop at the same spot, I can select the folder, and then it runs the type mismatch. that is where it all goes wrong, now, I tried referencing both DAO and ADO like the microsoft error site told me too. howvere that didnt work. Any ideas to why this is happeneing thanks,

[June7]

Now I recall using the Array function in a procedure some time ago to populate an array variable. However, reading each element of array requires use of array index. Since you know there are 7 elements, the indices will be 0 to 6 because the default lower bound of an array is 0 (http://msdn.microsoft.com/en-us/libr...(v=VS.60).aspx). Use backslash.

For i = 0 to 6
DoCmd.TransferText acImportDelim, actextTypeCSV12, strtblname(i), SourceFolder & "\" & strInputFileName(i), True, ""
MsgBox "Imported " & strtblename(i)
Next

[mike02]

thanks for all your help june, I re wrote it with `With` Statement:

Function txtImportstring()
On Error GoTo Import_Err


Dim i As Long
Dim a As String
SourceFolder = GetFolder()
If SourceFolder > 0 Then
With Me.cbotablename
For i = 0 To .ListCount
a = i & ".CSV"
  If Len(strInputFileName) > 0 Then
    DoCmd.TransferText acImportDelim, actextTypeCSV12, i, SourceFolder & "/" & a, True, ""
  End If


If Len(strInputFileName) > 0 Then
MsgBox "Imported" & "i"
Else
MsgBox i & "Was Not Impoted"
End If
Next
    End With
Else
MsgBox "Import Cancelled"
End If
Import_Exit:
  Exit Function
Import_Err:
  MsgBox Error$
  Resume Import_Exit
End Function

Appreciate your help