ADO Import csv file to excel, Variable not set error

[ltworf13]

The file run through and grabs my csf files but then stops when it get's to my left file. For some reason the Tempcell object variable get's lost and becomes nothing. When I step through the code I see that it hops backwarss back to close #1 then make TempCell = nothing

Sub TestGetTextFileData()
    Application.ScreenUpdating = False
    
CLearData.ClearAllPdata

Dim cn As ADODB.Connection
Set cn = New ADODB.Connection
Set cmd = New ADODB.Command




With cn
    .Provider = "Microsoft.Jet.OLEDB.4.0"
    .ConnectionString = "Data Source=c:\Reporting\skilltable.xls;" & _
"Extended Properties=Excel 8.0;"
    .Open
End With


Set cmd.ActiveConnection = cn

cmd.CommandText = "SELECT * FROM [Input_Data$]"


Set rs = cmd.Execute


With rs

'RowPlacement = ![RowPlacement]
    
        While Not rs.EOF ' loops through first movenext
                            skill = ![skill]
                            AggName = ![AggName]
                            Location = ![Location]
                            CSVfilename = ![filename]
                            RowPlacement = ![RowPlacement]
Sheets("CMS Skills Data").Select
    getCMSIntervalData "SELECT * FROM " & CSVfilename & "", "C:\", Range("a8:s8"), skill, AggName, Location, CSVfilename, RowPlacement

    Columns("A:IV").AutoFit
                  
'   RowPlacement = RowPlacement + 1
                    .MoveNext
 
        Wend
        

        
        rs.Close
            Set rs = Nothing
        
        Close #2
        
    End With 'With rsRec


End Sub



Sub getCMSIntervalData(strSQL As String, strFolder As String, rngTargetCell As Range, skill, AggName, Location, CSVfilename, RowPlacement)

Dim info As Object
Dim REP As Object
Dim b As Boolean
Dim status As Boolean
Dim log As Object
Dim incDate As Date
Dim freetmpnum As Integer
Dim linestring As String
Dim tmp As String
Dim StartDate As Date
Dim EndDate As Date
Dim Header As String
Dim outfile As String
Dim ReturnValue As Variant
Dim stepp As Double
Dim TempCell As Range

    ExportCVFile = "c:\" & CSVfilename

                            

                    On Error GoTo Skip
                        Open ExportCVFile For Input As #1
                                    
                              freetmpnum = FreeFile()
                              found = 0
                                        While found = 0
                                          Line Input #1, linestring
                        If Left(linestring, 5) = "Total" Then
                            totallinestring = linestring
                            found = 1
                                End If
                                        Wend
Skip:
                        Close #1

                    
                    Set TempCell = Sheets("CMS Skills Data").Range("A" & CStr(RowPlacement) & ":A1000").Find(What:="", SearchOrder:=xlByRows)
                    
                    
                    
                   ' TempCell.Offset(RowPlacement, 0).Value = AggName & "," & Location & "," & skill & "," & totallinestring
                    TempCell.Value = AggName & "," & Location & "," & skill & "," & totallinestring
                    
                    TempCell.Select
                    Selection.TextToColumns Destination:=ActiveCell, DataType:=xlDelimited, _
                    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
                    Semicolon:=False, Comma:=True, Space:=False, Other:=False, OtherChar _
                    :=".", FieldInfo:=Array(1, 1)
                    

'    Close #2



 
  
End Sub

[ltworf13]

got it. The cell range I have to A1000 had to be formated for some reason for it to parse the data

[Killer42]

Way too quick for us. :)

Glad to see you got it working, anyway.