Link to home
Start Free TrialLog in
Avatar of wrt1mea
wrt1mea

asked on

Update a macro

I need to update the following macro to trim out any open or closed quotation marks. When I am running this I am getting some quotation marks thats being entered into the .txt file. Also, is there a way to remove any blank lines at the end of the sheet??? I dont need it exporting a bunch of spaces or lines...

Sub Save_as_text_file()
Dim fPath As String, fname As String
Dim ws As Worksheet
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set ws = ActiveWorkbook.ActiveSheet
fname = Application.ActiveWorkbook.Path & "\" & _
        ThisWorkbook.Worksheets("BOB_BUILDER").Range("A2") & "_" & _
        ws.Name & ".txt"
fPath = Application.ActiveWorkbook.Path
ws.Copy
ActiveWorkbook.SaveAs fname, xlUnicodeText
Application.ActiveWorkbook.Close
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub


Or maybe modifying this would be easier? I set the range to copy from 1:5000. Just really needs to copy the last line of data from "PASTE_NOTEPAD"

Sub COPY_PASTE()
    Sheets("PASTE_NOTEPAD").Range("A1:A5000").Copy Destination:=Sheets("sheet1").Range("A1:A5000")
End Sub
Avatar of Bill Prew
Bill Prew

Can you provide a sample of a workbook that has examples of the data you need removed?


»bp
Avatar of wrt1mea

ASKER

I have no idea how I can obfuscate the data and provide a workbook with macros that would come close to running.

Everything seems to be so close....on the export to a .txt file, it randomly adds quotation marks. 48 of them as a matter of fact.

And I was just wanting to copy all the data in Paste_Notepad to sheet1. I am currently specifying a range and its bringing over a few thousand extra line breaks. But I wanted to leave room for future growth.
ASKER CERTIFIED SOLUTION
Avatar of Bill Prew
Bill Prew

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
So are you saying there are text cells in the data sheet that have either single quotes in them, or double quotes in them, and you want to delete those characters from those cells?


»bp
Avatar of wrt1mea

ASKER

OK, Bill...I will try that now. As far as my "random" quotes go...I think I have it traced down to a comma (,).

For example when I have commas in a row of data in excel, it wraps the row in open and closed quotes when it exports to a txt file.

Only lines that have commas...
Yes, that is desired behavior though, since comma is the field delimiter.  If you don't allow that to happen then the output file will not align properly when used / processed.


»bp
If you want the commas in the data fields, then they will need to be quoted, or you would have to change to a different delimiter that can never be in the data.

It depends on the usage of the exported data, what that program is expecting...


»bp
Avatar of wrt1mea

ASKER

I tried the

Sub COPY_PASTE()
    With Sheets("PASTE_NOTEPAD")
        .Range("A1:A" & .Cells(.Rows.Count, 1).End(xlUp).Row).Copy Destination:=Sheets("sheet1").Range("A1")
    End With
End Sub

Still bringing over 5000 lines
Then you must have data in column A for those 5000 rows.  Is there another column that we should check for the end of data in?


»bp
This copies the last row on PASTE_NOTEPAD to the same row on Sheet1

Dim lngLastRow As Long

With Sheets("PASTE_NOTEPAD")
    lngLastRow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    .Rows(lngLastRow).Copy Destination:=Sheets("sheet1").Rows(lngLastRow)
End With

Open in new window

Avatar of wrt1mea

ASKER

All data is pasted into A1. I verified that there is no data in A1100 to A5000.
Avatar of wrt1mea

ASKER

How do I use this with a sub?

Dim lngLastRow As Long

With Sheets("PASTE_NOTEPAD")
    lngLastRow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    .Rows(lngLastRow).Copy Destination:=Sheets("sheet1").Rows(lngLastRow)
End With
If you want the last row to be pasted into A1 then

Dim lngLastRow As Long

With Sheets("PASTE_NOTEPAD")
    lngLastRow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    .Rows(lngLastRow).Copy Destination:=Sheets("sheet1").Rows("1")
End With

Open in new window

In a sub.
Sub MySub()

Dim lngLastRow As Long

With Sheets("PASTE_NOTEPAD")
    lngLastRow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    .Rows(lngLastRow).Copy Destination:=Sheets("sheet1").Rows(lngLastRow)
End With

End Sub

Open in new window

Avatar of wrt1mea

ASKER

I need all of the data up to the last line from Paste_Notepad to get copied into Sheet1 starting at A1.
I verified that there is no data in A1100 to A5000

That can't be the case, there has to be something in column A.

If you think there is nothing in the rows after a certain point, then how do you think / know they were copied to SHEET1, since there would be no data to show that in them?


»bp
Avatar of wrt1mea

ASKER

Bill, I dont know why. I verified no data, highlighted and deleted and its still bringing over 5000 lines. Ideas on what to check?
I need all of the data up to the last line from Paste_Notepad to get copied into Sheet1 starting at A1.
Sub MySub()
Dim lngLastRow As Long

With Sheets("PASTE_NOTEPAD")
    lngLastRow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    .Rows("1:" & lngLastRow).Copy Destination:=Sheets("sheet1").Rows("1")
End With
End Sub

Open in new window

And you're sure you are running the updated macro, not the old 5000 row one?

Well, you could try this, it will use column B as the reference for used rows rather than A, just to see what it does.

Sub COPY_PASTE()
    With Sheets("PASTE_NOTEPAD")
        .Range("A1:A" & .Cells(.Rows.Count, 2).End(xlUp).Row).Copy Destination:=Sheets("sheet1").Range("A1")
    End With
End Sub

Open in new window


»bp
That can't be the case, there has to be something in column A.
Excel is not always accurate in its determination of the last row and I've found that Cells.Find("*",SearchOrder:=xlByRows,SearchDirection:=xlPrevious).Row is the most reliable.
Avatar of wrt1mea

ASKER

nd you're sure you are running the updated macro, not the old 5000 row one?

Well, you could try this, it will use column B as the reference for used rows rather than A, just to see what it does.

I have checked and double checked. Using column b didnt bring over the data and the macro doesnt produce the updated information in the txt file.
Avatar of wrt1mea

ASKER

I tried this from Martin Liss:

Sub MySub()
Dim lngLastRow As Long

With Sheets("PASTE_NOTEPAD")
    lngLastRow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    .Rows("1:" & lngLastRow).Copy Destination:=Sheets("sheet1").Rows("1")
End With
End Sub

It brought over the data, all 5001 rows....
There must be something in the cells all the way down to row 5000, even if it appears that they are empty.

You could try going to what you think is the start of the "empty" rows, and selecting all rows from there to the end, and deleting them.  Then see what the macro does.


»bp
It brought over the data, all 5001 rows
Is that what you wanted?
If it's not then try this

Sub MySub()
Dim lngLastRow As Long

With Sheets("PASTE_NOTEPAD")
    For lngLastRow = 5500 To 1 Step -1
        If Not IsEmpty(.Cells(lngLastRow, "A")) Then
            Exit For
        End If
    Next
    .Rows("1:" & lngLastRow).Copy Destination:=Sheets("sheet1").Rows("1")
End With
End Sub

Open in new window

Avatar of wrt1mea

ASKER

I didnt want all 5001 rows...

I tried what Bill mentioned....I highlighted the "empty" rows down past 5000 and deleted. AND IT WORKED!!!!!!!
Did my last post also work?
Avatar of wrt1mea

ASKER

Now, I need help to figure out how to exclude the quotation marks.

What I am trying to do is use this for a template to build firewalls. So the quotations being added are a result I could really do with out.
Avatar of wrt1mea

ASKER

YES Martin that also worked!!!!!!
I have no idea what your worksheet looks like, not even the headers, at this point, but what you might consider is adding an addition column to the right of your entries, and using string functions to concatenate and format the various columns you have entered into the useful "line" that you want output to a file.  Then just save this "calculated" column to the file, rather than letting Excel save the various columns and do it's own formatting and delims, etc.

It's a technique I often use for building large BAT scripts, or building test data, etc.


»bp
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of wrt1mea

ASKER

Martin, how would I incorporate your macro with this? This is the end of the macro I am currently using:

Sub Save_as_text_file()
Dim fPath As String, fname As String
Dim ws As Worksheet
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set ws = ActiveWorkbook.ActiveSheet
fname = Application.ActiveWorkbook.Path & "\" & _
        ThisWorkbook.Worksheets("BOB_BUILDER").Range("A2") & "_" & _
        ws.Name & ".txt"
fPath = Application.ActiveWorkbook.Path
ws.Copy
ActiveWorkbook.SaveAs fname, xlUnicodeText
Application.ActiveWorkbook.Close
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
The problem isn't quotes in the data Martin (as I understand it), it's commas in the data, and when the file is saved Excel adds double quotes around those columns since comma if the field delim in the output file...  Unless I read that all wrong, so hard working without seeing ay test examples...


»bp
If your quotes are the so-called "Smart Quotes" then you'll need to do it this way.

Sub MySub()
Dim cel As Range

For Each cel In Sheets("PASTE_NOTEPAD").UsedRange.Cells
    cel = Replace(cel, Chr(147), "")
    cel = Replace(cel, Chr(148), "")
Next

End Sub

Open in new window

Avatar of wrt1mea

ASKER

OK, I am working on providing an example.....gonna be a few minutes.
If ws in your code is the "PASTE_NOTEPAD" sheet then

Sub Save_as_text_file()
Dim fPath As String, fname As String
Dim ws As Worksheet
Dim lngLastRow As Long

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set ws = ActiveWorkbook.ActiveSheet
With ws
    For lngLastRow = 5500 To 1 Step -1
        If Not IsEmpty(.Cells(lngLastRow, "A")) Then
            Exit For
        End If
    Next
    .Rows("1:" & lngLastRow).Copy Destination:=Sheets("sheet1").Rows("1")
End With
fname = Application.ActiveWorkbook.Path & "\" & _
        ThisWorkbook.Worksheets("BOB_BUILDER").Range("A2") & "_" & _
        ws.Name & ".txt"
fPath = Application.ActiveWorkbook.Path
ws.Copy
ActiveWorkbook.SaveAs fname, xlUnicodeText
Application.ActiveWorkbook.Close
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

Open in new window

Avatar of wrt1mea

ASKER

Martin, it still wrapped the lines with commas in quotes....

I am working on a workbook that has data that I can provide as an example. Gonna take me a while to get it prepped.
Avatar of wrt1mea

ASKER

Thinking out loud here...

What if AFTER the creation and export of the filename.txt that another sub routine opens the filename.txt and removes the quotation marks, saves and closes???? That an option?
But then you will have more commas in the data than there are fields.  How is that file used?


»bp
Can you show a picture of what some of the commas in quotes look like?
Avatar of wrt1mea

ASKER

OK gentlemen. Please see the attached. Use the RUN_ALL macro in Sheet1. It will copy and paste data into the sheet and then export it into a text file in the same directory where you have the workbook. Take a look at line 11 in the text file. You will see it inserts the quotes any where the line has a comma.

Thanks for all of the peristence. I am leaving to get on the road for a couple of hours and will be out of the office tomorrow.
ExpEch-Example.xlsm
NAME-987_SHEET1.txt
Okay, I understand a bit better.

But I don't think there is an easy way to prevent that in Excel saveing the file, all the CSV delimited formats will wrap in quotes if the field contains a comma.  A couple of thoughts.

(1) Could you use a different character in your data beside comma, like maybe semicolon?

(2) Create a formatted column as I explained, and then only save that to text file.

(3) Write a more complicated macro that loops through all the data and does it's own formatting of the output lines and writes them to the file.  This would replace the SaveAs that you use now and would take a bit more code but is doable.


»bp
Avatar of wrt1mea

ASKER

Bill,

I have no idea what your worksheet looks like, not even the headers, at this point, but what you might consider is adding an addition column to the right of your entries, and using string functions to concatenate and format the various columns you have entered into the useful "line" that you want output to a file.  Then just save this "calculated" column to the file, rather than letting Excel save the various columns and do it's own formatting and delims, etc.

It's a technique I often use for building large BAT scripts, or building test data, etc.

Can you expand upon this?
Avatar of wrt1mea

ASKER

Bill, disregard that last comment. I am able to amend the original data to exclude commas.
Avatar of wrt1mea

ASKER

You guys are awesome!

More q's to come...
I’m glad I was able to help.

If you expand the “Full Biography” section of my profile you’ll find links to some articles I’ve written that may interest you.

Marty - Microsoft MVP 2009 to 2017
              Experts Exchange Most Valuable Expert (MVE) 2015, 2017
              Experts Exchange Top Expert Visual Basic Classic 2012 to 2018
              Experts Exchange Top Expert VBA 2018