Hi All

I have just used the macro recorder for the code below is there a way to
send it to
sheet3 without copying and pasting.

Sub Tex2Col()

Sheets("Sheet2").Select
Range("A1:A150").Select
Selection.TextToColumns Destination:=Range("I1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=True, _
Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
:=Array(Array(1, 9), Array(2, 9), Array(3, 1), Array(4, 1)),
TrailingMinusNumbers:= _
True
Range("I1:J150").Select
Selection.Copy
Sheets("Sheet3").Select
Range("G2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
End Sub

--
Many Thanks

textToColumns has to be on the source sheet AFAIK but you can replace the
last few lines

Range("I1:J150").Select
Selection.Copy
Sheets("Sheet3").Select
Range("G2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False


by:
With Range("I1:J150")
worksheets("sheet3").Range("G2").Resize( .Rows.Count,
..Columns.Count).Value = .Value
End With


"Hazel" wrote:

Hi All

I have just used the macro recorder for the code below is there a way to
send it to
sheet3 without copying and pasting.

Sub Tex2Col()

Sheets("Sheet2").Select
Range("A1:A150").Select
Selection.TextToColumns Destination:=Range("I1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=True, _
Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
:=Array(Array(1, 9), Array(2, 9), Array(3, 1), Array(4, 1)),
TrailingMinusNumbers:= _
True
Range("I1:J150").Select
Selection.Copy
Sheets("Sheet3").Select
Range("G2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
End Sub

--
Many Thanks

You can use that Destination parm:

Worksheets("Sheet2").Range("A1:A150").TextToColumn s _
Destination:=Worksheets("sheet3").Range("G2"), _
DataType:=xlDelimited, _
textQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, _
Tab:=True, _
Semicolon:=False, _
Comma:=True, _
Space:=False, _
Other:=False, _
FieldInfo:=Array(Array(1, 9), Array(2, 9), Array(3, 1), Array(4, 1)), _
TrailingMinusNumbers:=True



Hazel wrote:

Hi All

I have just used the macro recorder for the code below is there a way to
send it to
sheet3 without copying and pasting.

Sub Tex2Col()

Sheets("Sheet2").Select
Range("A1:A150").Select
Selection.TextToColumns Destination:=Range("I1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=True, _
Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
:=Array(Array(1, 9), Array(2, 9), Array(3, 1), Array(4, 1)),
TrailingMinusNumbers:= _
True
Range("I1:J150").Select
Selection.Copy
Sheets("Sheet3").Select
Range("G2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
End Sub

--
Many Thanks

--

Dave Peterson

Hi Dave

Used your code and it worked a treat thank you for your help. May I take
this a stage further and ask you is it possible if I place a TextBox on the
UserForm and browse for the contents of the csv data -- I usually save all
csv data that I receive by Email to a CSV folder on the Desktop. Then use a
CommandButton to place it in Column A of Sheet2. Once again thanks for your
patience.
--
Many Thanks


"Dave Peterson" wrote:

You can use that Destination parm:

Worksheets("Sheet2").Range("A1:A150").TextToColumn s _
Destination:=Worksheets("sheet3").Range("G2"), _
DataType:=xlDelimited, _
textQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, _
Tab:=True, _
Semicolon:=False, _
Comma:=True, _
Space:=False, _
Other:=False, _
FieldInfo:=Array(Array(1, 9), Array(2, 9), Array(3, 1), Array(4, 1)), _
TrailingMinusNumbers:=True



Hazel wrote:

Hi All

I have just used the macro recorder for the code below is there a way to
send it to
sheet3 without copying and pasting.

Sub Tex2Col()

Sheets("Sheet2").Select
Range("A1:A150").Select
Selection.TextToColumns Destination:=Range("I1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=True, _
Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
:=Array(Array(1, 9), Array(2, 9), Array(3, 1), Array(4, 1)),
TrailingMinusNumbers:= _
True
Range("I1:J150").Select
Selection.Copy
Sheets("Sheet3").Select
Range("G2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
End Sub

--
Many Thanks

--

Dave Peterson

You could use a textbox, but then it would be up to you to validate the
input--and as a user (and a bad typist), I wouldn't want to type that path and
filename into a textbox.

How about using a label and a commandbutton. (You could use a textbox if you
want to give the user the ability to type in the name.) The commandbutton would
open the File|Open dialog box so I could choose the .csv file I wanted.

Option Explicit
Private Declare Function SetCurrentDirectoryA Lib _
"kernel32" (ByVal lpPathName As String) As Long
Sub ChDirNet(szPath As String)
Dim lReturn As Long
lReturn = SetCurrentDirectoryA(szPath)
If lReturn = 0 Then Err.Raise vbObjectError + 1, "Error setting path."
End Sub


This goes in a General module (not behind the userform):


And this is the code in the userform for that commandbutton:

Option Explicit
Private Sub CommandButton1_Click()

Dim myFileName As Variant
Dim myCurFolder As String
Dim myNewFolder As String
Dim myPathToDesktop As String
Dim myDesktopFolderName As String

myDesktopFolderName = "\myCSVFolder"

myCurFolder = CurDir

myPathToDesktop = CreateObject("WScript.Shell").SpecialFolders("Desk Top")
myNewFolder = myPathToDesktop & myDesktopFolderName

On Error Resume Next
ChDirNet myNewFolder
If Err.Number < 0 Then
'what should happen
MsgBox "Please change to your own folder"
Err.Clear
End If
On Error GoTo 0

myFileName = Application.GetOpenFilename(filefilter:="CSV Files, *.CSV", _
Title:="Pick a File")

ChDirNet myCurFolder

If myFileName = False Then
'user hit cancel
Me.Label1.Caption = ""
Else
Me.Label1.Caption = myFileName
End If

End Sub



Hazel wrote:

Hi Dave

Used your code and it worked a treat thank you for your help. May I take
this a stage further and ask you is it possible if I place a TextBox on the
UserForm and browse for the contents of the csv data -- I usually save all
csv data that I receive by Email to a CSV folder on the Desktop. Then use a
CommandButton to place it in Column A of Sheet2. Once again thanks for your
patience.
--
Many Thanks

"Dave Peterson" wrote:

You can use that Destination parm:

Worksheets("Sheet2").Range("A1:A150").TextToColumn s _
Destination:=Worksheets("sheet3").Range("G2"), _
DataType:=xlDelimited, _
textQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, _
Tab:=True, _
Semicolon:=False, _
Comma:=True, _
Space:=False, _
Other:=False, _
FieldInfo:=Array(Array(1, 9), Array(2, 9), Array(3, 1), Array(4, 1)), _
TrailingMinusNumbers:=True



Hazel wrote:

Hi All

I have just used the macro recorder for the code below is there a way to
send it to
sheet3 without copying and pasting.

Sub Tex2Col()

Sheets("Sheet2").Select
Range("A1:A150").Select
Selection.TextToColumns Destination:=Range("I1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=True, _
Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
:=Array(Array(1, 9), Array(2, 9), Array(3, 1), Array(4, 1)),
TrailingMinusNumbers:= _
True
Range("I1:J150").Select
Selection.Copy
Sheets("Sheet3").Select
Range("G2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
End Sub

--
Many Thanks

--

Dave Peterson


--

Dave Peterson

Hi Dave

I have inserted your first code in a new module ( Not The Sheet ) placed the
second code behind the UserForm not getting any joy or error messages --
where would I insert the Path details to the Desk top folder?

Going to be out of the office for the rest of today will have a look tonight
--
Many Thanks


"Dave Peterson" wrote:

You could use a textbox, but then it would be up to you to validate the
input--and as a user (and a bad typist), I wouldn't want to type that path and
filename into a textbox.

How about using a label and a commandbutton. (You could use a textbox if you
want to give the user the ability to type in the name.) The commandbutton would
open the File|Open dialog box so I could choose the .csv file I wanted.

Option Explicit
Private Declare Function SetCurrentDirectoryA Lib _
"kernel32" (ByVal lpPathName As String) As Long
Sub ChDirNet(szPath As String)
Dim lReturn As Long
lReturn = SetCurrentDirectoryA(szPath)
If lReturn = 0 Then Err.Raise vbObjectError + 1, "Error setting path."
End Sub


This goes in a General module (not behind the userform):


And this is the code in the userform for that commandbutton:

Option Explicit
Private Sub CommandButton1_Click()

Dim myFileName As Variant
Dim myCurFolder As String
Dim myNewFolder As String
Dim myPathToDesktop As String
Dim myDesktopFolderName As String

myDesktopFolderName = "\myCSVFolder"

myCurFolder = CurDir

myPathToDesktop = CreateObject("WScript.Shell").SpecialFolders("Desk Top")
myNewFolder = myPathToDesktop & myDesktopFolderName

On Error Resume Next
ChDirNet myNewFolder
If Err.Number < 0 Then
'what should happen
MsgBox "Please change to your own folder"
Err.Clear
End If
On Error GoTo 0

myFileName = Application.GetOpenFilename(filefilter:="CSV Files, *.CSV", _
Title:="Pick a File")

ChDirNet myCurFolder

If myFileName = False Then
'user hit cancel
Me.Label1.Caption = ""
Else
Me.Label1.Caption = myFileName
End If

End Sub



Hazel wrote:

Hi Dave

Used your code and it worked a treat thank you for your help. May I take
this a stage further and ask you is it possible if I place a TextBox on the
UserForm and browse for the contents of the csv data -- I usually save all
csv data that I receive by Email to a CSV folder on the Desktop. Then use a
CommandButton to place it in Column A of Sheet2. Once again thanks for your
patience.
--
Many Thanks

"Dave Peterson" wrote:

You can use that Destination parm:

Worksheets("Sheet2").Range("A1:A150").TextToColumn s _
Destination:=Worksheets("sheet3").Range("G2"), _
DataType:=xlDelimited, _
textQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, _
Tab:=True, _
Semicolon:=False, _
Comma:=True, _
Space:=False, _
Other:=False, _
FieldInfo:=Array(Array(1, 9), Array(2, 9), Array(3, 1), Array(4, 1)), _
TrailingMinusNumbers:=True



Hazel wrote:

Hi All

I have just used the macro recorder for the code below is there a way to
send it to
sheet3 without copying and pasting.

Sub Tex2Col()

Sheets("Sheet2").Select
Range("A1:A150").Select
Selection.TextToColumns Destination:=Range("I1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=True, _
Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
:=Array(Array(1, 9), Array(2, 9), Array(3, 1), Array(4, 1)),
TrailingMinusNumbers:= _
True
Range("I1:J150").Select
Selection.Copy
Sheets("Sheet3").Select
Range("G2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
End Sub

--
Many Thanks

--

Dave Peterson


--

Dave Peterson

these two lines (from Dave's code) get the path to the desktop:
myPathToDesktop = CreateObject("WScript.Shell").SpecialFolders("Desk Top")
myNewFolder = myPathToDesktop & myDesktopFolderName

you could replace myPathToDesktop with the exact address

does that answer your question?



"Hazel" wrote:

Hi Dave

I have inserted your first code in a new module ( Not The Sheet ) placed the
second code behind the UserForm not getting any joy or error messages --
where would I insert the Path details to the Desk top folder?

Going to be out of the office for the rest of today will have a look tonight
--
Many Thanks


"Dave Peterson" wrote:

You could use a textbox, but then it would be up to you to validate the
input--and as a user (and a bad typist), I wouldn't want to type that path and
filename into a textbox.

How about using a label and a commandbutton. (You could use a textbox if you
want to give the user the ability to type in the name.) The commandbutton would
open the File|Open dialog box so I could choose the .csv file I wanted.

Option Explicit
Private Declare Function SetCurrentDirectoryA Lib _
"kernel32" (ByVal lpPathName As String) As Long
Sub ChDirNet(szPath As String)
Dim lReturn As Long
lReturn = SetCurrentDirectoryA(szPath)
If lReturn = 0 Then Err.Raise vbObjectError + 1, "Error setting path."
End Sub


This goes in a General module (not behind the userform):


And this is the code in the userform for that commandbutton:

Option Explicit
Private Sub CommandButton1_Click()

Dim myFileName As Variant
Dim myCurFolder As String
Dim myNewFolder As String
Dim myPathToDesktop As String
Dim myDesktopFolderName As String

myDesktopFolderName = "\myCSVFolder"

myCurFolder = CurDir

myPathToDesktop = CreateObject("WScript.Shell").SpecialFolders("Desk Top")
myNewFolder = myPathToDesktop & myDesktopFolderName

On Error Resume Next
ChDirNet myNewFolder
If Err.Number < 0 Then
'what should happen
MsgBox "Please change to your own folder"
Err.Clear
End If
On Error GoTo 0

myFileName = Application.GetOpenFilename(filefilter:="CSV Files, *.CSV", _
Title:="Pick a File")

ChDirNet myCurFolder

If myFileName = False Then
'user hit cancel
Me.Label1.Caption = ""
Else
Me.Label1.Caption = myFileName
End If

End Sub



Hazel wrote:

Hi Dave

Used your code and it worked a treat thank you for your help. May I take
this a stage further and ask you is it possible if I place a TextBox on the
UserForm and browse for the contents of the csv data -- I usually save all
csv data that I receive by Email to a CSV folder on the Desktop. Then use a
CommandButton to place it in Column A of Sheet2. Once again thanks for your
patience.
--
Many Thanks

"Dave Peterson" wrote:

You can use that Destination parm:

Worksheets("Sheet2").Range("A1:A150").TextToColumn s _
Destination:=Worksheets("sheet3").Range("G2"), _
DataType:=xlDelimited, _
textQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, _
Tab:=True, _
Semicolon:=False, _
Comma:=True, _
Space:=False, _
Other:=False, _
FieldInfo:=Array(Array(1, 9), Array(2, 9), Array(3, 1), Array(4, 1)), _
TrailingMinusNumbers:=True



Hazel wrote:

Hi All

I have just used the macro recorder for the code below is there a way to
send it to
sheet3 without copying and pasting.

Sub Tex2Col()

Sheets("Sheet2").Select
Range("A1:A150").Select
Selection.TextToColumns Destination:=Range("I1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=True, _
Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
:=Array(Array(1, 9), Array(2, 9), Array(3, 1), Array(4, 1)),
TrailingMinusNumbers:= _
True
Range("I1:J150").Select
Selection.Copy
Sheets("Sheet3").Select
Range("G2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
End Sub

--
Many Thanks

--

Dave Peterson


--

Dave Peterson

aha! thanks Dave

"Dave Peterson" wrote in message
...
You can use that Destination parm:

Worksheets("Sheet2").Range("A1:A150").TextToColumn s _
Destination:=Worksheets("sheet3").Range("G2"), _
DataType:=xlDelimited, _
textQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, _
Tab:=True, _
Semicolon:=False, _
Comma:=True, _
Space:=False, _
Other:=False, _
FieldInfo:=Array(Array(1, 9), Array(2, 9), Array(3, 1), Array(4, 1)), _
TrailingMinusNumbers:=True



Hazel wrote:

Hi All

I have just used the macro recorder for the code below is there a way to
send it to
sheet3 without copying and pasting.

Sub Tex2Col()

Sheets("Sheet2").Select
Range("A1:A150").Select
Selection.TextToColumns Destination:=Range("I1"),
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=True, _
Semicolon:=False, Comma:=True, Space:=False, Other:=False,
FieldInfo _
:=Array(Array(1, 9), Array(2, 9), Array(3, 1), Array(4, 1)),
TrailingMinusNumbers:= _
True
Range("I1:J150").Select
Selection.Copy
Sheets("Sheet3").Select
Range("G2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
End Sub

--
Many Thanks

--

Dave Peterson