0

I run into an compilation error "Expected procedure, not variable" where i can not find the issue. Can someone help me with finding/pointing the issue? Setup is to get a file from a FTP place and then read it into a access database. Code is based on https://www.excel-easy.com/vba/examples/read-data-from-text-file.html The transfertext with a delimit design option is not possible because it somehow locks up. The file itself is small.

There is no reference added yet (like scripting or other libraries), in case that is important.

code below:

Public Function ImportCSVFile()
    Dim strTextLine As String
    Dim aryMyData() As String
    Dim strSQL As String
    
    Open "C:\Users\Electronica\Documents\3000.csv" For Input As #1
    Do While Not EOF(1)             ' Loop until end of file.
        Line Input #1, strTextLine   ' Read line into variable.
        aryMyData = Split(strTextLine, ",") 'Split text into array by comma
        Debug.Print aryMyData
        If aryMyData(0) = "" Then
            strSQL = "INSERT INTO Tbl_Theo_Stock([Delivery Note], [Delivery Date], [Delivered QTY], [LABELNR], [SAP PO], [SAP POS], [OPEN QTY], [Wished Date], [Materialnr], [reference], [description], [vendor]) "
            strSQL = strSQL & " VALUES(" & aryMyData(0) & ", " & aryMyData(1) & ", " & aryMyData(2) & ", " & aryMyData(3) & ", " & aryMyData(4) & ", " & aryMyData(5) & ", " & aryMyData(6) & ", #" & aryMyData(7) & "#, " & aryMyData(8) & ", " & aryMyData(9) & ", " & aryMyData(10) & ", " & aryMyData(11) & ", " & aryMyData() & ", " & aryMyData(12) & ");"
        Else
        End If
        'Debug.Print strSQL
        DoCmd.RunSQL strSQL
    Loop
    Close #1
    Debug.Print "hello"
End Function
Bart
  • 11
  • 3
  • Referring to aryMyData without giving the index parameter is one of the possible issues: `Debug.Print aryMyData` and in the `strSQL (...) " & aryMyData() & "`. – Tomasz Paluch Jun 29 '20 at 09:36
  • I see 0 code related to FTP in your question. Also, reading a CSV line-by-line and splitting it using `Split` is tricky business if it can contain text. Depending on the standard used for your CSV, it might contain escaped comma's and newlines. And, of course, `Debug.Print aryMyData` is wrong because you can't just print an array (bytearrays are an exception but usually produce nonsense when printed). – Erik A Jun 29 '20 at 09:47
  • The FTP part works fine. That i can verify, because i see the file appear on location. Was reffered as for the general setup. the debug.print is fixed( removed) heck if i remove all code of the loop i still get the error. – Bart Jun 29 '20 at 09:52
  • 1
    You have in the second assignment of `strSQL` a `aryMyData()`. This can't work. And at which line do you get the error _ Expected procedure, not variable_ – Storax Jun 29 '20 at 10:53
  • no line comes up on the error. given at the moment no error handling is present. – Bart Jun 29 '20 at 11:24
  • The compiler should mark a line after you have confirmed the message box. You are saying no line is marked in your code, right? And you are doing a `Debug/Compile`, right? – Storax Jun 29 '20 at 11:50
  • Concatenated parameters for text field need apostrophe delimiters, date/time field need #. `VALUES('" & aryMyData(0) & "', #" & aryMyData(1) & "#, "`. SQL will have issue if values have apostrophe or quote characters. Which line does the debugger highlight? – June7 Jun 29 '20 at 16:51
  • Please edit your post to include what is output to the immediate window by `Debug.Print strSQL`. Is it possible that `aryMyData(0)` is not equal to an empty string, so due to the position of the `DoCmd.RunSQL` you have not set `strSQL` equal to anything? – Applecore Jun 29 '20 at 17:00
  • Review https://stackoverflow.com/questions/62582374/syntax-missing-operator-error-saving-string-contents-to-variable-with-runsql-i/62582747#62582747 – June7 Jun 29 '20 at 17:12

0 Answers0