save file as text file then close text file (1 Viewer)

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:15
Joined
May 7, 2009
Messages
19,175
this is a single excel:
Code:
Sub ProcessIt()

If runprocess Then
    'import here
    Call importprocess
    Range("a1").Select

    'clean up here
    Call changedata
    
    Kill "C:\Users\jonathan\Desktop\n\final.txt"
    
    ActiveWorkbook.SaveAs "C:\Users\jonathan\Desktop\n\final.txt", FileFormat:=xlText, _
                           CreateBackup:=False
    Call runmigration
End If

    ActiveWorkbook.Save
        If MsgBox("data have been updated." & Chr(10) & Chr(10) & _
                  "Would you like to close the file?", vbExclamation + vbYesNoCancel, "flag") = vbYes Then
            
            Application.Quit
        End If

End Sub

Private Function runprocess() As Boolean
On Error GoTo err
Dim c As Long

'delete previous queries here
If ActiveSheet.QueryTables.Count > 0 Then
    For c = ActiveSheet.QueryTables.Count To 1 Step -1
        ActiveSheet.QueryTables(c).Delete
    Next c
End If

ActiveSheet.Cells.Select
Selection.ClearContents
runprocess = True
Exit Function

err:
    MsgBox "Error has occured:" & Chr(10) & Chr(10) & "Description: " & err.Description, , "RunProcess"
    runprocess = False
End Function

Private Function importprocess() As Boolean
    With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;file:///C:/Users/jonathan/Desktop/n/original.html", Destination:=Range( _
        "$A$1"))
        .Name = "bm"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlEntirePage
        .WebFormatting = xlWebFormattingNone
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With
    importprocess = True
End Function

Private Function changedata() As Boolean
Const top As Long = 9
Dim bot As Long
Const href As String = "HREF="
Const arrowRight As String = ">"
Const arrowLeft As String = "<"
Dim rReplace As String
Dim rTemp1 As String
Dim rTemp2 As String
Dim start1 As Long
Dim finish1 As Long
Dim start2 As Long
Dim finish2 As Long
Dim length As Long
Dim r As Range

Application.ScreenUpdating = False
bot = Range("a" & top).End(xlDown).Row

    For Each r In Range("a" & top, "a" & bot)
        If InStr(r, href) > 0 Then
            length = Len(r)
            start1 = 1
            finish1 = InStr(r, "ADD_DATE") - 2
            rTemp1 = Left(r, finish1)
            rTemp1 = rTemp1 & arrowRight
            start2 = InStrRev(r, arrowRight, length - 1) + 1
            finish2 = length + 1
            rTemp2 = Mid(r, start2, finish2 - start2)
            rReplace = rTemp1 & rTemp2
            r = rReplace
        End If
    Next r

Application.ScreenUpdating = True
changedata = True
End Function

Private Function runmigration() As Boolean
On Error GoTo err
    Name "C:\Users\jonathan\Desktop\n\final.txt" As _
         "C:\Users\jonathan\Desktop\n\final.html"
    Kill "C:\Users\jonathan\Desktop\n\web\HOSTS\LINUX\" & _
         "final.html"
    FileCopy "C:\Users\jonathan\Desktop\n\final.html", _
         "C:\Users\jonathan\Desktop\n\web\HOSTS\LINUX\" & _
         "final.html"
    runmigration = True
    Exit Function
err:
    MsgBox "Error has occured:" & Chr(10) & Chr(10) & "Description: " & err.Description
    runmigration = False
End Function
 

conception_native_0123

Well-known member
Local time
Today, 10:15
Joined
Mar 13, 2021
Messages
1,826
this is just a snip of the file. but it all looks like this
Code:
<!DOCTYPE NETSCAPE-Bookmark-file-1>
<!-- This is an automatically generated file.
     It will be read and overwritten.
     DO NOT EDIT! -->
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=UTF-8">
<TITLE>Bookmarks</TITLE>
<H1>Bookmarks Menu</H1>

<DL>
    <HR>    <HR>    <HR>    <HR>    <DT><H3 ADD_DATE="1605951312" LAST_MODIFIED="1618128304" PERSONAL_TOOLBAR_FOLDER="true">Bookmarks Toolbar</H3>
    <DL>
        <DT><H3 ADD_DATE="1606585052" LAST_MODIFIED="1618079661">Clinical</H3>
        <DL>
            <DT><H3 ADD_DATE="1616998613" LAST_MODIFIED="1617045342">People</H3>
            <DL>
                <DT><A HREF="https://omaha.vancethompsonvision.com/dr-baartman" ADD_DATE="1617000140" LAST_MODIFIED="1617000144">Baartman</A>
                </DT>
                <DT><A HREF="https://omaha.vancethompsonvision.com/dr-goertz" ADD_DATE="1617000158" LAST_MODIFIED="1617000159">Goertz</A>
                </DT>
                <DT><A HREF="https://omaha.vancethompsonvision.com/research" ADD_DATE="1610976696" LAST_MODIFIED="1610979115">Research</A>
                </DT>
            </DL>
            </DT>
        </DL>
        </DT>
    </DL>
    </DT>
</DL>
 

conception_native_0123

Well-known member
Local time
Today, 10:15
Joined
Mar 13, 2021
Messages
1,826
well you asked for html code. right? i just post that. how can i use your last posting of code in one single excel file? that's just like my use of 2.
i will combine into one once i get this fixed. thank you.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:15
Joined
May 7, 2009
Messages
19,175
i made an xlsm file from your code with slight modification.
run ProcessIt() sub.
the excel will close when you press OK at the end.
since we SaveAs, the displayed excel file is no longer the .xlsm
but .html so there is no point not to close it.
 

Attachments

  • conception.zip
    22.9 KB · Views: 108

conception_native_0123

Well-known member
Local time
Today, 10:15
Joined
Mar 13, 2021
Messages
1,826
arne,

it works failrly well. but, this code that i put in is not deleting all the rows. is that because code is running too fast?
Code:
Dim delRows As Boolean
Const dept2 As String = "dept2"

    For Each r In Range("a" & top, "a" & bot)
      If delRows Then
        Rows(r.Row & ":" & r.Row).Select
        Selection.Delete Shift:=xlUp
      Else
        If InStr(r, href) > 0 Then
          length = Len(r)
          start1 = 1
          finish1 = InStr(r, "ADD_DATE") - 2
          rTemp1 = Left(r, finish1)
          rTemp1 = rTemp1 & arrowRight
          start2 = InStrRev(r, arrowRight, length - 1) + 1
          finish2 = length + 1
          rTemp2 = Mid(r, start2, finish2 - start2)
          rReplace = rTemp1 & rTemp2
          r = rReplace
        End If
          If InStr(r, music2) > 0 Then
            delRows = True
          End If
      End If
    Next r

it is deleting most data. but some links in folders are being are not. and index of dept1 and dept2 folders, in the html code are both 1 and 2. so should not be happening. :(
 

conception_native_0123

Well-known member
Local time
Today, 10:15
Joined
Mar 13, 2021
Messages
1,826
arne,

i just noticed that my code is not deleting any rows at all. i recorded code with macro recorder. what am i doing wrong? the string is actually in the data at the start point. so every row after that should be getting deleted.
 

Isaac

Lifelong Learner
Local time
Today, 08:15
Joined
Mar 14, 2017
Messages
8,738
ok let me know if it helps.

yeah - when you loop 'forward'/normally, it messes with vba's brain.....because the Deletions actually dynamically affect the Index of the loop....so it gets very confused as to precisely what loop index iteration it's on, because that paradigm is being changed real-time by deletions. thus when you delete rows by looping forward/normally, it tends to do a few here, a few there--seemingly arbitrary, but generally correlating to how the Index is being changed real time as you Delete actual chunks of it.
 

conception_native_0123

Well-known member
Local time
Today, 10:15
Joined
Mar 13, 2021
Messages
1,826
because the Deletions actually dynamically affect the Index of the loop..
yes i know that now. i ran test. and yes, it skips every other row or something like that. i will post again after finishing it. i need to see anymore issues. i dont think so though. i will post again here soon i hope. thank you
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:15
Joined
May 7, 2009
Messages
19,175
why not save first the range address to be deleted in a collection object.
then later loop through this collection (starting from end).
Code:
Dim RangeToDelete As New Collection
Dim r As Range, i As Long
Const dept2 As String = "dept2"

    For Each r In Range("a" & top, "a" & bot)
        If InStr(r, href) > 0 Then
          Length = Len(r)
          start1 = 1
          finish1 = InStr(r, "ADD_DATE") - 2
          rTemp1 = Left(r, finish1)
          rTemp1 = rTemp1 & arrowRight
          start2 = InStrRev(r, arrowRight, Length - 1) + 1
          finish2 = Length + 1
          rTemp2 = Mid(r, start2, finish2 - start2)
          rReplace = rTemp1 & rTemp2
          r = rReplace
        End If
          If InStr(r, music2) > 0 Then
            RangeToDelete.Add r.Address
          End If
      End If
    Next r
   
    For i = RangeToDelete.Count To 1 Step -1
        Range(RangeToDelete.Item(i)).Select
        Selection.Delete shift:=xlUp
    Next
   
    Set RangeToDelete = Nothing
    Set r = Nothing
 

conception_native_0123

Well-known member
Local time
Today, 10:15
Joined
Mar 13, 2021
Messages
1,826
i having more trouble guys. arne, your final code resulted in my file being moved, yes, but the links in the html file all came out like this when looking at them in a browser program..

Code:
https://www.domain.com/final.html

so, i see a hyperlink in the browser on the page, but that address above is for every hyperlink. why is that? it was almost done, but now i notice new problem.
 

conception_native_0123

Well-known member
Local time
Today, 10:15
Joined
Mar 13, 2021
Messages
1,826
maybe i'm not doing this the standard waay i should be. i mean, this is an html file being uploaded to a server page. html file. but still, in excel all markup looks fine. but when i upload, it displays like that. :(
 

conception_native_0123

Well-known member
Local time
Today, 10:15
Joined
Mar 13, 2021
Messages
1,826
i think i have bothered too many of you about this. i am going to play with it a little more to find the right solution. i have enough help from all of you. thank you. you took enough time to help me.
 

Users who are viewing this thread

Top Bottom