copying data from csv file
 

I am trying to copy data from a csv file into another workbook.
The code I have is below and it works except that the first row of the data
to be copied is missing after the copy.

The csv data start in A1 and have no headers. The data should be copied to
start in A2 to allow for a header row in the new worksheet.
I have tried making the destination A2 but that doesn't make any difference.
Can anyone help please?
Chris

On Error Resume Next
Application.DisplayAlerts = False
Workbooks.OpenText filename:=stockname & ".csv", _
Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:=
_
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False,
Semicolon:=False _
, Comma:=True, Space:=False, Other:=False, FieldInfo:=Array(Array(1,
3), _
Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1))
If Err.Number < 1004 Then
Columns("g").Delete
Cells(1, 1).Value = "Date"
Cells(1, 2).Value = "Open"
Cells(1, 3).Value = "High"
Cells(1, 4).Value = "Low"
Cells(1, 5).Value = "Close"
Cells(1, 6).Value = "Volume"
Columns("A:A").Select
Selection.TextToColumns Destination:=range("A1"), DataType:=xlDelimited,
_
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=False, _
Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo
_
:=Array(1, 5), TrailingMinusNumbers:=True

range("a1:f1").Select
With Selection
.Font.Bold = True
.HorizontalAlignment = xlCenter
End With
Columns("a:f").EntireColumn.AutoFit
twb = ActiveWorkbook.Name
' copy data to results file
Cells.Select
Selection.Copy
Windows(awb).Activate
Worksheets(wsheet).Activate
range("A1").Select
ActiveSheet.Paste

copying data from csv file
 

If, as it appears the file is a csv of the type Excel creates and
recognises, then you can use a simplified opening sequence, which will put
the data starting in A1, use a row insert and label headings as required.
Then it should be a matter of copy and paste as usual.
The macro you have does seem rather complex for such a simple task - -
recorder does this all the time!

So try this one

Workbooks.Open Filename:=stockname & ".csv"
Rows("1:1").Insert Shift:=xlDown
Range("A1").Value = "heading 1"

Cheers
Nigel

"inquirer" wrote in message
...
I am trying to copy data from a csv file into another workbook.
The code I have is below and it works except that the first row of the
data
to be copied is missing after the copy.

The csv data start in A1 and have no headers. The data should be copied to
start in A2 to allow for a header row in the new worksheet.
I have tried making the destination A2 but that doesn't make any
difference.
Can anyone help please?
Chris

On Error Resume Next
Application.DisplayAlerts = False
Workbooks.OpenText filename:=stockname & ".csv", _
Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited,
TextQualifier:=
_
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False,
Semicolon:=False _
, Comma:=True, Space:=False, Other:=False, FieldInfo:=Array(Array(1,
3), _
Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1))
If Err.Number < 1004 Then
Columns("g").Delete
Cells(1, 1).Value = "Date"
Cells(1, 2).Value = "Open"
Cells(1, 3).Value = "High"
Cells(1, 4).Value = "Low"
Cells(1, 5).Value = "Close"
Cells(1, 6).Value = "Volume"
Columns("A:A").Select
Selection.TextToColumns Destination:=range("A1"),
DataType:=xlDelimited,
_
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=False, _
Semicolon:=False, Comma:=True, Space:=False, Other:=False,
FieldInfo
_
:=Array(1, 5), TrailingMinusNumbers:=True

range("a1:f1").Select
With Selection
.Font.Bold = True
.HorizontalAlignment = xlCenter
End With
Columns("a:f").EntireColumn.AutoFit
twb = ActiveWorkbook.Name
' copy data to results file
Cells.Select
Selection.Copy
Windows(awb).Activate
Worksheets(wsheet).Activate
range("A1").Select
ActiveSheet.Paste

copying data from csv file
 

Thanks, that is much simpler and easier
Chris

"Nigel" wrote in message
...
If, as it appears the file is a csv of the type Excel creates and
recognises, then you can use a simplified opening sequence, which will put
the data starting in A1, use a row insert and label headings as required.
Then it should be a matter of copy and paste as usual.
The macro you have does seem rather complex for such a simple task - -
recorder does this all the time!

So try this one

Workbooks.Open Filename:=stockname & ".csv"
Rows("1:1").Insert Shift:=xlDown
Range("A1").Value = "heading 1"

Cheers
Nigel

"inquirer" wrote in message
...
I am trying to copy data from a csv file into another workbook.
The code I have is below and it works except that the first row of the
data
to be copied is missing after the copy.

The csv data start in A1 and have no headers. The data should be copied
to
start in A2 to allow for a header row in the new worksheet.
I have tried making the destination A2 but that doesn't make any
difference.
Can anyone help please?
Chris

On Error Resume Next
Application.DisplayAlerts = False
Workbooks.OpenText filename:=stockname & ".csv", _
Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited,
TextQualifier:=
_
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False,
Semicolon:=False _
, Comma:=True, Space:=False, Other:=False,
FieldInfo:=Array(Array(1,
3), _
Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1))
If Err.Number < 1004 Then
Columns("g").Delete
Cells(1, 1).Value = "Date"
Cells(1, 2).Value = "Open"
Cells(1, 3).Value = "High"
Cells(1, 4).Value = "Low"
Cells(1, 5).Value = "Close"
Cells(1, 6).Value = "Volume"
Columns("A:A").Select
Selection.TextToColumns Destination:=range("A1"),
DataType:=xlDelimited,
_
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=False, _
Semicolon:=False, Comma:=True, Space:=False, Other:=False,
FieldInfo
_
:=Array(1, 5), TrailingMinusNumbers:=True

range("a1:f1").Select
With Selection
.Font.Bold = True
.HorizontalAlignment = xlCenter
End With
Columns("a:f").EntireColumn.AutoFit
twb = ActiveWorkbook.Name
' copy data to results file
Cells.Select
Selection.Copy
Windows(awb).Activate
Worksheets(wsheet).Activate
range("A1").Select
ActiveSheet.Paste

copying data from csv file
 

Thanks, that is much simpler
Chris


"Nigel" wrote in message
...
If, as it appears the file is a csv of the type Excel creates and
recognises, then you can use a simplified opening sequence, which will put
the data starting in A1, use a row insert and label headings as required.
Then it should be a matter of copy and paste as usual.
The macro you have does seem rather complex for such a simple task - -
recorder does this all the time!

So try this one

Workbooks.Open Filename:=stockname & ".csv"
Rows("1:1").Insert Shift:=xlDown
Range("A1").Value = "heading 1"

Cheers
Nigel

"inquirer" wrote in message
...
I am trying to copy data from a csv file into another workbook.
The code I have is below and it works except that the first row of the
data
to be copied is missing after the copy.

The csv data start in A1 and have no headers. The data should be copied
to
start in A2 to allow for a header row in the new worksheet.
I have tried making the destination A2 but that doesn't make any
difference.
Can anyone help please?
Chris

On Error Resume Next
Application.DisplayAlerts = False
Workbooks.OpenText filename:=stockname & ".csv", _
Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited,
TextQualifier:=
_
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False,
Semicolon:=False _
, Comma:=True, Space:=False, Other:=False,
FieldInfo:=Array(Array(1,
3), _
Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1))
If Err.Number < 1004 Then
Columns("g").Delete
Cells(1, 1).Value = "Date"
Cells(1, 2).Value = "Open"
Cells(1, 3).Value = "High"
Cells(1, 4).Value = "Low"
Cells(1, 5).Value = "Close"
Cells(1, 6).Value = "Volume"
Columns("A:A").Select
Selection.TextToColumns Destination:=range("A1"),
DataType:=xlDelimited,
_
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=False, _
Semicolon:=False, Comma:=True, Space:=False, Other:=False,
FieldInfo
_
:=Array(1, 5), TrailingMinusNumbers:=True

range("a1:f1").Select
With Selection
.Font.Bold = True
.HorizontalAlignment = xlCenter
End With
Columns("a:f").EntireColumn.AutoFit
twb = ActiveWorkbook.Name
' copy data to results file
Cells.Select
Selection.Copy
Windows(awb).Activate
Worksheets(wsheet).Activate
range("A1").Select
ActiveSheet.Paste