Member Avatar for grvs

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

  • 4 Contributors
  • 7 Replies
  • 455 Views
  • 2 Years Discussion Span
  • Latest Post Latest Post by grvs

Dani AI

Generated

A short, practical workflow to automate CSV → Excel → PowerPoint (build on the thread)

’s line-by-line parser is a fine quick hack, and is right that Excel can open CSVs directly, but for reliable automation you want a method that handles quoted fields, embedded commas/semicolons and locale date/number differences. Use Excel’s import routines (Workbooks.OpenText or a QueryTable / Power Query) to import cleanly, create the chart in Excel, then automate PowerPoint and paste the chart (as a picture or embedded chart) — that keeps charting logic in Excel where it belongs and leaves PowerPoint for presentation.

Example (late binding, avoid needing a reference):

Sub ImportCSV_OpenText(Fname As String, Optional Delim As String = ",")
    Dim commaFlag As Boolean, semicolonFlag As Boolean
    commaFlag = (Delim = ",")
    semicolonFlag = (Delim = ";")
    Workbooks.OpenText Filename:=Fname, DataType:=xlDelimited, TextQualifier:=xlTextQualifierDoubleQuote, _
        ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=semicolonFlag, Comma:=commaFlag, Space:=False, Other:=False
End Sub

Sub ExportRangeChartToPPT(rng As Range)
    Dim chtObj As ChartObject
    Set chtObj = rng.Worksheet.ChartObjects.Add(10, 10, 480, 320)
    chtObj.Chart.SetSourceData Source:=rng
    chtObj.Chart.ChartType = xlLineMarkers

    chtObj.Chart.CopyPicture Appearance:=xlScreen, Format:=xlPicture

    Dim ppApp As Object, ppPres As Object, ppSlide As Object
    Set ppApp = CreateObject("PowerPoint.Application")
    ppApp.Visible = True
    Set ppPres = ppApp.Presentations.Add
    Set ppSlide = ppPres.Slides.Add(1, 12)  '12 = ppLayoutBlank
    ppSlide.Shapes.Paste
End Sub

Practical tips and gotchas

  • If your system uses semicolons (European locales), set Semicolon:=True or pass Delim=";".
  • Prefer CopyPicture or Chart.Copy when pasting as image; paste special or linking if you need live updates.
  • Clean up COM objects and avoid leaving PowerPoint instances running.
  • For large/recurring jobs consider Power Query (Get & Transform) for robust imports, or create a hidden Excel workbook that generates charts and only push the images to PowerPoint.
  • As noted, always close any opened file handles and release objects to avoid locks.

This keeps import robust, chart logic in Excel, and presentation automation simple and maintainable.

Recommended Answers

All 7 Replies

Member Avatar for grvs

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)

Member Avatar for rdrast

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?

Member Avatar for grvs

because i need to automate things....

Member Avatar for Koukal

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.

Member Avatar for grvs

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.

Member Avatar for newhere

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

Member Avatar for grvs

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.