Posts by giancarlo.g

Thanks for getting back!

I am in fact running this in Access. My goal is to add the field to the csv file before importing into an access database. Initially I was looking for a way to do it via VBA during the import step itself, however found this code (and suggestion) to do it beforehand, by using this code. Therefore, is there a way to achieve this by running the VBA in access?

Thanks.

hi,

I found this code to add a column to a csv with the filename as the value, however when running this code on my Scenario, I am getting "Sub or Function not defined" for the 'Cells' function (line 14). I'm assuming I need to specify the file. (I tried 'fn.Cells', 'x.Cells',) but still no joy. Quite new to VBA, can you help? Thank you!

Function AddColumn()


Dim myDir As String, fn As String, ff As Integer, txt As String, flg As Boolean
Dim delim As String, n As Long, b() As Variant, x As Variant, t As Integer




myDir = "C:\Users\----------\Documents\CRMTesting"
delim = ","
fn = Dir(myDir & "\*.csv")


Do While fn <> ""
ff = FreeFile
Open myDir & "\" & fn For Input As #ff


Do While Not EOF(ff)
Line Input #ff, txt
x = Split(txt, delim)
If Not flg Then
n = n + 1
Sheet1.Cells(n, 1) = fn
flg = True
End If
If UBound(x) > 0 Then
n = n + 1
Cells(n, 2) = x(2)
Cells(n, 3) = x(1)
End If
Loop
Close #ff
fn = Dir()
flg = False


Loop
End Function

Quote from pike

Re: Add a column with file name while importing .csv file in vba

hello,
Maybe just write straight to the worksheet

Code

myDir = "C:\Users\ASUS\Documents\Basket Stocks"
    delim = vbTab
    fn = Dir(myDir & "\*.csv")
    
    Do While fn <> ""
        ff = FreeFile
        Open myDir & "\" & fn For Input As #ff
         
        Do While Not EOF(ff)
            Line Input #ff, txt
            x = Split(txt, delim)
            If Not flg Then
                n = n + 1
              Cells(n, 1) = fn
                flg = True
            End If
            If UBound(x) > 0 Then
                n = n + 1
               Cells(n, 2) = x(2)
                Cells(n, 3) = x(1)
            End If
        Loop
        Close #ff
        fn = Dir()
        flg = False
       
    Loop

Display More

Hi, When running this code on my Scenario, I am getting "Sub or Function not defined" for the 'Cells' function (line 14). I'm assuming I need to specify the file. (I tried 'fn.Cells', 'x.Cells',) but still no joy. Quite new to VBA, can you help? Thank you!