ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Text To Columns On A Different Sheet (https://www.excelbanter.com/excel-programming/433333-text-columns-different-sheet.html)

hazel
Text To Columns On A Different Sheet
 
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

Patrick Molloy[_2_]
Text To Columns On A Different Sheet
 
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

Dave Peterson
Text To Columns On A Different Sheet
 
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

hazel
Text To Columns On A Different Sheet
 
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

Patrick Molloy
Text To Columns On A Different Sheet
 
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


Dave Peterson
Text To Columns On A Different Sheet
 
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

hazel
Text To Columns On A Different Sheet
 
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

Patrick Molloy[_2_]
Text To Columns On A Different Sheet
 
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

Dave Peterson
Text To Columns On A Different Sheet
 
I declared that mydesktopfoldername as a variable so that Hazel would only have
to change this line:

myDesktopFolderName = "\myCSVFolder"

(Include that leading backslash!)



Patrick Molloy wrote:

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


--

Dave Peterson

hazel
Text To Columns On A Different Sheet
 
Hi Dave

Back early which is very unusual

Code below is exactly as in the module getting this error message

Compile Error on this line myDesktopFolderName = C:\Documents and
Settings\Hazel Jones.HAZE-5A4051ECC\Desktop\myCSVFolder

Expected: line number or label or statement or end of statement


Private Sub Add3_Click()

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

myDesktopFolderName = C:\Documents and Settings\Hazel
Jones.HAZE-5A4051ECC\Desktop\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
--
Many Thanks


"Dave Peterson" wrote:

I declared that mydesktopfoldername as a variable so that Hazel would only have
to change this line:

myDesktopFolderName = "\myCSVFolder"

(Include that leading backslash!)



Patrick Molloy wrote:

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


--

Dave Peterson

Dave Peterson
Text To Columns On A Different Sheet
 
This line:
myPathToDesktop = CreateObject("WScript.Shell").SpecialFolders("Desk Top")
actually returns this:
C:\Documents and Settings\Hazel Jones.HAZE-5A4051ECC\Desktop
so you don't have to type it.
(And it's smart enough to figure out the location/name of the desktop for anyone
running the macro!)

This is the line you would want to change:
myDesktopFolderName = "\myCSVFolder"

What's the name of the folder that's on your desktop.
If it's really "MyCSVFolder", then you don't have to change anything.

But if it's really "Hazel's Folder that contains CSV Files"
you want to change that line to:
myDesktopFolderName = "\Hazel's Folder that contains CSV Files"
(Include that leading backslash!)







Hazel wrote:

Hi Dave

Back early which is very unusual

Code below is exactly as in the module getting this error message

Compile Error on this line myDesktopFolderName = C:\Documents and
Settings\Hazel Jones.HAZE-5A4051ECC\Desktop\myCSVFolder

Expected: line number or label or statement or end of statement

Private Sub Add3_Click()

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

myDesktopFolderName = C:\Documents and Settings\Hazel
Jones.HAZE-5A4051ECC\Desktop\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
--
Many Thanks

"Dave Peterson" wrote:

I declared that mydesktopfoldername as a variable so that Hazel would only have
to change this line:

myDesktopFolderName = "\myCSVFolder"

(Include that leading backslash!)



Patrick Molloy wrote:

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


--

Dave Peterson


--

Dave Peterson

hazel
Text To Columns On A Different Sheet
 
Hi Dave

Spent a couple of hours trying but I cannot get it to open the file -- tried
all different ways of trying to open it with the code all to no avail

This is the line you would want to change:
myDesktopFolderName = "\myCSVFolder"
changed to myDesktopFolderName = \myCSVFolder No joy

folder on desktop named myCSVFolder

Now I'm really lost

Early start tomorrow will have to leave it for today
--
Many Thanks


"Dave Peterson" wrote:

This line:
myPathToDesktop = CreateObject("WScript.Shell").SpecialFolders("Desk Top")
actually returns this:
C:\Documents and Settings\Hazel Jones.HAZE-5A4051ECC\Desktop
so you don't have to type it.
(And it's smart enough to figure out the location/name of the desktop for anyone
running the macro!)

This is the line you would want to change:
myDesktopFolderName = "\myCSVFolder"

What's the name of the folder that's on your desktop.
If it's really "MyCSVFolder", then you don't have to change anything.

But if it's really "Hazel's Folder that contains CSV Files"
you want to change that line to:
myDesktopFolderName = "\Hazel's Folder that contains CSV Files"
(Include that leading backslash!)







Hazel wrote:

Hi Dave

Back early which is very unusual

Code below is exactly as in the module getting this error message

Compile Error on this line myDesktopFolderName = C:\Documents and
Settings\Hazel Jones.HAZE-5A4051ECC\Desktop\myCSVFolder

Expected: line number or label or statement or end of statement

Private Sub Add3_Click()

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

myDesktopFolderName = C:\Documents and Settings\Hazel
Jones.HAZE-5A4051ECC\Desktop\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
--
Many Thanks

"Dave Peterson" wrote:

I declared that mydesktopfoldername as a variable so that Hazel would only have
to change this line:

myDesktopFolderName = "\myCSVFolder"

(Include that leading backslash!)



Patrick Molloy wrote:

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


--

Dave Peterson


--

Dave Peterson

Dave Peterson
Text To Columns On A Different Sheet
 
First, you need to surround your text string with double quotes:

myDesktopFolderName = "\myCSVFolder"

Second, the code I provided doesn't open the file. It just gets the name of the
file to be opened from the user.

I thought that you already had code that would open your .CSV file if you
specified the filename. Something like:

Workbooks.Open Filename:="C:\My Documents\Excel\book1.csv"

You'll want to verify that the label (or textbox) are not blank and then use
something like:

Workbooks.Open Filename:=Me.Label1.Caption




Hazel wrote:

Hi Dave

Spent a couple of hours trying but I cannot get it to open the file -- tried
all different ways of trying to open it with the code all to no avail

This is the line you would want to change:
myDesktopFolderName = "\myCSVFolder"
changed to myDesktopFolderName = \myCSVFolder No joy

folder on desktop named myCSVFolder

Now I'm really lost

Early start tomorrow will have to leave it for today
--
Many Thanks

"Dave Peterson" wrote:

This line:
myPathToDesktop = CreateObject("WScript.Shell").SpecialFolders("Desk Top")
actually returns this:
C:\Documents and Settings\Hazel Jones.HAZE-5A4051ECC\Desktop
so you don't have to type it.
(And it's smart enough to figure out the location/name of the desktop for anyone
running the macro!)

This is the line you would want to change:
myDesktopFolderName = "\myCSVFolder"

What's the name of the folder that's on your desktop.
If it's really "MyCSVFolder", then you don't have to change anything.

But if it's really "Hazel's Folder that contains CSV Files"
you want to change that line to:
myDesktopFolderName = "\Hazel's Folder that contains CSV Files"
(Include that leading backslash!)







Hazel wrote:

Hi Dave

Back early which is very unusual

Code below is exactly as in the module getting this error message

Compile Error on this line myDesktopFolderName = C:\Documents and
Settings\Hazel Jones.HAZE-5A4051ECC\Desktop\myCSVFolder

Expected: line number or label or statement or end of statement

Private Sub Add3_Click()

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

myDesktopFolderName = C:\Documents and Settings\Hazel
Jones.HAZE-5A4051ECC\Desktop\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
--
Many Thanks

"Dave Peterson" wrote:

I declared that mydesktopfoldername as a variable so that Hazel would only have
to change this line:

myDesktopFolderName = "\myCSVFolder"

(Include that leading backslash!)



Patrick Molloy wrote:

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


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


All times are GMT +1. The time now is 02:14 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com