Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to make columns in one sheet match columns from another sheet | Excel Discussion (Misc queries) | |||
2 Small VBA Questions; Text To Columns and Naming First Sheet | Excel Discussion (Misc queries) | |||
TEXT TO COLUMNS PROTECTED SHEET | Excel Discussion (Misc queries) | |||
Linking text columns with text and data columns | Excel Worksheet Functions | |||
extracting text from within a cell - 'text to rows@ equivalent of 'text to columns' | Excel Programming |