Getting data from CSV file into a workbook

[AventAClue]

Hi All,

Hoping someone may be able to help out with some knowledge and code.

I have an excel workbook that I'd like to automate the process of me going to a csv file, copying it's data, and pasting it back to a worksheet within my workbook.

Ive just had some great help from Peter SSs to manipulate some data from worksheet to worksheet within the workbook, but now hope to go that little further in being able to have the csv data part of it included in the process.

Thinking along the lines of pressing a macro button within my workbook it:

-Asks which file i wish to open
-Upon me locating and selecting the csv, copies the sheet contents (can be the whole csv file sheet)
-Closes the csv file and pastes the copied data to worksheet called "DataDownload" in my workbook

It could then continue on and run the code that Peter SSs provided for me.

The thread that covers my previous 'worksheet to worksheet' code which may help in explaining what I'm after is located at: http://www.mrexcel.com/forum/showthread.php?p=2874620#post2874620


Thanks very much

 

[AventAClue]

the sheet name must be DataDownload not "Data Download" I think.

Yes I had removed the space between "Data Download" as well.

I just tried that change you just put in there but it gives a runtime error 91
'Object variable or With Block variable not set'

Also when you run this part the destination file should be the activeworkbook.

I open my workbook, run the macro and it asks which file i wish to open, I select it and click ok. I see it open the file i selected to open and that's when it crashes.

 

[Arul.rajesh]

sub copy_CSV()

dim DestFile as workbook
Dim srcfile as Workbook
Dim sourcefile
set DestWB=Activeworkbook
sourcefile = Application.GetOpenFilename(,,"Select CSV file")
Workbooks.Open sourcefile
Set srcfile = ActiveWorkbook
srcfile.sheets(1).usedrange.Copy Destination:=DestFile.sheets("DataDownload").Cells(1, 1)
srcfile.Close

end sub

Sorry My mistake I should have copied the range instead of the sheet.

 

[AventAClue]

sub copy_CSV()

dim DestFile as workbook
Dim srcfile as Workbook
Dim sourcefile
set DestWB=Activeworkbook
sourcefile = Application.GetOpenFilename(,,"Select CSV file")
Workbooks.Open sourcefile
Set srcfile = ActiveWorkbook
srcfile.sheets(1).usedrange.Copy Destination:=DestFile.sheets("DataDownload").Cells(1, 1)
srcfile.Close

end sub

Sorry My mistake I should have copied the range instead of the sheet.

Hi Arul.rajesh,

I tried that piece of code but it's still stopping on that same line and throwing back the same error.
srcfile.Sheets(1).UsedRange.Copy Destination:=DestFile.Sheets("DataDownload").Cells(1, 1)

Runtime error 91
'Object variable or With Block variable not set'

 

[AventAClue]

What about my macro-free approach?

Hi Jan,

I'm actually very impressed with the way that works.
The only issue I see in my situation is I close off my file each 90 days, and use my template to begin the next one before saving it as the new filename.

90 days apart is probably long enough for me to forget how to set it up again the next time, whereas the macro button asks me to locate the file each time I perform an update.

Actually two days is probably long enough, truth be told lol

I do like it though

 

[Peter_SSs]

As far as I understand, the code will reside in the workbook that holds the 'DataDownload' sheet. If that is the case, then try this in a copy of that workbook.

This combines the code that is being attempted here and the code from the other thread you linked to. Both codes have been modified a bit.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> CopyData()<br>    <SPAN style="color:#00007F">Dim</SPAN> wsM <SPAN style="color:#00007F">As</SPAN> Worksheet, wsDD <SPAN style="color:#00007F">As</SPAN> Worksheet<br>    <SPAN style="color:#00007F">Dim</SPAN> StDate <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, r <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, lc <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, c <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> sourcefile <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br><br>    <SPAN style="color:#00007F">Set</SPAN> wsM = Sheets("Manipulated")<br>    <SPAN style="color:#00007F">Set</SPAN> wsDD = Sheets("DataDownload")<br>    <SPAN style="color:#00007F">With</SPAN> wsM<br>        StDate = .Range("B2").Value<br>        lc = .Cells(2, .Columns.Count).End(xlToLeft).Column<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    sourcefile = Application.GetOpenFilename(, , "Select CSV file")<br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">With</SPAN> wsDD<br>        .UsedRange.ClearContents<br>        Workbooks.Open sourcefile<br>        ActiveSheet.UsedRange.Copy Destination:=.Cells(1, 1)<br>        Application.DisplayAlerts = <SPAN style="color:#00007F">False</SPAN><br>        ActiveWorkbook.Close<br>        .Columns("B:C").Insert<br>        .Columns("A").TextToColumns Destination:=.Range("A1"), DataType:=xlDelimited, _<br>            TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _<br>            Semicolon:=False, Comma:=<SPAN style="color:#00007F">False</SPAN>, Space:=True, Other:=False, _<br>            FieldInfo:=Array(Array(1, 4), Array(2, 1)), TrailingMinusNumbers:=<SPAN style="color:#00007F">True</SPAN><br>        Application.DisplayAlerts = <SPAN style="color:#00007F">True</SPAN><br>        r = 1<br>        <SPAN style="color:#00007F">Do</SPAN><br>            r = r + 1<br>        <SPAN style="color:#00007F">Loop</SPAN> <SPAN style="color:#00007F">While</SPAN> .Cells(r, 1).Value <> StDate<br>        r = r + 1<br>        Application.ScreenUpdating = False<br>        <SPAN style="color:#00007F">For</SPAN> c = 2 <SPAN style="color:#00007F">To</SPAN> lc<br>            wsM.Cells(4, c).Resize(288).Value = .Cells(r, 4).Resize(288).Value<br>            r = r + 288<br>        <SPAN style="color:#00007F">Next</SPAN> c<br>        Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br>End <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[jkpieterse]

Can't you setup your template with the text import already in place and put a small textbox on there with a reminder how it works?

 

[Jon von der Heyden]

I'm quite partial to a bit of ADO when it comes to importing CSV data.

Public Sub GetCSVData()
    Dim varFile As Variant
    Dim lngSplit As Long, strTable As String, strPath As String
    Dim objRS As Object
    Dim strConnection As String, strSQL As String
    Dim lngField As Long, varHeaders As Variant
    
    varFile = Application.GetOpenFilename("CSV Files (*.csv), *.csv", , "Get CSV File", "Select", False)
    If varFile = False Then Exit Sub
    
    lngSplit = InStrRev(varFile, "\")
    
    strTable = Mid$(varFile, lngSplit + 1)
    strPath = Left$(varFile, lngSplit - 1)
    
    strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                    "Data Source=" & strPath & ";" & _
                    "Extended Properties=Text;"
                    
    strSQL = "SELECT * FROM " & strTable & ";"

    Set objRS = CreateObject("ADODB.Recordset")
    Call objRS.Open(strSQL, strConnection, 1, 3, 1)
    
    With Sheets("DataDownload")
        .UsedRange.Clear
        For lngField = 0 To objRS.Fields.Count - 1
            .Cells(1, lngField + 1).Value = objRS.Fields(lngField).Name
        Next lngField
        Call .Cells(2, 1).CopyFromRecordset(objRS)
    End With
    
    objRS.Close
    Set objRS = Nothing
End Sub

 

[AventAClue]

As far as I understand, the code will reside in the workbook that holds the 'DataDownload' sheet. If that is the case, then try this in a copy of that workbook.

This combines the code that is being attempted here and the code from the other thread you linked to. Both codes have been modified a bit.


Sub CopyData()
Dim wsM As Worksheet, wsDD As Worksheet
Dim StDate As Long, r As Long, lc As Long, c As Long
Dim sourcefile As String

Set wsM = Sheets("Manipulated")
Set wsDD = Sheets("DataDownload")
With wsM
StDate = .Range("B2").Value
lc = .Cells(2, .Columns.Count).End(xlToLeft).Column
End With
sourcefile = Application.GetOpenFilename(, , "Select CSV file")
Application.ScreenUpdating = False
With wsDD
.UsedRange.ClearContents
Workbooks.Open sourcefile
ActiveSheet.UsedRange.Copy Destination:=.Cells(1, 1)
Application.DisplayAlerts = False
ActiveWorkbook.Close
.Columns("B:C").Insert
.Columns("A").TextToColumns Destination:=.Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=True, Other:=False, _
FieldInfo:=Array(Array(1, 4), Array(2, 1)), TrailingMinusNumbers:=True
Application.DisplayAlerts = True
r = 1
Do
r = r + 1
Loop While .Cells(r, 1).Value <> StDate
r = r + 1
Application.ScreenUpdating = False
For c = 2 To lc
wsM.Cells(4, c).Resize(288).Value = .Cells(r, 4).Resize(288).Value
r = r + 288
Next c
Application.ScreenUpdating = True
End With
Application.ScreenUpdating = True
End Sub

That worked perfectly!!!

Thank you again Peter ! That worked "straight out of the box" so to speak!

Does exactly what I wanted to do.

Thank you very much for all your help! That's made things so much easier and will not only save me heaps of time but also removes the very likely possibility of me making mistakes doing it manually.

I'm very pleased! I love it!
Thanks so much!!!

 

[AventAClue]

Can't you setup your template with the text import already in place and put a small textbox on there with a reminder how it works?

Yes, I thought I might go down that path if the macro code didn't work out Jan. As I say I did like the way once it was set up I'd only need to select a cell for it to update again.
And the textbox would certainly be something i'd need to remember the steps.
I already need them on the workbook as it is lol.

The code from Peter SSs went in flawlessly and does exactly as I had hoped.

A huge thankyou to both yourself and Arul.rajesh for the work you have both put in!!
I appreciate the help available in forums like this. It's great to see people offering to help others.
Thankyou all so much !

 

[AventAClue]

I'm quite partial to a bit of ADO when it comes to importing CSV data.

Hi Jon,

Forgive my ignorance. I'm not sure what you mean by ADO?