Excel And VB6 Question?

[Kev]

I have 2 .csv files that I want to convert into a xls file showing each of the .csv files on a seperate worksheet.

At the moment I have the following code -

    Dim objExcel As Excel.Application
    Dim objWrkBk As Excel.Workbook
    Dim objSheet As Excel.Worksheet
    Dim strFilename As String
    Dim strExcelFileName As String
    Dim intResp As Integer
    
    Set objExcel = CreateObject("Excel.Application")
    
    objExcel.Workbooks.OpenText FileName:=App.Path & "/" & "StandardHmeLabelsRep.csv", Origin:=xlWindows, StartRow:=1, _
        DataType:=xlDelimited, TextQualifier:=xlTextQualifierDoubleQuote, ConsecutiveDelimiter:=False, _
        Tab:=False, Semicolon:=True, Comma:=False, Space:=False, Other:=False, _
        FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
        Array(7, 1), Array(8, 1), Array(9, 1))
    
    strExcelFileName = App.Path & "\LabelsStandardReport.xls"
    
    If Dir(strExcelFileName) <> "" Then
        intResp = MsgBox(strExcelFileName & " already exists, do you wish to overwrite this file?", vbQuestion + vbYesNoCancel, "FIFG - separation")
        
        If intResp = vbNo Then
            comGetFile.ShowOpen
            strExcelFileName = comGetFile.FileName
            
            If strExcelFileName = "" Or IsNull(strExcelFileName) Then
                objExcel.Workbooks.Close
                objExcel.Quit
                Set objExcel = Nothing
                MsgBox "You have cancelled the Actual Paid report!", vbInformation + vbOKOnly
                g_ShowGlass False
                Exit Sub
            End If
        ElseIf intResp = vbYes Then
            Kill strExcelFileName
        ElseIf intResp = vbCancel Then
            objExcel.Workbooks.Close
            objExcel.Quit
            Set objExcel = Nothing
            MsgBox "You have cancelled the Actual Paid report!", vbInformation + vbOKOnly
            g_ShowGlass False
            Exit Sub
        End If
    End If
    
    objExcel.ActiveWorkbook.SaveAs FileName:=strExcelFileName, FileFormat:= _
    xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
    , CreateBackup:=False
    
    objExcel.Workbooks.Open strExcelFileName
    objExcel.Worksheets.Add

    
    objExcel.Workbooks.OpenText FileName:=App.Path & "/" & "StandardExpLabelsRep.csv", Origin:=xlWindows, StartRow:=1, _
    DataType:=xlDelimited, TextQualifier:=xlTextQualifierDoubleQuote, ConsecutiveDelimiter:=False, _
    Tab:=False, Semicolon:=False, Comma:=True, Space:=False, Other:=False, _
    FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
    Array(7, 1), Array(8, 1), Array(9, 1))
'
'    objExcel.ActiveWorkbook.SaveAs FileName:=strExcelFileName, FileFormat:= _
'        xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
'        , CreateBackup:=False

    objExcel.Workbooks.Close
    objExcel.Quit
    Set objExcel = Nothing

I can place the first csv file fine and create a new worksheet, but how do I place the second csv file onto the newlyt created worksheet?

[westconn1]

the worksheets are a collection so you need to move to the next sheet and set it as the worksheet object

rgds pete

[thiru_rajamani]

Since already you have a workbook, just add a sheet or activate the next sheet

[Kev]

Thanks i have done that now.

Is there a quick way of loading a comma delimited file into the active work sheet?

thanks

Kev

[trisuglow]

Does this work for you?

Workbooks.Open "C:\PersonalContacts.csv", , , 2

[Kev]

I don't think so.

I have an exsiting spreadsheet that I open, add a new worksheet and make that worksheet active. I then want to place the contents of a csv file into the new worksheet. So far all I have is

    objExcel.Workbooks.Open strExcelFileName
    objExcel.Worksheets.Add after:=Sheets(Worksheets.Count)

    objExcel.Worksheets(Worksheets.Count).Select

I tried the loading using the opentext method, but is only used for creating a new workbook.

[trisuglow]

What about this then?

Sub ImportCSV()
    Dim f As Integer
    Dim s As String
    Dim l() As String
    Dim r As Long
    
    r = 20
    f = FreeFile
    Open "C:\PersonalContacts.csv" For Input As f
    While Not EOF(f)
        Line Input #f, s
        l = Split(s, ",")
        For c = 1 To UBound(l)
            Cells(r, c).Value = l(c)
        Next
        r = r + 1
    Wend
    Close f
End Sub

[Kev]

That sort of works, it is missing the first Line and first column but I can get around that. It also is puts quotes around everything.

I'll figure it out now that you gave me a workable suggestion. Thnaks for your help.



Kev.