Text To Columns On A Different Sheet

[Hazel]

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

 

[Patrick Molloy]

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

 

[Dave Peterson]

You can use that Destination parm:

Worksheets("Sheet2").Range("A1:A150").TextToColumns _
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]

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.

 

[Patrick Molloy]

aha! thanks Dave

You can use that Destination parm:

Worksheets("Sheet2").Range("A1:A150").TextToColumns _
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

 

[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("DeskTop")
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

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.

 

[Hazel]

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

 

[Patrick Molloy]

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

you could replace myPathToDesktop with the exact address

does that answer your question?

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

 

[Dave Peterson]

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

myDesktopFolderName = "\myCSVFolder"

(Include that leading backslash!)

 

[Hazel]

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("DeskTop")
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

 

[Dave Peterson]

This line:
myPathToDesktop = CreateObject("WScript.Shell").SpecialFolders("DeskTop")
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!)

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("DeskTop")
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

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

myDesktopFolderName = "\myCSVFolder"

(Include that leading backslash!)

 

[Hazel]

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

This line:
myPathToDesktop = CreateObject("WScript.Shell").SpecialFolders("DeskTop")
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!)

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("DeskTop")
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

 

[Dave Peterson]

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

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