|
Chip Pearson Import/Export Macros
Hi,
I need help on the Macros posted on chip's web site at http://www.cpearson.com/excel/imptext.htm (I am a newbie at VB.) I have basically copied and pasted the 2 macros for importing-each in a separate module and saved in a file (importtotext.xls). When I open the file and then minimize it, if I have another file open into which I want to import text data, I run the macro and it works. (I may not even be doing this right.) Doing the same for the EXPORT macro does not work. I just copied and pasted into a module each of the EXPORT macros but no joy--I get error messages such as "delimiter" expected here, or parameter expected etc. I was wondering if anyone could help me figure it out as I could REALLY use the export macros! Tx a million. S |
Chip Pearson Import/Export Macros
Post the code you are using to call the export macro.
-- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "saturnin02" <saturnin02_at_hotmail.com wrote in message ... Hi, I need help on the Macros posted on chip's web site at http://www.cpearson.com/excel/imptext.htm (I am a newbie at VB.) I have basically copied and pasted the 2 macros for importing-each in a separate module and saved in a file (importtotext.xls). When I open the file and then minimize it, if I have another file open into which I want to import text data, I run the macro and it works. (I may not even be doing this right.) Doing the same for the EXPORT macro does not work. I just copied and pasted into a module each of the EXPORT macros but no joy--I get error messages such as "delimiter" expected here, or parameter expected etc. I was wondering if anyone could help me figure it out as I could REALLY use the export macros! Tx a million. S |
Chip Pearson Import/Export Macros
Where did you install the macros, same workbook ?
Where do you use the macros, same workbook ? If you use the macro from a different workbook that workbook should be hidden if you want access to the macros without specifying the workbook name. Excel Window menu, hide files More information in http://www.mvps.org/dmcritchie/excel/install.htm I am assuming you copied Chip's code without changes. Are the two macro in the same module if they are and one works then they both in standard modules and neither is in a class module. --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "saturnin02" <saturnin02_at_hotmail.com wrote in message ... Hi, I need help on the Macros posted on chip's web site at http://www.cpearson.com/excel/imptext.htm (I am a newbie at VB.) I have basically copied and pasted the 2 macros for importing-each in a separate module and saved in a file (importtotext.xls). When I open the file and then minimize it, if I have another file open into which I want to import text data, I run the macro and it works. (I may not even be doing this right.) Doing the same for the EXPORT macro does not work. I just copied and pasted into a module each of the EXPORT macros but no joy--I get error messages such as "delimiter" expected here, or parameter expected etc. I was wondering if anyone could help me figure it out as I could REALLY use the export macros! Tx a million. S |
Chip Pearson Import/Export Macros
Chip, Bear with me but I do not understand.
Do you mean in the same workbook as the import one, separate workbook ..... Which macro first, etc. I am really a newb--please help me get going. TX. S "Chip Pearson" wrote in message ... Post the code you are using to call the export macro. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "saturnin02" <saturnin02_at_hotmail.com wrote in message ... Hi, I need help on the Macros posted on chip's web site at http://www.cpearson.com/excel/imptext.htm (I am a newbie at VB.) I have basically copied and pasted the 2 macros for importing-each in a separate module and saved in a file (importtotext.xls). When I open the file and then minimize it, if I have another file open into which I want to import text data, I run the macro and it works. (I may not even be doing this right.) Doing the same for the EXPORT macro does not work. I just copied and pasted into a module each of the EXPORT macros but no joy--I get error messages such as "delimiter" expected here, or parameter expected etc. I was wondering if anyone could help me figure it out as I could REALLY use the export macros! Tx a million. S |
Chip Pearson Import/Export Macros
Sorry Chip--U didn't realize you meant post it HERE.
I am just using is AS IS on your site. Public Sub ExportToTextFile(FName As String, _ Sep As String, SelectionOnly As Boolean) Dim WholeLine As String Dim FNum As Integer Dim RowNdx As Long Dim ColNdx As Integer Dim StartRow As Long Dim EndRow As Long Dim StartCol As Integer Dim EndCol As Integer Dim CellValue As String Application.ScreenUpdating = False On Error GoTo EndMacro: FNum = FreeFile If SelectionOnly = True Then With Selection StartRow = .Cells(1).row StartCol = .Cells(1).Column EndRow = .Cells(.Cells.Count).row EndCol = .Cells(.Cells.Count).Column End With Else With ActiveSheet.UsedRange StartRow = .Cells(1).row StartCol = .Cells(1).Column EndRow = .Cells(.Cells.Count).row EndCol = .Cells(.Cells.Count).Column End With End If Open FName For Output Access Write As #FNum For RowNdx = StartRow To EndRow WholeLine = "" For ColNdx = StartCol To EndCol If Cells(RowNdx, ColNdx).Value = "" Then CellValue = Chr(34) & Chr(34) Else CellValue = _ Application.WorksheetFunction.Text _ (Cells(RowNdx, ColNdx).Value, _ Cells(RowNdx, ColNdx).NumberFormat) End If WholeLine = WholeLine & CellValue & Sep Next ColNdx WholeLine = Left(WholeLine, Len(WholeLine) - Len(Sep)) Print #FNum, WholeLine Next RowNdx EndMacro: On Error GoTo 0 Application.ScreenUpdating = True Close #FNum End Sub And the other one is: Public Sub DoTheExport() Dim FName As Variant Dim Sep As String FName = Application.GetSaveAsFileName() If FName = False Then MsgBox "You didn't select a file" Exit Sub End If Sep = InputBox("Enter a single delimiter character (e.g., comma or semi-colon)", _ "Export To Text File") ExportToTextFile CStr(FName), Sep, _ MsgBox("Do You Want To Export The Entire Worksheet?", _ vbYesNo, "Export To Text File") = vbNo End Sub I am sure I need to DEFINE some parameters here is that correct? S "saturnin02" <saturnin02_at_hotmail.com wrote in message ... Chip, Bear with me but I do not understand. Do you mean in the same workbook as the import one, separate workbook ...... Which macro first, etc. I am really a newb--please help me get going. TX. S "Chip Pearson" wrote in message ... Post the code you are using to call the export macro. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "saturnin02" <saturnin02_at_hotmail.com wrote in message ... Hi, I need help on the Macros posted on chip's web site at http://www.cpearson.com/excel/imptext.htm (I am a newbie at VB.) I have basically copied and pasted the 2 macros for importing-each in a separate module and saved in a file (importtotext.xls). When I open the file and then minimize it, if I have another file open into which I want to import text data, I run the macro and it works. (I may not even be doing this right.) Doing the same for the EXPORT macro does not work. I just copied and pasted into a module each of the EXPORT macros but no joy--I get error messages such as "delimiter" expected here, or parameter expected etc. I was wondering if anyone could help me figure it out as I could REALLY use the export macros! Tx a million. S |
Chip Pearson Import/Export Macros
The code works for me just fine. In the Public Sub
ExportToTextFile declaration, ensure that 'Sep As String' and 'SelectionOnly As Boolean' are on the same line. You may be suffering from line break problems caused by copy/paste. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "saturnin02" <saturnin02_at_hotmail.com wrote in message ... Sorry Chip--U didn't realize you meant post it HERE. I am just using is AS IS on your site. Public Sub ExportToTextFile(FName As String, _ Sep As String, SelectionOnly As Boolean) Dim WholeLine As String Dim FNum As Integer Dim RowNdx As Long Dim ColNdx As Integer Dim StartRow As Long Dim EndRow As Long Dim StartCol As Integer Dim EndCol As Integer Dim CellValue As String Application.ScreenUpdating = False On Error GoTo EndMacro: FNum = FreeFile If SelectionOnly = True Then With Selection StartRow = .Cells(1).row StartCol = .Cells(1).Column EndRow = .Cells(.Cells.Count).row EndCol = .Cells(.Cells.Count).Column End With Else With ActiveSheet.UsedRange StartRow = .Cells(1).row StartCol = .Cells(1).Column EndRow = .Cells(.Cells.Count).row EndCol = .Cells(.Cells.Count).Column End With End If Open FName For Output Access Write As #FNum For RowNdx = StartRow To EndRow WholeLine = "" For ColNdx = StartCol To EndCol If Cells(RowNdx, ColNdx).Value = "" Then CellValue = Chr(34) & Chr(34) Else CellValue = _ Application.WorksheetFunction.Text _ (Cells(RowNdx, ColNdx).Value, _ Cells(RowNdx, ColNdx).NumberFormat) End If WholeLine = WholeLine & CellValue & Sep Next ColNdx WholeLine = Left(WholeLine, Len(WholeLine) - Len(Sep)) Print #FNum, WholeLine Next RowNdx EndMacro: On Error GoTo 0 Application.ScreenUpdating = True Close #FNum End Sub And the other one is: Public Sub DoTheExport() Dim FName As Variant Dim Sep As String FName = Application.GetSaveAsFileName() If FName = False Then MsgBox "You didn't select a file" Exit Sub End If Sep = InputBox("Enter a single delimiter character (e.g., comma or semi-colon)", _ "Export To Text File") ExportToTextFile CStr(FName), Sep, _ MsgBox("Do You Want To Export The Entire Worksheet?", _ vbYesNo, "Export To Text File") = vbNo End Sub I am sure I need to DEFINE some parameters here is that correct? S "saturnin02" <saturnin02_at_hotmail.com wrote in message ... Chip, Bear with me but I do not understand. Do you mean in the same workbook as the import one, separate workbook ..... Which macro first, etc. I am really a newb--please help me get going. TX. S "Chip Pearson" wrote in message ... Post the code you are using to call the export macro. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "saturnin02" <saturnin02_at_hotmail.com wrote in message ... Hi, I need help on the Macros posted on chip's web site at http://www.cpearson.com/excel/imptext.htm (I am a newbie at VB.) I have basically copied and pasted the 2 macros for importing-each in a separate module and saved in a file (importtotext.xls). When I open the file and then minimize it, if I have another file open into which I want to import text data, I run the macro and it works. (I may not even be doing this right.) Doing the same for the EXPORT macro does not work. I just copied and pasted into a module each of the EXPORT macros but no joy--I get error messages such as "delimiter" expected here, or parameter expected etc. I was wondering if anyone could help me figure it out as I could REALLY use the export macros! Tx a million. S |
Chip Pearson Import/Export Macros
Chip,
You were absolutely right! I put the 'Sep As String' and 'SelectionOnly As Boolean' on the same line and it works. What is the "_" at the end or within some of the lines? Tx for your help! S "Chip Pearson" wrote in message ... The code works for me just fine. In the Public Sub ExportToTextFile declaration, ensure that 'Sep As String' and 'SelectionOnly As Boolean' are on the same line. You may be suffering from line break problems caused by copy/paste. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "saturnin02" <saturnin02_at_hotmail.com wrote in message ... Sorry Chip--U didn't realize you meant post it HERE. I am just using is AS IS on your site. Public Sub ExportToTextFile(FName As String, _ Sep As String, SelectionOnly As Boolean) Dim WholeLine As String Dim FNum As Integer Dim RowNdx As Long Dim ColNdx As Integer Dim StartRow As Long Dim EndRow As Long Dim StartCol As Integer Dim EndCol As Integer Dim CellValue As String Application.ScreenUpdating = False On Error GoTo EndMacro: FNum = FreeFile If SelectionOnly = True Then With Selection StartRow = .Cells(1).row StartCol = .Cells(1).Column EndRow = .Cells(.Cells.Count).row EndCol = .Cells(.Cells.Count).Column End With Else With ActiveSheet.UsedRange StartRow = .Cells(1).row StartCol = .Cells(1).Column EndRow = .Cells(.Cells.Count).row EndCol = .Cells(.Cells.Count).Column End With End If Open FName For Output Access Write As #FNum For RowNdx = StartRow To EndRow WholeLine = "" For ColNdx = StartCol To EndCol If Cells(RowNdx, ColNdx).Value = "" Then CellValue = Chr(34) & Chr(34) Else CellValue = _ Application.WorksheetFunction.Text _ (Cells(RowNdx, ColNdx).Value, _ Cells(RowNdx, ColNdx).NumberFormat) End If WholeLine = WholeLine & CellValue & Sep Next ColNdx WholeLine = Left(WholeLine, Len(WholeLine) - Len(Sep)) Print #FNum, WholeLine Next RowNdx EndMacro: On Error GoTo 0 Application.ScreenUpdating = True Close #FNum End Sub And the other one is: Public Sub DoTheExport() Dim FName As Variant Dim Sep As String FName = Application.GetSaveAsFileName() If FName = False Then MsgBox "You didn't select a file" Exit Sub End If Sep = InputBox("Enter a single delimiter character (e.g., comma or semi-colon)", _ "Export To Text File") ExportToTextFile CStr(FName), Sep, _ MsgBox("Do You Want To Export The Entire Worksheet?", _ vbYesNo, "Export To Text File") = vbNo End Sub I am sure I need to DEFINE some parameters here is that correct? S "saturnin02" <saturnin02_at_hotmail.com wrote in message ... Chip, Bear with me but I do not understand. Do you mean in the same workbook as the import one, separate workbook ..... Which macro first, etc. I am really a newb--please help me get going. TX. S "Chip Pearson" wrote in message ... Post the code you are using to call the export macro. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "saturnin02" <saturnin02_at_hotmail.com wrote in message ... Hi, I need help on the Macros posted on chip's web site at http://www.cpearson.com/excel/imptext.htm (I am a newbie at VB.) I have basically copied and pasted the 2 macros for importing-each in a separate module and saved in a file (importtotext.xls). When I open the file and then minimize it, if I have another file open into which I want to import text data, I run the macro and it works. (I may not even be doing this right.) Doing the same for the EXPORT macro does not work. I just copied and pasted into a module each of the EXPORT macros but no joy--I get error messages such as "delimiter" expected here, or parameter expected etc. I was wondering if anyone could help me figure it out as I could REALLY use the export macros! Tx a million. S |
Chip Pearson Import/Export Macros
Chip,
One last thing. How do I modify the Export Macro so that when it asks me to Save File As--it automatically puts the extension ".txt" there instead of .xls? Tx, S "Chip Pearson" wrote in message ... The code works for me just fine. In the Public Sub ExportToTextFile declaration, ensure that 'Sep As String' and 'SelectionOnly As Boolean' are on the same line. You may be suffering from line break problems caused by copy/paste. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "saturnin02" <saturnin02_at_hotmail.com wrote in message ... Sorry Chip--U didn't realize you meant post it HERE. I am just using is AS IS on your site. Public Sub ExportToTextFile(FName As String, _ Sep As String, SelectionOnly As Boolean) Dim WholeLine As String Dim FNum As Integer Dim RowNdx As Long Dim ColNdx As Integer Dim StartRow As Long Dim EndRow As Long Dim StartCol As Integer Dim EndCol As Integer Dim CellValue As String Application.ScreenUpdating = False On Error GoTo EndMacro: FNum = FreeFile If SelectionOnly = True Then With Selection StartRow = .Cells(1).row StartCol = .Cells(1).Column EndRow = .Cells(.Cells.Count).row EndCol = .Cells(.Cells.Count).Column End With Else With ActiveSheet.UsedRange StartRow = .Cells(1).row StartCol = .Cells(1).Column EndRow = .Cells(.Cells.Count).row EndCol = .Cells(.Cells.Count).Column End With End If Open FName For Output Access Write As #FNum For RowNdx = StartRow To EndRow WholeLine = "" For ColNdx = StartCol To EndCol If Cells(RowNdx, ColNdx).Value = "" Then CellValue = Chr(34) & Chr(34) Else CellValue = _ Application.WorksheetFunction.Text _ (Cells(RowNdx, ColNdx).Value, _ Cells(RowNdx, ColNdx).NumberFormat) End If WholeLine = WholeLine & CellValue & Sep Next ColNdx WholeLine = Left(WholeLine, Len(WholeLine) - Len(Sep)) Print #FNum, WholeLine Next RowNdx EndMacro: On Error GoTo 0 Application.ScreenUpdating = True Close #FNum End Sub And the other one is: Public Sub DoTheExport() Dim FName As Variant Dim Sep As String FName = Application.GetSaveAsFileName() If FName = False Then MsgBox "You didn't select a file" Exit Sub End If Sep = InputBox("Enter a single delimiter character (e.g., comma or semi-colon)", _ "Export To Text File") ExportToTextFile CStr(FName), Sep, _ MsgBox("Do You Want To Export The Entire Worksheet?", _ vbYesNo, "Export To Text File") = vbNo End Sub I am sure I need to DEFINE some parameters here is that correct? S "saturnin02" <saturnin02_at_hotmail.com wrote in message ... Chip, Bear with me but I do not understand. Do you mean in the same workbook as the import one, separate workbook ..... Which macro first, etc. I am really a newb--please help me get going. TX. S "Chip Pearson" wrote in message ... Post the code you are using to call the export macro. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "saturnin02" <saturnin02_at_hotmail.com wrote in message ... Hi, I need help on the Macros posted on chip's web site at http://www.cpearson.com/excel/imptext.htm (I am a newbie at VB.) I have basically copied and pasted the 2 macros for importing-each in a separate module and saved in a file (importtotext.xls). When I open the file and then minimize it, if I have another file open into which I want to import text data, I run the macro and it works. (I may not even be doing this right.) Doing the same for the EXPORT macro does not work. I just copied and pasted into a module each of the EXPORT macros but no joy--I get error messages such as "delimiter" expected here, or parameter expected etc. I was wondering if anyone could help me figure it out as I could REALLY use the export macros! Tx a million. S |
Chip Pearson Import/Export Macros
The " _" characters (note the leading space before the
underscore) indicate that a line of code is continued on the next line of text in the file. You can use this to split logical lines of code across several physical lines of text in order to improve readability. They have no effect on the execution of the code. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "saturnin02" <saturnin02_at_hotmail.com wrote in message ... Chip, You were absolutely right! I put the 'Sep As String' and 'SelectionOnly As Boolean' on the same line and it works. What is the "_" at the end or within some of the lines? Tx for your help! S "Chip Pearson" wrote in message ... The code works for me just fine. In the Public Sub ExportToTextFile declaration, ensure that 'Sep As String' and 'SelectionOnly As Boolean' are on the same line. You may be suffering from line break problems caused by copy/paste. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "saturnin02" <saturnin02_at_hotmail.com wrote in message ... Sorry Chip--U didn't realize you meant post it HERE. I am just using is AS IS on your site. Public Sub ExportToTextFile(FName As String, _ Sep As String, SelectionOnly As Boolean) Dim WholeLine As String Dim FNum As Integer Dim RowNdx As Long Dim ColNdx As Integer Dim StartRow As Long Dim EndRow As Long Dim StartCol As Integer Dim EndCol As Integer Dim CellValue As String Application.ScreenUpdating = False On Error GoTo EndMacro: FNum = FreeFile If SelectionOnly = True Then With Selection StartRow = .Cells(1).row StartCol = .Cells(1).Column EndRow = .Cells(.Cells.Count).row EndCol = .Cells(.Cells.Count).Column End With Else With ActiveSheet.UsedRange StartRow = .Cells(1).row StartCol = .Cells(1).Column EndRow = .Cells(.Cells.Count).row EndCol = .Cells(.Cells.Count).Column End With End If Open FName For Output Access Write As #FNum For RowNdx = StartRow To EndRow WholeLine = "" For ColNdx = StartCol To EndCol If Cells(RowNdx, ColNdx).Value = "" Then CellValue = Chr(34) & Chr(34) Else CellValue = _ Application.WorksheetFunction.Text _ (Cells(RowNdx, ColNdx).Value, _ Cells(RowNdx, ColNdx).NumberFormat) End If WholeLine = WholeLine & CellValue & Sep Next ColNdx WholeLine = Left(WholeLine, Len(WholeLine) - Len(Sep)) Print #FNum, WholeLine Next RowNdx EndMacro: On Error GoTo 0 Application.ScreenUpdating = True Close #FNum End Sub And the other one is: Public Sub DoTheExport() Dim FName As Variant Dim Sep As String FName = Application.GetSaveAsFileName() If FName = False Then MsgBox "You didn't select a file" Exit Sub End If Sep = InputBox("Enter a single delimiter character (e.g., comma or semi-colon)", _ "Export To Text File") ExportToTextFile CStr(FName), Sep, _ MsgBox("Do You Want To Export The Entire Worksheet?", _ vbYesNo, "Export To Text File") = vbNo End Sub I am sure I need to DEFINE some parameters here is that correct? S "saturnin02" <saturnin02_at_hotmail.com wrote in message ... Chip, Bear with me but I do not understand. Do you mean in the same workbook as the import one, separate workbook ..... Which macro first, etc. I am really a newb--please help me get going. TX. S "Chip Pearson" wrote in message ... Post the code you are using to call the export macro. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "saturnin02" <saturnin02_at_hotmail.com wrote in message ... Hi, I need help on the Macros posted on chip's web site at http://www.cpearson.com/excel/imptext.htm (I am a newbie at VB.) I have basically copied and pasted the 2 macros for importing-each in a separate module and saved in a file (importtotext.xls). When I open the file and then minimize it, if I have another file open into which I want to import text data, I run the macro and it works. (I may not even be doing this right.) Doing the same for the EXPORT macro does not work. I just copied and pasted into a module each of the EXPORT macros but no joy--I get error messages such as "delimiter" expected here, or parameter expected etc. I was wondering if anyone could help me figure it out as I could REALLY use the export macros! Tx a million. S |
Chip Pearson Import/Export Macros
The underscore is a continuation character and is used
to indicate that the next line is a continuation. You should have gotten a syntax error and line(s) in the macro in RED. You will frequently have such problems in copying a macro from a newsposting due to line wrap. But this should not happen when copying from a web page. The web page should have been fixed after telling you what was wrong with your (adopted) code. Pasting macro code into a module sheet (XL95), or into VBA Editor (XL97 & XL2000) http://www.mvps.org/dmcritchie/excel/install.htm You want to make both the Excel and the web source windows as wide as possible. Pasted lines or any other lines turn RED if there is a syntax or other easily recognizable problem in Excel. An underscore at the end of a line indicates a continuation. Splitting a line without the continuation character is the most frequent syntax problem and code lines turn red as soon as continuation or other syntax errors are detected. Actually the continuation character is really two characters, a space followed by an underscore. --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "saturnin02" <saturnin02_at_hotmail.com wrote in message ... Chip, You were absolutely right! I put the 'Sep As String' and 'SelectionOnly As Boolean' on the same line and it works. What is the "_" at the end or within some of the lines? Tx for your help! |
Chip Pearson Import/Export Macros
Tx CHip.
So theoretically, with the " _", I should not run into the previous line break problem. Correct? S "Chip Pearson" wrote in message ... The " _" characters (note the leading space before the underscore) indicate that a line of code is continued on the next line of text in the file. You can use this to split logical lines of code across several physical lines of text in order to improve readability. They have no effect on the execution of the code. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "saturnin02" <saturnin02_at_hotmail.com wrote in message ... Chip, You were absolutely right! I put the 'Sep As String' and 'SelectionOnly As Boolean' on the same line and it works. What is the "_" at the end or within some of the lines? Tx for your help! S "Chip Pearson" wrote in message ... The code works for me just fine. In the Public Sub ExportToTextFile declaration, ensure that 'Sep As String' and 'SelectionOnly As Boolean' are on the same line. You may be suffering from line break problems caused by copy/paste. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "saturnin02" <saturnin02_at_hotmail.com wrote in message ... Sorry Chip--U didn't realize you meant post it HERE. I am just using is AS IS on your site. Public Sub ExportToTextFile(FName As String, _ Sep As String, SelectionOnly As Boolean) Dim WholeLine As String Dim FNum As Integer Dim RowNdx As Long Dim ColNdx As Integer Dim StartRow As Long Dim EndRow As Long Dim StartCol As Integer Dim EndCol As Integer Dim CellValue As String Application.ScreenUpdating = False On Error GoTo EndMacro: FNum = FreeFile If SelectionOnly = True Then With Selection StartRow = .Cells(1).row StartCol = .Cells(1).Column EndRow = .Cells(.Cells.Count).row EndCol = .Cells(.Cells.Count).Column End With Else With ActiveSheet.UsedRange StartRow = .Cells(1).row StartCol = .Cells(1).Column EndRow = .Cells(.Cells.Count).row EndCol = .Cells(.Cells.Count).Column End With End If Open FName For Output Access Write As #FNum For RowNdx = StartRow To EndRow WholeLine = "" For ColNdx = StartCol To EndCol If Cells(RowNdx, ColNdx).Value = "" Then CellValue = Chr(34) & Chr(34) Else CellValue = _ Application.WorksheetFunction.Text _ (Cells(RowNdx, ColNdx).Value, _ Cells(RowNdx, ColNdx).NumberFormat) End If WholeLine = WholeLine & CellValue & Sep Next ColNdx WholeLine = Left(WholeLine, Len(WholeLine) - Len(Sep)) Print #FNum, WholeLine Next RowNdx EndMacro: On Error GoTo 0 Application.ScreenUpdating = True Close #FNum End Sub And the other one is: Public Sub DoTheExport() Dim FName As Variant Dim Sep As String FName = Application.GetSaveAsFileName() If FName = False Then MsgBox "You didn't select a file" Exit Sub End If Sep = InputBox("Enter a single delimiter character (e.g., comma or semi-colon)", _ "Export To Text File") ExportToTextFile CStr(FName), Sep, _ MsgBox("Do You Want To Export The Entire Worksheet?", _ vbYesNo, "Export To Text File") = vbNo End Sub I am sure I need to DEFINE some parameters here is that correct? S "saturnin02" <saturnin02_at_hotmail.com wrote in message ... Chip, Bear with me but I do not understand. Do you mean in the same workbook as the import one, separate workbook ..... Which macro first, etc. I am really a newb--please help me get going. TX. S "Chip Pearson" wrote in message ... Post the code you are using to call the export macro. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "saturnin02" <saturnin02_at_hotmail.com wrote in message ... Hi, I need help on the Macros posted on chip's web site at http://www.cpearson.com/excel/imptext.htm (I am a newbie at VB.) I have basically copied and pasted the 2 macros for importing-each in a separate module and saved in a file (importtotext.xls). When I open the file and then minimize it, if I have another file open into which I want to import text data, I run the macro and it works. (I may not even be doing this right.) Doing the same for the EXPORT macro does not work. I just copied and pasted into a module each of the EXPORT macros but no joy--I get error messages such as "delimiter" expected here, or parameter expected etc. I was wondering if anyone could help me figure it out as I could REALLY use the export macros! Tx a million. S |
Chip Pearson Import/Export Macros
S
The <space_ at the end of some lines is a continuation character which lets Excel know this is all one wrapped line of code, not two. Gord Dibben Excel MVP On Sat, 3 Apr 2004 11:01:43 -0500, "saturnin02" <saturnin02_at_hotmail.com wrote: Chip, You were absolutely right! I put the 'Sep As String' and 'SelectionOnly As Boolean' on the same line and it works. What is the "_" at the end or within some of the lines? Tx for your help! S "Chip Pearson" wrote in message ... The code works for me just fine. In the Public Sub ExportToTextFile declaration, ensure that 'Sep As String' and 'SelectionOnly As Boolean' are on the same line. You may be suffering from line break problems caused by copy/paste. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "saturnin02" <saturnin02_at_hotmail.com wrote in message ... Sorry Chip--U didn't realize you meant post it HERE. I am just using is AS IS on your site. Public Sub ExportToTextFile(FName As String, _ Sep As String, SelectionOnly As Boolean) Dim WholeLine As String Dim FNum As Integer Dim RowNdx As Long Dim ColNdx As Integer Dim StartRow As Long Dim EndRow As Long Dim StartCol As Integer Dim EndCol As Integer Dim CellValue As String Application.ScreenUpdating = False On Error GoTo EndMacro: FNum = FreeFile If SelectionOnly = True Then With Selection StartRow = .Cells(1).row StartCol = .Cells(1).Column EndRow = .Cells(.Cells.Count).row EndCol = .Cells(.Cells.Count).Column End With Else With ActiveSheet.UsedRange StartRow = .Cells(1).row StartCol = .Cells(1).Column EndRow = .Cells(.Cells.Count).row EndCol = .Cells(.Cells.Count).Column End With End If Open FName For Output Access Write As #FNum For RowNdx = StartRow To EndRow WholeLine = "" For ColNdx = StartCol To EndCol If Cells(RowNdx, ColNdx).Value = "" Then CellValue = Chr(34) & Chr(34) Else CellValue = _ Application.WorksheetFunction.Text _ (Cells(RowNdx, ColNdx).Value, _ Cells(RowNdx, ColNdx).NumberFormat) End If WholeLine = WholeLine & CellValue & Sep Next ColNdx WholeLine = Left(WholeLine, Len(WholeLine) - Len(Sep)) Print #FNum, WholeLine Next RowNdx EndMacro: On Error GoTo 0 Application.ScreenUpdating = True Close #FNum End Sub And the other one is: Public Sub DoTheExport() Dim FName As Variant Dim Sep As String FName = Application.GetSaveAsFileName() If FName = False Then MsgBox "You didn't select a file" Exit Sub End If Sep = InputBox("Enter a single delimiter character (e.g., comma or semi-colon)", _ "Export To Text File") ExportToTextFile CStr(FName), Sep, _ MsgBox("Do You Want To Export The Entire Worksheet?", _ vbYesNo, "Export To Text File") = vbNo End Sub I am sure I need to DEFINE some parameters here is that correct? S "saturnin02" <saturnin02_at_hotmail.com wrote in message ... Chip, Bear with me but I do not understand. Do you mean in the same workbook as the import one, separate workbook ..... Which macro first, etc. I am really a newb--please help me get going. TX. S "Chip Pearson" wrote in message ... Post the code you are using to call the export macro. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "saturnin02" <saturnin02_at_hotmail.com wrote in message ... Hi, I need help on the Macros posted on chip's web site at http://www.cpearson.com/excel/imptext.htm (I am a newbie at VB.) I have basically copied and pasted the 2 macros for importing-each in a separate module and saved in a file (importtotext.xls). When I open the file and then minimize it, if I have another file open into which I want to import text data, I run the macro and it works. (I may not even be doing this right.) Doing the same for the EXPORT macro does not work. I just copied and pasted into a module each of the EXPORT macros but no joy--I get error messages such as "delimiter" expected here, or parameter expected etc. I was wondering if anyone could help me figure it out as I could REALLY use the export macros! Tx a million. S |
Chip Pearson Import/Export Macros
add after FName =
FName = Application.GetSaveAsFileName() if not instr(Fname,".") then FName = FName & ".txt" end if -- Regards, Tom Ogilvy "saturnin02" <saturnin02_at_hotmail.com wrote in message ... Chip, One last thing. How do I modify the Export Macro so that when it asks me to Save File As--it automatically puts the extension ".txt" there instead of .xls? Tx, S "Chip Pearson" wrote in message ... The code works for me just fine. In the Public Sub ExportToTextFile declaration, ensure that 'Sep As String' and 'SelectionOnly As Boolean' are on the same line. You may be suffering from line break problems caused by copy/paste. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "saturnin02" <saturnin02_at_hotmail.com wrote in message ... Sorry Chip--U didn't realize you meant post it HERE. I am just using is AS IS on your site. Public Sub ExportToTextFile(FName As String, _ Sep As String, SelectionOnly As Boolean) Dim WholeLine As String Dim FNum As Integer Dim RowNdx As Long Dim ColNdx As Integer Dim StartRow As Long Dim EndRow As Long Dim StartCol As Integer Dim EndCol As Integer Dim CellValue As String Application.ScreenUpdating = False On Error GoTo EndMacro: FNum = FreeFile If SelectionOnly = True Then With Selection StartRow = .Cells(1).row StartCol = .Cells(1).Column EndRow = .Cells(.Cells.Count).row EndCol = .Cells(.Cells.Count).Column End With Else With ActiveSheet.UsedRange StartRow = .Cells(1).row StartCol = .Cells(1).Column EndRow = .Cells(.Cells.Count).row EndCol = .Cells(.Cells.Count).Column End With End If Open FName For Output Access Write As #FNum For RowNdx = StartRow To EndRow WholeLine = "" For ColNdx = StartCol To EndCol If Cells(RowNdx, ColNdx).Value = "" Then CellValue = Chr(34) & Chr(34) Else CellValue = _ Application.WorksheetFunction.Text _ (Cells(RowNdx, ColNdx).Value, _ Cells(RowNdx, ColNdx).NumberFormat) End If WholeLine = WholeLine & CellValue & Sep Next ColNdx WholeLine = Left(WholeLine, Len(WholeLine) - Len(Sep)) Print #FNum, WholeLine Next RowNdx EndMacro: On Error GoTo 0 Application.ScreenUpdating = True Close #FNum End Sub And the other one is: Public Sub DoTheExport() Dim FName As Variant Dim Sep As String FName = Application.GetSaveAsFileName() If FName = False Then MsgBox "You didn't select a file" Exit Sub End If Sep = InputBox("Enter a single delimiter character (e.g., comma or semi-colon)", _ "Export To Text File") ExportToTextFile CStr(FName), Sep, _ MsgBox("Do You Want To Export The Entire Worksheet?", _ vbYesNo, "Export To Text File") = vbNo End Sub I am sure I need to DEFINE some parameters here is that correct? S "saturnin02" <saturnin02_at_hotmail.com wrote in message ... Chip, Bear with me but I do not understand. Do you mean in the same workbook as the import one, separate workbook ..... Which macro first, etc. I am really a newb--please help me get going. TX. S "Chip Pearson" wrote in message ... Post the code you are using to call the export macro. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "saturnin02" <saturnin02_at_hotmail.com wrote in message ... Hi, I need help on the Macros posted on chip's web site at http://www.cpearson.com/excel/imptext.htm (I am a newbie at VB.) I have basically copied and pasted the 2 macros for importing-each in a separate module and saved in a file (importtotext.xls). When I open the file and then minimize it, if I have another file open into which I want to import text data, I run the macro and it works. (I may not even be doing this right.) Doing the same for the EXPORT macro does not work. I just copied and pasted into a module each of the EXPORT macros but no joy--I get error messages such as "delimiter" expected here, or parameter expected etc. I was wondering if anyone could help me figure it out as I could REALLY use the export macros! Tx a million. S |
Chip Pearson Import/Export Macros
Hi Dave,
Tx for the comments--And I will ck out your link. I have already been on your site and gotten some goodies there.... S "David McRitchie" wrote in message ... The underscore is a continuation character and is used to indicate that the next line is a continuation. You should have gotten a syntax error and line(s) in the macro in RED. You will frequently have such problems in copying a macro from a newsposting due to line wrap. But this should not happen when copying from a web page. The web page should have been fixed after telling you what was wrong with your (adopted) code. Pasting macro code into a module sheet (XL95), or into VBA Editor (XL97 & XL2000) http://www.mvps.org/dmcritchie/excel/install.htm You want to make both the Excel and the web source windows as wide as possible. Pasted lines or any other lines turn RED if there is a syntax or other easily recognizable problem in Excel. An underscore at the end of a line indicates a continuation. Splitting a line without the continuation character is the most frequent syntax problem and code lines turn red as soon as continuation or other syntax errors are detected. Actually the continuation character is really two characters, a space followed by an underscore. --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "saturnin02" <saturnin02_at_hotmail.com wrote in message ... Chip, You were absolutely right! I put the 'Sep As String' and 'SelectionOnly As Boolean' on the same line and it works. What is the "_" at the end or within some of the lines? Tx for your help! |
Chip Pearson Import/Export Macros
Got it.
Tx, S "Gord Dibben" <gorddibbATshawDOTca wrote in message ... S The <space_ at the end of some lines is a continuation character which lets Excel know this is all one wrapped line of code, not two. Gord Dibben Excel MVP On Sat, 3 Apr 2004 11:01:43 -0500, "saturnin02" <saturnin02_at_hotmail.com wrote: Chip, You were absolutely right! I put the 'Sep As String' and 'SelectionOnly As Boolean' on the same line and it works. What is the "_" at the end or within some of the lines? Tx for your help! S "Chip Pearson" wrote in message ... The code works for me just fine. In the Public Sub ExportToTextFile declaration, ensure that 'Sep As String' and 'SelectionOnly As Boolean' are on the same line. You may be suffering from line break problems caused by copy/paste. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "saturnin02" <saturnin02_at_hotmail.com wrote in message ... Sorry Chip--U didn't realize you meant post it HERE. I am just using is AS IS on your site. Public Sub ExportToTextFile(FName As String, _ Sep As String, SelectionOnly As Boolean) Dim WholeLine As String Dim FNum As Integer Dim RowNdx As Long Dim ColNdx As Integer Dim StartRow As Long Dim EndRow As Long Dim StartCol As Integer Dim EndCol As Integer Dim CellValue As String Application.ScreenUpdating = False On Error GoTo EndMacro: FNum = FreeFile If SelectionOnly = True Then With Selection StartRow = .Cells(1).row StartCol = .Cells(1).Column EndRow = .Cells(.Cells.Count).row EndCol = .Cells(.Cells.Count).Column End With Else With ActiveSheet.UsedRange StartRow = .Cells(1).row StartCol = .Cells(1).Column EndRow = .Cells(.Cells.Count).row EndCol = .Cells(.Cells.Count).Column End With End If Open FName For Output Access Write As #FNum For RowNdx = StartRow To EndRow WholeLine = "" For ColNdx = StartCol To EndCol If Cells(RowNdx, ColNdx).Value = "" Then CellValue = Chr(34) & Chr(34) Else CellValue = _ Application.WorksheetFunction.Text _ (Cells(RowNdx, ColNdx).Value, _ Cells(RowNdx, ColNdx).NumberFormat) End If WholeLine = WholeLine & CellValue & Sep Next ColNdx WholeLine = Left(WholeLine, Len(WholeLine) - Len(Sep)) Print #FNum, WholeLine Next RowNdx EndMacro: On Error GoTo 0 Application.ScreenUpdating = True Close #FNum End Sub And the other one is: Public Sub DoTheExport() Dim FName As Variant Dim Sep As String FName = Application.GetSaveAsFileName() If FName = False Then MsgBox "You didn't select a file" Exit Sub End If Sep = InputBox("Enter a single delimiter character (e.g., comma or semi-colon)", _ "Export To Text File") ExportToTextFile CStr(FName), Sep, _ MsgBox("Do You Want To Export The Entire Worksheet?", _ vbYesNo, "Export To Text File") = vbNo End Sub I am sure I need to DEFINE some parameters here is that correct? S "saturnin02" <saturnin02_at_hotmail.com wrote in message ... Chip, Bear with me but I do not understand. Do you mean in the same workbook as the import one, separate workbook ..... Which macro first, etc. I am really a newb--please help me get going. TX. S "Chip Pearson" wrote in message ... Post the code you are using to call the export macro. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "saturnin02" <saturnin02_at_hotmail.com wrote in message ... Hi, I need help on the Macros posted on chip's web site at http://www.cpearson.com/excel/imptext.htm (I am a newbie at VB.) I have basically copied and pasted the 2 macros for importing-each in a separate module and saved in a file (importtotext.xls). When I open the file and then minimize it, if I have another file open into which I want to import text data, I run the macro and it works. (I may not even be doing this right.) Doing the same for the EXPORT macro does not work. I just copied and pasted into a module each of the EXPORT macros but no joy--I get error messages such as "delimiter" expected here, or parameter expected etc. I was wondering if anyone could help me figure it out as I could REALLY use the export macros! Tx a million. S |
Chip Pearson Import/Export Macros
Tom,
Sorry to ask but...There are several instances of FName = Is it in the "Do The Export" Macro or the "Export to Text File" macro? Also, so I REPLACE or insert in addition to what is already there? Tx, S "Tom Ogilvy" wrote in message ... add after FName = FName = Application.GetSaveAsFileName() if not instr(Fname,".") then FName = FName & ".txt" end if -- Regards, Tom Ogilvy "saturnin02" <saturnin02_at_hotmail.com wrote in message ... Chip, One last thing. How do I modify the Export Macro so that when it asks me to Save File As--it automatically puts the extension ".txt" there instead of .xls? Tx, S "Chip Pearson" wrote in message ... The code works for me just fine. In the Public Sub ExportToTextFile declaration, ensure that 'Sep As String' and 'SelectionOnly As Boolean' are on the same line. You may be suffering from line break problems caused by copy/paste. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "saturnin02" <saturnin02_at_hotmail.com wrote in message ... Sorry Chip--U didn't realize you meant post it HERE. I am just using is AS IS on your site. Public Sub ExportToTextFile(FName As String, _ Sep As String, SelectionOnly As Boolean) Dim WholeLine As String Dim FNum As Integer Dim RowNdx As Long Dim ColNdx As Integer Dim StartRow As Long Dim EndRow As Long Dim StartCol As Integer Dim EndCol As Integer Dim CellValue As String Application.ScreenUpdating = False On Error GoTo EndMacro: FNum = FreeFile If SelectionOnly = True Then With Selection StartRow = .Cells(1).row StartCol = .Cells(1).Column EndRow = .Cells(.Cells.Count).row EndCol = .Cells(.Cells.Count).Column End With Else With ActiveSheet.UsedRange StartRow = .Cells(1).row StartCol = .Cells(1).Column EndRow = .Cells(.Cells.Count).row EndCol = .Cells(.Cells.Count).Column End With End If Open FName For Output Access Write As #FNum For RowNdx = StartRow To EndRow WholeLine = "" For ColNdx = StartCol To EndCol If Cells(RowNdx, ColNdx).Value = "" Then CellValue = Chr(34) & Chr(34) Else CellValue = _ Application.WorksheetFunction.Text _ (Cells(RowNdx, ColNdx).Value, _ Cells(RowNdx, ColNdx).NumberFormat) End If WholeLine = WholeLine & CellValue & Sep Next ColNdx WholeLine = Left(WholeLine, Len(WholeLine) - Len(Sep)) Print #FNum, WholeLine Next RowNdx EndMacro: On Error GoTo 0 Application.ScreenUpdating = True Close #FNum End Sub And the other one is: Public Sub DoTheExport() Dim FName As Variant Dim Sep As String FName = Application.GetSaveAsFileName() If FName = False Then MsgBox "You didn't select a file" Exit Sub End If Sep = InputBox("Enter a single delimiter character (e.g., comma or semi-colon)", _ "Export To Text File") ExportToTextFile CStr(FName), Sep, _ MsgBox("Do You Want To Export The Entire Worksheet?", _ vbYesNo, "Export To Text File") = vbNo End Sub I am sure I need to DEFINE some parameters here is that correct? S "saturnin02" <saturnin02_at_hotmail.com wrote in message ... Chip, Bear with me but I do not understand. Do you mean in the same workbook as the import one, separate workbook ..... Which macro first, etc. I am really a newb--please help me get going. TX. S "Chip Pearson" wrote in message ... Post the code you are using to call the export macro. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "saturnin02" <saturnin02_at_hotmail.com wrote in message ... Hi, I need help on the Macros posted on chip's web site at http://www.cpearson.com/excel/imptext.htm (I am a newbie at VB.) I have basically copied and pasted the 2 macros for importing-each in a separate module and saved in a file (importtotext.xls). When I open the file and then minimize it, if I have another file open into which I want to import text data, I run the macro and it works. (I may not even be doing this right.) Doing the same for the EXPORT macro does not work. I just copied and pasted into a module each of the EXPORT macros but no joy--I get error messages such as "delimiter" expected here, or parameter expected etc. I was wondering if anyone could help me figure it out as I could REALLY use the export macros! Tx a million. S |
Chip Pearson Import/Export Macros
On the code you posted there is only one line like this:
FName = Application.GetSaveAsFileName() it was in Public Sub DoTheExport() 4th line after the above. -- Regards, Tom Ogilvy "saturnin02" <saturnin02_at_hotmail.com wrote in message ... Tom, Sorry to ask but...There are several instances of FName = Is it in the "Do The Export" Macro or the "Export to Text File" macro? Also, so I REPLACE or insert in addition to what is already there? Tx, S "Tom Ogilvy" wrote in message ... add after FName = FName = Application.GetSaveAsFileName() if not instr(Fname,".") then FName = FName & ".txt" end if -- Regards, Tom Ogilvy "saturnin02" <saturnin02_at_hotmail.com wrote in message ... Chip, One last thing. How do I modify the Export Macro so that when it asks me to Save File As--it automatically puts the extension ".txt" there instead of .xls? Tx, S "Chip Pearson" wrote in message ... The code works for me just fine. In the Public Sub ExportToTextFile declaration, ensure that 'Sep As String' and 'SelectionOnly As Boolean' are on the same line. You may be suffering from line break problems caused by copy/paste. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "saturnin02" <saturnin02_at_hotmail.com wrote in message ... Sorry Chip--U didn't realize you meant post it HERE. I am just using is AS IS on your site. Public Sub ExportToTextFile(FName As String, _ Sep As String, SelectionOnly As Boolean) Dim WholeLine As String Dim FNum As Integer Dim RowNdx As Long Dim ColNdx As Integer Dim StartRow As Long Dim EndRow As Long Dim StartCol As Integer Dim EndCol As Integer Dim CellValue As String Application.ScreenUpdating = False On Error GoTo EndMacro: FNum = FreeFile If SelectionOnly = True Then With Selection StartRow = .Cells(1).row StartCol = .Cells(1).Column EndRow = .Cells(.Cells.Count).row EndCol = .Cells(.Cells.Count).Column End With Else With ActiveSheet.UsedRange StartRow = .Cells(1).row StartCol = .Cells(1).Column EndRow = .Cells(.Cells.Count).row EndCol = .Cells(.Cells.Count).Column End With End If Open FName For Output Access Write As #FNum For RowNdx = StartRow To EndRow WholeLine = "" For ColNdx = StartCol To EndCol If Cells(RowNdx, ColNdx).Value = "" Then CellValue = Chr(34) & Chr(34) Else CellValue = _ Application.WorksheetFunction.Text _ (Cells(RowNdx, ColNdx).Value, _ Cells(RowNdx, ColNdx).NumberFormat) End If WholeLine = WholeLine & CellValue & Sep Next ColNdx WholeLine = Left(WholeLine, Len(WholeLine) - Len(Sep)) Print #FNum, WholeLine Next RowNdx EndMacro: On Error GoTo 0 Application.ScreenUpdating = True Close #FNum End Sub And the other one is: Public Sub DoTheExport() Dim FName As Variant Dim Sep As String FName = Application.GetSaveAsFileName() If FName = False Then MsgBox "You didn't select a file" Exit Sub End If Sep = InputBox("Enter a single delimiter character (e.g., comma or semi-colon)", _ "Export To Text File") ExportToTextFile CStr(FName), Sep, _ MsgBox("Do You Want To Export The Entire Worksheet?", _ vbYesNo, "Export To Text File") = vbNo End Sub I am sure I need to DEFINE some parameters here is that correct? S "saturnin02" <saturnin02_at_hotmail.com wrote in message ... Chip, Bear with me but I do not understand. Do you mean in the same workbook as the import one, separate workbook ..... Which macro first, etc. I am really a newb--please help me get going. TX. S "Chip Pearson" wrote in message ... Post the code you are using to call the export macro. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "saturnin02" <saturnin02_at_hotmail.com wrote in message ... Hi, I need help on the Macros posted on chip's web site at http://www.cpearson.com/excel/imptext.htm (I am a newbie at VB.) I have basically copied and pasted the 2 macros for importing-each in a separate module and saved in a file (importtotext.xls). When I open the file and then minimize it, if I have another file open into which I want to import text data, I run the macro and it works. (I may not even be doing this right.) Doing the same for the EXPORT macro does not work. I just copied and pasted into a module each of the EXPORT macros but no joy--I get error messages such as "delimiter" expected here, or parameter expected etc. I was wondering if anyone could help me figure it out as I could REALLY use the export macros! Tx a million. S |
Chip Pearson Import/Export Macros
Can't get it to work.
I must be doing s'thing wrong. Do I REPLACE or insert in addition to what is already there? S "Tom Ogilvy" wrote in message ... On the code you posted there is only one line like this: FName = Application.GetSaveAsFileName() it was in Public Sub DoTheExport() 4th line after the above. -- Regards, Tom Ogilvy "saturnin02" <saturnin02_at_hotmail.com wrote in message ... Tom, Sorry to ask but...There are several instances of FName = Is it in the "Do The Export" Macro or the "Export to Text File" macro? Also, so I REPLACE or insert in addition to what is already there? Tx, S "Tom Ogilvy" wrote in message ... add after FName = FName = Application.GetSaveAsFileName() if not instr(Fname,".") then FName = FName & ".txt" end if -- Regards, Tom Ogilvy "saturnin02" <saturnin02_at_hotmail.com wrote in message ... Chip, One last thing. How do I modify the Export Macro so that when it asks me to Save File As--it automatically puts the extension ".txt" there instead of .xls? Tx, S "Chip Pearson" wrote in message ... The code works for me just fine. In the Public Sub ExportToTextFile declaration, ensure that 'Sep As String' and 'SelectionOnly As Boolean' are on the same line. You may be suffering from line break problems caused by copy/paste. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "saturnin02" <saturnin02_at_hotmail.com wrote in message ... Sorry Chip--U didn't realize you meant post it HERE. I am just using is AS IS on your site. Public Sub ExportToTextFile(FName As String, _ Sep As String, SelectionOnly As Boolean) Dim WholeLine As String Dim FNum As Integer Dim RowNdx As Long Dim ColNdx As Integer Dim StartRow As Long Dim EndRow As Long Dim StartCol As Integer Dim EndCol As Integer Dim CellValue As String Application.ScreenUpdating = False On Error GoTo EndMacro: FNum = FreeFile If SelectionOnly = True Then With Selection StartRow = .Cells(1).row StartCol = .Cells(1).Column EndRow = .Cells(.Cells.Count).row EndCol = .Cells(.Cells.Count).Column End With Else With ActiveSheet.UsedRange StartRow = .Cells(1).row StartCol = .Cells(1).Column EndRow = .Cells(.Cells.Count).row EndCol = .Cells(.Cells.Count).Column End With End If Open FName For Output Access Write As #FNum For RowNdx = StartRow To EndRow WholeLine = "" For ColNdx = StartCol To EndCol If Cells(RowNdx, ColNdx).Value = "" Then CellValue = Chr(34) & Chr(34) Else CellValue = _ Application.WorksheetFunction.Text _ (Cells(RowNdx, ColNdx).Value, _ Cells(RowNdx, ColNdx).NumberFormat) End If WholeLine = WholeLine & CellValue & Sep Next ColNdx WholeLine = Left(WholeLine, Len(WholeLine) - Len(Sep)) Print #FNum, WholeLine Next RowNdx EndMacro: On Error GoTo 0 Application.ScreenUpdating = True Close #FNum End Sub And the other one is: Public Sub DoTheExport() Dim FName As Variant Dim Sep As String FName = Application.GetSaveAsFileName() If FName = False Then MsgBox "You didn't select a file" Exit Sub End If Sep = InputBox("Enter a single delimiter character (e.g., comma or semi-colon)", _ "Export To Text File") ExportToTextFile CStr(FName), Sep, _ MsgBox("Do You Want To Export The Entire Worksheet?", _ vbYesNo, "Export To Text File") = vbNo End Sub I am sure I need to DEFINE some parameters here is that correct? S "saturnin02" <saturnin02_at_hotmail.com wrote in message ... Chip, Bear with me but I do not understand. Do you mean in the same workbook as the import one, separate workbook ..... Which macro first, etc. I am really a newb--please help me get going. TX. S "Chip Pearson" wrote in message ... Post the code you are using to call the export macro. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "saturnin02" <saturnin02_at_hotmail.com wrote in message ... Hi, I need help on the Macros posted on chip's web site at http://www.cpearson.com/excel/imptext.htm (I am a newbie at VB.) I have basically copied and pasted the 2 macros for importing-each in a separate module and saved in a file (importtotext.xls). When I open the file and then minimize it, if I have another file open into which I want to import text data, I run the macro and it works. (I may not even be doing this right.) Doing the same for the EXPORT macro does not work. I just copied and pasted into a module each of the EXPORT macros but no joy--I get error messages such as "delimiter" expected here, or parameter expected etc. I was wondering if anyone could help me figure it out as I could REALLY use the export macros! Tx a million. S |
Chip Pearson Import/Export Macros
In the file dialog, just enter the filename with a .txt extension and you
should be fine. -- Regards, Tom Ogilvy "saturnin02" <saturnin02_at_hotmail.com wrote in message ... Can't get it to work. I must be doing s'thing wrong. Do I REPLACE or insert in addition to what is already there? S "Tom Ogilvy" wrote in message ... On the code you posted there is only one line like this: FName = Application.GetSaveAsFileName() it was in Public Sub DoTheExport() 4th line after the above. -- Regards, Tom Ogilvy "saturnin02" <saturnin02_at_hotmail.com wrote in message ... Tom, Sorry to ask but...There are several instances of FName = Is it in the "Do The Export" Macro or the "Export to Text File" macro? Also, so I REPLACE or insert in addition to what is already there? Tx, S "Tom Ogilvy" wrote in message ... add after FName = FName = Application.GetSaveAsFileName() if not instr(Fname,".") then FName = FName & ".txt" end if -- Regards, Tom Ogilvy "saturnin02" <saturnin02_at_hotmail.com wrote in message ... Chip, One last thing. How do I modify the Export Macro so that when it asks me to Save File As--it automatically puts the extension ".txt" there instead of .xls? Tx, S "Chip Pearson" wrote in message ... The code works for me just fine. In the Public Sub ExportToTextFile declaration, ensure that 'Sep As String' and 'SelectionOnly As Boolean' are on the same line. You may be suffering from line break problems caused by copy/paste. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "saturnin02" <saturnin02_at_hotmail.com wrote in message ... Sorry Chip--U didn't realize you meant post it HERE. I am just using is AS IS on your site. Public Sub ExportToTextFile(FName As String, _ Sep As String, SelectionOnly As Boolean) Dim WholeLine As String Dim FNum As Integer Dim RowNdx As Long Dim ColNdx As Integer Dim StartRow As Long Dim EndRow As Long Dim StartCol As Integer Dim EndCol As Integer Dim CellValue As String Application.ScreenUpdating = False On Error GoTo EndMacro: FNum = FreeFile If SelectionOnly = True Then With Selection StartRow = .Cells(1).row StartCol = .Cells(1).Column EndRow = .Cells(.Cells.Count).row EndCol = .Cells(.Cells.Count).Column End With Else With ActiveSheet.UsedRange StartRow = .Cells(1).row StartCol = .Cells(1).Column EndRow = .Cells(.Cells.Count).row EndCol = .Cells(.Cells.Count).Column End With End If Open FName For Output Access Write As #FNum For RowNdx = StartRow To EndRow WholeLine = "" For ColNdx = StartCol To EndCol If Cells(RowNdx, ColNdx).Value = "" Then CellValue = Chr(34) & Chr(34) Else CellValue = _ Application.WorksheetFunction.Text _ (Cells(RowNdx, ColNdx).Value, _ Cells(RowNdx, ColNdx).NumberFormat) End If WholeLine = WholeLine & CellValue & Sep Next ColNdx WholeLine = Left(WholeLine, Len(WholeLine) - Len(Sep)) Print #FNum, WholeLine Next RowNdx EndMacro: On Error GoTo 0 Application.ScreenUpdating = True Close #FNum End Sub And the other one is: Public Sub DoTheExport() Dim FName As Variant Dim Sep As String FName = Application.GetSaveAsFileName() If FName = False Then MsgBox "You didn't select a file" Exit Sub End If Sep = InputBox("Enter a single delimiter character (e.g., comma or semi-colon)", _ "Export To Text File") ExportToTextFile CStr(FName), Sep, _ MsgBox("Do You Want To Export The Entire Worksheet?", _ vbYesNo, "Export To Text File") = vbNo End Sub I am sure I need to DEFINE some parameters here is that correct? S "saturnin02" <saturnin02_at_hotmail.com wrote in message ... Chip, Bear with me but I do not understand. Do you mean in the same workbook as the import one, separate workbook ..... Which macro first, etc. I am really a newb--please help me get going. TX. S "Chip Pearson" wrote in message ... Post the code you are using to call the export macro. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "saturnin02" <saturnin02_at_hotmail.com wrote in message ... Hi, I need help on the Macros posted on chip's web site at http://www.cpearson.com/excel/imptext.htm (I am a newbie at VB.) I have basically copied and pasted the 2 macros for importing-each in a separate module and saved in a file (importtotext.xls). When I open the file and then minimize it, if I have another file open into which I want to import text data, I run the macro and it works. (I may not even be doing this right.) Doing the same for the EXPORT macro does not work. I just copied and pasted into a module each of the EXPORT macros but no joy--I get error messages such as "delimiter" expected here, or parameter expected etc. I was wondering if anyone could help me figure it out as I could REALLY use the export macros! Tx a million. S |
Chip Pearson Import/Export Macros
My feeling exactly.
Let's not go nuts here... (Tx for all the feedback--it is always very helpful.) S "Tom Ogilvy" wrote in message ... In the file dialog, just enter the filename with a .txt extension and you should be fine. -- Regards, Tom Ogilvy "saturnin02" <saturnin02_at_hotmail.com wrote in message ... Can't get it to work. I must be doing s'thing wrong. Do I REPLACE or insert in addition to what is already there? S "Tom Ogilvy" wrote in message ... On the code you posted there is only one line like this: FName = Application.GetSaveAsFileName() it was in Public Sub DoTheExport() 4th line after the above. -- Regards, Tom Ogilvy "saturnin02" <saturnin02_at_hotmail.com wrote in message ... Tom, Sorry to ask but...There are several instances of FName = Is it in the "Do The Export" Macro or the "Export to Text File" macro? Also, so I REPLACE or insert in addition to what is already there? Tx, S "Tom Ogilvy" wrote in message ... add after FName = FName = Application.GetSaveAsFileName() if not instr(Fname,".") then FName = FName & ".txt" end if -- Regards, Tom Ogilvy "saturnin02" <saturnin02_at_hotmail.com wrote in message ... Chip, One last thing. How do I modify the Export Macro so that when it asks me to Save File As--it automatically puts the extension ".txt" there instead of .xls? Tx, S "Chip Pearson" wrote in message ... The code works for me just fine. In the Public Sub ExportToTextFile declaration, ensure that 'Sep As String' and 'SelectionOnly As Boolean' are on the same line. You may be suffering from line break problems caused by copy/paste. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "saturnin02" <saturnin02_at_hotmail.com wrote in message ... Sorry Chip--U didn't realize you meant post it HERE. I am just using is AS IS on your site. Public Sub ExportToTextFile(FName As String, _ Sep As String, SelectionOnly As Boolean) Dim WholeLine As String Dim FNum As Integer Dim RowNdx As Long Dim ColNdx As Integer Dim StartRow As Long Dim EndRow As Long Dim StartCol As Integer Dim EndCol As Integer Dim CellValue As String Application.ScreenUpdating = False On Error GoTo EndMacro: FNum = FreeFile If SelectionOnly = True Then With Selection StartRow = .Cells(1).row StartCol = .Cells(1).Column EndRow = .Cells(.Cells.Count).row EndCol = .Cells(.Cells.Count).Column End With Else With ActiveSheet.UsedRange StartRow = .Cells(1).row StartCol = .Cells(1).Column EndRow = .Cells(.Cells.Count).row EndCol = .Cells(.Cells.Count).Column End With End If Open FName For Output Access Write As #FNum For RowNdx = StartRow To EndRow WholeLine = "" For ColNdx = StartCol To EndCol If Cells(RowNdx, ColNdx).Value = "" Then CellValue = Chr(34) & Chr(34) Else CellValue = _ Application.WorksheetFunction.Text _ (Cells(RowNdx, ColNdx).Value, _ Cells(RowNdx, ColNdx).NumberFormat) End If WholeLine = WholeLine & CellValue & Sep Next ColNdx WholeLine = Left(WholeLine, Len(WholeLine) - Len(Sep)) Print #FNum, WholeLine Next RowNdx EndMacro: On Error GoTo 0 Application.ScreenUpdating = True Close #FNum End Sub And the other one is: Public Sub DoTheExport() Dim FName As Variant Dim Sep As String FName = Application.GetSaveAsFileName() If FName = False Then MsgBox "You didn't select a file" Exit Sub End If Sep = InputBox("Enter a single delimiter character (e.g., comma or semi-colon)", _ "Export To Text File") ExportToTextFile CStr(FName), Sep, _ MsgBox("Do You Want To Export The Entire Worksheet?", _ vbYesNo, "Export To Text File") = vbNo End Sub I am sure I need to DEFINE some parameters here is that correct? S "saturnin02" <saturnin02_at_hotmail.com wrote in message ... Chip, Bear with me but I do not understand. Do you mean in the same workbook as the import one, separate workbook ..... Which macro first, etc. I am really a newb--please help me get going. TX. S "Chip Pearson" wrote in message ... Post the code you are using to call the export macro. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "saturnin02" <saturnin02_at_hotmail.com wrote in message ... Hi, I need help on the Macros posted on chip's web site at http://www.cpearson.com/excel/imptext.htm (I am a newbie at VB.) I have basically copied and pasted the 2 macros for importing-each in a separate module and saved in a file (importtotext.xls). When I open the file and then minimize it, if I have another file open into which I want to import text data, I run the macro and it works. (I may not even be doing this right.) Doing the same for the EXPORT macro does not work. I just copied and pasted into a module each of the EXPORT macros but no joy--I get error messages such as "delimiter" expected here, or parameter expected etc. I was wondering if anyone could help me figure it out as I could REALLY use the export macros! Tx a million. S |
Chip Pearson Import/Export Macros - Added Twist
Chip,
Everything works good with the export to text macro. I need to modify it to reflect a specila twist so that another program can read it, and that is I want the macro to export all cell values as text, but put each cell value in one column only i.e. in destination file. A1 A2 A3 B1 B2 B3 to A1 A2 A3 B1 B2 B3 Is it possible to modify it that way? Sorry, put I am using a program in DELPHI that will only import text files with values in one column only. Let me know. Tx, "Chip Pearson" wrote in message ... The " _" characters (note the leading space before the underscore) indicate that a line of code is continued on the next line of text in the file. You can use this to split logical lines of code across several physical lines of text in order to improve readability. They have no effect on the execution of the code. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "saturnin02" <saturnin02_at_hotmail.com wrote in message ... Chip, You were absolutely right! I put the 'Sep As String' and 'SelectionOnly As Boolean' on the same line and it works. What is the "_" at the end or within some of the lines? Tx for your help! S "Chip Pearson" wrote in message ... The code works for me just fine. In the Public Sub ExportToTextFile declaration, ensure that 'Sep As String' and 'SelectionOnly As Boolean' are on the same line. You may be suffering from line break problems caused by copy/paste. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "saturnin02" <saturnin02_at_hotmail.com wrote in message ... Sorry Chip--U didn't realize you meant post it HERE. I am just using is AS IS on your site. Public Sub ExportToTextFile(FName As String, _ Sep As String, SelectionOnly As Boolean) Dim WholeLine As String Dim FNum As Integer Dim RowNdx As Long Dim ColNdx As Integer Dim StartRow As Long Dim EndRow As Long Dim StartCol As Integer Dim EndCol As Integer Dim CellValue As String Application.ScreenUpdating = False On Error GoTo EndMacro: FNum = FreeFile If SelectionOnly = True Then With Selection StartRow = .Cells(1).row StartCol = .Cells(1).Column EndRow = .Cells(.Cells.Count).row EndCol = .Cells(.Cells.Count).Column End With Else With ActiveSheet.UsedRange StartRow = .Cells(1).row StartCol = .Cells(1).Column EndRow = .Cells(.Cells.Count).row EndCol = .Cells(.Cells.Count).Column End With End If Open FName For Output Access Write As #FNum For RowNdx = StartRow To EndRow WholeLine = "" For ColNdx = StartCol To EndCol If Cells(RowNdx, ColNdx).Value = "" Then CellValue = Chr(34) & Chr(34) Else CellValue = _ Application.WorksheetFunction.Text _ (Cells(RowNdx, ColNdx).Value, _ Cells(RowNdx, ColNdx).NumberFormat) End If WholeLine = WholeLine & CellValue & Sep Next ColNdx WholeLine = Left(WholeLine, Len(WholeLine) - Len(Sep)) Print #FNum, WholeLine Next RowNdx EndMacro: On Error GoTo 0 Application.ScreenUpdating = True Close #FNum End Sub And the other one is: Public Sub DoTheExport() Dim FName As Variant Dim Sep As String FName = Application.GetSaveAsFileName() If FName = False Then MsgBox "You didn't select a file" Exit Sub End If Sep = InputBox("Enter a single delimiter character (e.g., comma or semi-colon)", _ "Export To Text File") ExportToTextFile CStr(FName), Sep, _ MsgBox("Do You Want To Export The Entire Worksheet?", _ vbYesNo, "Export To Text File") = vbNo End Sub I am sure I need to DEFINE some parameters here is that correct? S "saturnin02" <saturnin02_at_hotmail.com wrote in message ... Chip, Bear with me but I do not understand. Do you mean in the same workbook as the import one, separate workbook ..... Which macro first, etc. I am really a newb--please help me get going. TX. S "Chip Pearson" wrote in message ... Post the code you are using to call the export macro. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "saturnin02" <saturnin02_at_hotmail.com wrote in message ... Hi, I need help on the Macros posted on chip's web site at http://www.cpearson.com/excel/imptext.htm (I am a newbie at VB.) I have basically copied and pasted the 2 macros for importing-each in a separate module and saved in a file (importtotext.xls). When I open the file and then minimize it, if I have another file open into which I want to import text data, I run the macro and it works. (I may not even be doing this right.) Doing the same for the EXPORT macro does not work. I just copied and pasted into a module each of the EXPORT macros but no joy--I get error messages such as "delimiter" expected here, or parameter expected etc. I was wondering if anyone could help me figure it out as I could REALLY use the export macros! Tx a million. S |
Chip Pearson Import/Export Macros - Added Twist
Try something like the following code:
Dim FName As String Dim FNum As Integer Dim Rng As Range FName = "C:\Test2\Test2.txt" ' change as desired FNum = FreeFile() Open FName For Output As #FNum For Each Rng In Selection.Cells ' change range as desired Print #FNum, Rng.Text Next Rng Close #FNum -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "saturnin02" <saturnin02_at_hotmail.com wrote in message ... Chip, Everything works good with the export to text macro. I need to modify it to reflect a specila twist so that another program can read it, and that is I want the macro to export all cell values as text, but put each cell value in one column only i.e. in destination file. A1 A2 A3 B1 B2 B3 to A1 A2 A3 B1 B2 B3 Is it possible to modify it that way? Sorry, put I am using a program in DELPHI that will only import text files with values in one column only. Let me know. Tx, |
| All times are GMT +1. The time now is 10:28 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com