VBA copying range from CSV directly imto master workbook

[lionelnz]

Hi all. Bit rusty on this VBA stuf.
1. I need to copy a range from CSV file and hoping to do that by going directly to the master workbook.
2. Close CSV workbook only.
3. Activate master workbook.
the macro runs but have to (2) manually and wish to eliminate this manual task.
The macro is run from master workbook and both files are in the same folder on a server. It is Excel 13 that I am using.

Sub OpenCSV_CopyPaste()
'
' Copy_Paste_CSV Macro
' To open CSV file and copy to master WB
Dim wb As Workbook: Set wb = ThisWorkbook
 
Dim cN As String
cN = InputBox("Enter CSV name without '.csv' ", "CSV Filename!")
cN = cN & ".csv"

'
'Opens CSV file for weekly weather data
     Workbooks.OpenText Filename:="J:\Te Maunga ops\Easyweather\CSV EW\" & cN, _
        Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
        xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
        Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), _
        Array(2, 4), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), _
        Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1), Array(14, 1), Array(15 _
        , 1), Array(16, 1), Array(17, 1), Array(18, 1), Array(19, 1)), TrailingMinusNumbers:= _
        True
    'Selects and copies data fromCSV
    Range("B2").Select
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Selection.Copy
   [COLOR=#0000CD][B] 'Paste directly into master workbook
    Windows(wb).Sheets("CSV").Range("B" & Rows.Count).End(xlUp).Offset(1).Paste
    ActiveWorkbook.Close[/B][/COLOR]
    
    
    'Activates master weather WB & pastes to end of data set
    
    'Windows("170116 weather history .xlsm").Activate
    'Sheets("CSV").Select
    'Range("B" & Rows.Count).End(xlUp).Offset(1).Select
        
    'ActiveSheet.Paste
End Sub

 

[Jeffrey Mahoney]

I know I'm changing all your code, but please accept an alternative. This procedure copies directly from the CSV file without creating another workbook and then having to copy it to your master. You'll find it to be a bit faster. It takes out the middle man.

Sub GetCSVData()

  Dim A As String
  Dim R As Range
  Dim Pathfile As String
  Dim FF As Integer
  Dim X As Long
  Dim Y As Long
  
  Dim TWB As Workbook
  Dim CSV As Worksheet
  Dim OutR As Range
  Dim cN As String
  Dim Ary() As String
  
  Set TWB = ThisWorkbook
  Set CSV = TWB.Sheets("CSV")
  With CSV
    Set OutR = Cells(.Rows.Count, 2).End(xlUp)
  End With
  
  Application.Calculation = xlCalculationManual
  Application.EnableEvents = False
  
  
  cN = InputBox("Enter CSV name without '.csv' ", "CSV Filename!")
  cN = cN & ".csv"
  
  Pathfile = "J:\Te Maunga ops\Easyweather\CSV EW\" & cN
  A = Dir(Pathfile)
  If A = "" Then Exit Sub
  
  FF = FreeFile
  Open Pathfile For Input As #FF
  
  On Error GoTo ProblemChild
  X = 0
  Do Until EOF(FF)
    Line Input #FF, A
    X = X + 1
    Ary() = Split(A, ",")
    For Y = 0 To UBound(Ary)
      OutR.Offset(X, Y) = Ary(Y)
    Next Y
  Loop
  On Error GoTo 0
  Close #FF
  
  Application.Calculation = xlCalculationAutomatic
  Application.EnableEvents = True
  
  Exit Sub
ProblemChild:
  Close #FF
  
  
End Sub

 

[lionelnz]

Thanks for this Jeff but unfortunately it appears that the CSV file has to be opened and the data imported as xlsx format otherwise your code imports as single cell data per row.
The other thing is that the data set is a weekly operation on one csv file only coming from 3rd party weather recorder but does not have a lot of memory.

However I have come up with a solution and yes need to put in not updating sheet/window.

I do have a question. I am not happy with ActiveWorkbook.Close (last line of code) which closes the csv file. I like to know by using variables which file I am closing, activating etc. Is there a way I can do this?

Sub AAAOpenCSV_CopyPaste()
'
' Copy_Paste_CSV Macro
' To open CSV file and copy to master WB
Dim wb As Workbook: Set wb = ThisWorkbook
Dim Rng1 As Range, Rng2 As Range
Set Rng2 = Workbooks("170116 weather history .xlsm"). _
    Sheets("CSV").Range("B" & Rows.Count).End(xlUp).Offset(1)
 
 
Dim cN As String
cN = InputBox("Enter CSV name without '.csv' ", "CSV Filename!")
cN = cN & ".csv"

'
'Opens CSV file for weekly weather data
      Workbooks.OpenText Filename:="J:\Te Maunga ops\Easyweather\CSV EW\" & cN, _
        Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
        xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
        Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), _
        Array(2, 4), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), _
        Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1), Array(14, 1), Array(15 _
        , 1), Array(16, 1), Array(17, 1), Array(18, 1), Array(19, 1)), TrailingMinusNumbers:= _
        True
    'Selects and copies data fromCSV
    Range("B2").Select
    Set Rng1 = Range(Selection, ActiveCell.SpecialCells(xlLastCell))
    Rng1.Copy Rng2
   [COLOR=#0000CD][B]ActiveWorkbook.Close[/B][/COLOR]
    
    
End Sub

 

[Jeffrey Mahoney]

Is it actually a CSV file? Meaning, is it a comma separated values format? If the delimeter is another character, that's easy to fix.

 

[Jeffrey Mahoney]

Dang, I should have looked. It's a tab delimted file

Sub GetCSVData()

  Dim A As String
  Dim R As Range
  Dim Pathfile As String
  Dim FF As Integer
  Dim X As Long
  Dim Y As Long
  
  Dim TWB As Workbook
  Dim CSV As Worksheet
  Dim OutR As Range
  Dim cN As String
  Dim Ary() As String
  Dim TB As String
  
  
  Set TWB = ThisWorkbook
  Set CSV = TWB.Sheets("CSV")
  With CSV
    Set OutR = Cells(.Rows.Count, 2).End(xlUp)
  End With
  
  TB = Chr(9)
  
  Application.Calculation = xlCalculationManual
  Application.EnableEvents = False
  
  
  cN = InputBox("Enter CSV name without '.csv' ", "CSV Filename!")
  cN = cN & ".csv"
  
  Pathfile = "J:\Te Maunga ops\Easyweather\CSV EW\" & cN
  A = Dir(Pathfile)
  If A = "" Then Exit Sub
  
  FF = FreeFile
  Open Pathfile For Input As #FF
  
  On Error GoTo ProblemChild
  X = 0
  Do Until EOF(FF)
    Line Input #FF, A
    X = X + 1
    Ary() = Split(A, TB)
    For Y = 0 To UBound(Ary)
      OutR.Offset(X, Y) = Ary(Y)
    Next Y
  Loop
  On Error GoTo 0
  Close #FF
  
  Application.Calculation = xlCalculationAutomatic
  Application.EnableEvents = True
  
  Exit Sub
ProblemChild:
  Close #FF
  
  
End Sub

 

[lionelnz]

No it is a CSV file from a 3rd party app. The reason why I have to download it as the data is actualy stored on the weather recording device and has limited storage and other people need to access the data.

 

[Jeffrey Mahoney]

The data must be in a TAB delimeted format based on the code your using to import the data. The last bit of code I sent should import that data.

Workbooks.OpenText Filename:="J:\Te Maunga ops\Easyweather\CSV EW\" & cN, _        Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
        xlDoubleQuote, ConsecutiveDelimiter:=False, [COLOR=#ff0000]Tab:=True[/COLOR], Semicolon:=False, _
        [COLOR=#0000ff]Comma:=False[/COLOR], Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), _
        Array(2, 4), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), _
        Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1), Array(14, 1), Array(15 _
        , 1), Array(16, 1), Array(17, 1), Array(18, 1), Array(19, 1)), TrailingMinusNumbers:= _
        True

 

[lionelnz]

Sorry brain explosion. Tab delimiter.
I will try that and see what happens.
Thank you.