Hello guys

I am a newbie so please don't freak if you find this question stupid or easy

I have some data in CSV format, which I want to bring into Excel, and based on that data, I have to create graphs in PowerPoint.
I searched on Google and everywhere I find complex techniques to bring data into data-bases using some ADO objects and something called RECORDSET.
I want to know if there is something simpler which brings data into excel only.

Here is the Code till now.

sub importData()
fileName = Application.GetOpenFilename("Text Files (*.csv),*.csv", , "Please selec text file (in .csv format)...")
If fileName = "False" Then Exit Sub  [I]'User pressed Cancel on the open file dialog[/I]
End Sub

Next steps
save data from this file ( fileName ) in excel
create graph in PowerPoint.

Please provide guidance
Thanks
Gaurav

58 views and still no reply... I guess people here don't love easy work.
But if anyone among you have the same problem, I got a solution.

The solution is:

Public Sub ImportTextFile(Fname As String, Sep As String)

    Dim RowNdx As Long
    Dim ColNdx As Integer
    Dim TempVal As Variant
    Dim WholeLine As String
    Dim Pos As Integer
    Dim NextPos As Integer
    Dim SaveColNdx As Integer
    
    Application.ScreenUpdating = False
    'On Error GoTo EndMacro:
    
    SaveColNdx = ActiveCell.Column
    RowNdx = ActiveCell.Row
    
    Open Fname For Input Access Read As #1
    
    While Not EOF(1)
        Line Input #1, WholeLine
        If Right(WholeLine, 1) <> Sep Then
            WholeLine = WholeLine & Sep
        End If
        ColNdx = SaveColNdx
        Pos = 1
        NextPos = InStr(Pos, WholeLine, Sep)
        While NextPos >= 1
            TempVal = Mid(WholeLine, Pos, NextPos - Pos)
            Cells(RowNdx, ColNdx).Value = TempVal
            Pos = NextPos + 1
            ColNdx = ColNdx + 1
            NextPos = InStr(Pos, WholeLine, Sep)
        Wend
        RowNdx = RowNdx + 1
    Wend
End Sub

Then use this subroutine into importData after you get a file name and a separator.
But now I am stuck at "how to make graph in PowerPoint using data in excel?".
Any suggestions (though I haven't searched a lot on this, I hope to find a solution my self, will compare to yours if I find it myself)

Why "Import" at all? Excel can open a CSV file just fine all by itself.

As for graphing, just, well, define your X and Y axis in Excel (well, make your graph). Then copy it, and paste into a PowerPoint slide.

Really, why over complicate things?

PowerPoint is designed for presentation, not graphing, or data manipulation. Use it for presentation.

ADO RDO, DAC, etc are generally used for databases. Excel is not a database, though it's files can be manipulated by those components. Again, why over complicate?

because i need to automate things....

58 views and still no reply... I guess people here don't love easy work.
But if anyone among you have the same problem, I got a solution.

The solution is:

Public Sub ImportTextFile(Fname As String, Sep As String)

    Dim RowNdx As Long
    Dim ColNdx As Integer
    Dim TempVal As Variant
    Dim WholeLine As String
    Dim Pos As Integer
    Dim NextPos As Integer
    Dim SaveColNdx As Integer
    
    Application.ScreenUpdating = False
    'On Error GoTo EndMacro:
    
    SaveColNdx = ActiveCell.Column
    RowNdx = ActiveCell.Row
    
    Open Fname For Input Access Read As #1
    
    While Not EOF(1)
        Line Input #1, WholeLine
        If Right(WholeLine, 1) <> Sep Then
            WholeLine = WholeLine & Sep
        End If
        ColNdx = SaveColNdx
        Pos = 1
        NextPos = InStr(Pos, WholeLine, Sep)
        While NextPos >= 1
            TempVal = Mid(WholeLine, Pos, NextPos - Pos)
            Cells(RowNdx, ColNdx).Value = TempVal
            Pos = NextPos + 1
            ColNdx = ColNdx + 1
            NextPos = InStr(Pos, WholeLine, Sep)
        Wend
        RowNdx = RowNdx + 1
    Wend
End Sub

Then use this subroutine into importData after you get a file name and a separator.
But now I am stuck at "how to make graph in PowerPoint using data in excel?".
Any suggestions (though I haven't searched a lot on this, I hope to find a solution my self, will compare to yours if I find it myself)

Hi, your code is nice, I tried to use it and it works. The only very small bug is you have forgotten to add the command CLOSE #1 just before you leave the routine.
I have one surprising reason why to use such a code instead simply opening the csv file via Excel procedure: csv file opened by clicking onto file name opens correctly, opening the file via VBA reads the csv data chaoticaly, sometimes respects the delimiting semicolon, sometimes not! This is not the case of the code published. Thank you.

Well nice to see that this code helped you.
And thanks for correcting the mistake.

Well I did not know about the semicolons, but excel does play with the quotes in the csv file. Interesting to note that it also plays with semi-colons.

I'm totally new to VB, how do I combine these subrutines?
how to insert 2nd part into importData

just write the subroutine with arguements

sub importData()
fileName = Application.GetOpenFilename("Text Files (*.csv),*.csv", , "Please selec text file (in .csv format)...")
If fileName = "False" Then Exit Sub  'User pressed Cancel on the open file dialog

'Calling the subroutine now:
call ImportTextFile(stringName, SepSymbol)

End Sub
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.