save file as text file then close text file

[conception_native_0123]

hi everyone

how do i do this? i am trying to change html code inside an excel file and then move the html to another folder. right now i have 2 excel files working to do this but i do not think i need that much. here is the code in my first file after the html is imported

On Error Resume Next
'copy bookmarks to other folder
ActiveWorkbook.SaveAs Filename:= _
        "C:\exports\export_html.txt", FileFormat:=xlText, CreateBackup:=False
Workbooks.Open ("C:\exports\second_process.xlsm")
ActiveWorkbook.Close

then i am using kill statement and name statement to get my html file to the export directory. am i doing too much? do i need to post more code? thank you

 

[The_Doc_Man]

You should be able to use the SaveAs and Filename options to put files wherever they need to go. However, if you want to keep an unchanged copy of the original file, you can consider using the Workbook.SaveCopyAs Filename := xxxx syntax. Unlike .SaveAs, the .SaveCopyAs leaves the file open so you could save it again elsewhere. And ... the filename option for .SaveCopyAs can take folder information as well.

As to whether you are doing too much, that's hard to say because I'm not 100% clear on the details of your goal. Therefore, if it seems that my answer is off, it might be because I'm not clear on what you want in what format where - as well as what you DON'T want.

 

[conception_native_0123]

Thank you so much. That function work fine and it does what I need. I will post code below for what I am using to do what I need to do. But I think it can be shortened can it? Basically what I am doing is importing HTML data into a macro enabled file of excel and then my goal after that is to create an HTML file from that data inside Excel and copy that HTML file to another folder on one of our computers. So I think that why this process is unique and maybe redundant a little. But the function you recommended fix the problem find this time. I just tested it. But do you think all of this code could be reduced to less lines? Thanks.

Here is part of the first function in the first workbook

Dim wb As Workbook
Set wb = ActiveWorkbook

'clean up here
Call strip_tokens

On Error Resume Next
Kill "C:\exports\export_html.txt"
ActiveWorkbook.SaveCopyAs "C:\exports\export_html.txt"
Workbooks.Open ("C:\exports\final_process.xlsm")
wb.Close

Exit Sub

And here is the code in the second workbook then finishes the job as of now

  Name "C:\exports\export_html.txt" As _
         "C:\html_files\bm.html"
    Kill "C:\temp\bm.html"
    Name "C:\html_files\bm.html" As _
         "C:\temp\bm.html"

Thank you very much.

 

[The_Doc_Man]

Actually, you might need MORE lines to take care of error conditions, not LESS lines.

What you are doing isn't necessarily wrong but it might be considered a little bit out of date because the FileSystemObject (FSO) is another, more modern way to manipulate files. And it has more "primitives" to work with. Start with the link I provided and examine the left-hand "navigation" options to see what methods and properties you get with FSO. FAR more flexible. If you don't use it now, consider it for future efforts.

FileSystemObject object

Office VBA reference topic

docs.microsoft.com

Having said all of that above, I again state that what you are doing is not at all unreasonable. But if there is ever an error then your code won't be adequate. Using FSO could still have errors but it is easier to test whether, for example, your C:\temp\bm.html file exists before bothering to kill it.
NOTE that for new code, ON ERROR RESUME NEXT is not the best idea. On fully debugged code, if you are confident that you have everything working, it is OK - but for new code you WANT errors so you can tell what to fix. Again, not WRONG to suppress errors - but for this situation, maybe not the best idea since you were still trying to make it work.

 

[conception_native_0123]

Okay well thank you I will take a look at that object. Yes well this is not really processed for our research but I created this process as a shortcut to move data around that I need so I can do a job better in our process that's all. So are you saying that if I use file system object, my code would look better because it would be more like some code written by someone like you? I know a lot of code that I write is not the best and I don't write code as my job so.

 

[Isaac]

definitely remove on error resume next. it is always wrong in my opinion, unless used temporarily for a limited line of code for the sole purpose of testing whether there is/was an error - which isn't what you're doing, you're using it as a catch all, which will destroy any chance you have of intelligent debugging as well as testing.

think about it for a minute. on error resume next is telling vba: "if you encounter an error, don't let me know. don't tell me that anything is wrong , just keep it a secret, and keep trying to execute the next lines - keep going like this all the way to the end, and even then, don't tell me anything went wrong".

even on fully debugged and tested code. you have no way of knowing it won't start erring tomorrow. imagine giving a user a block of code and every day for 10 years they come in, make their coffee, run the code, and their job is done. for ten years nobody knows the code actually never worked. that's never ok in any job i've ever had.

 

[conception_native_0123]

yes i know but this code was experiment and not for our research. i told doc man that. if you want to see the full code i cannot post it. the site is telling me my code is spam. i cannot upload file, can i?

 

[conception_native_0123]

i tried stripping code down to one routine. then all my code. then some of it like 10 times. always got the spam message. =(

 

[Isaac]

Even for your own experiment purpose, if you include On Error Resume Next, that will effectively eliminate any chance you have of finding out what the problem is.

Anyway ... I'm not sure why the site is telling you your code is spam, but you could probably upload a zipped text file with it. (Properly indented to make it readable of course).

What is the question re: your code at this point? Are you just looking for any possible improvements or something not working?

 

[The_Doc_Man]

I believe the problem regarding SPAM posting is that conception_native_0123 is still considered a new member based on the number of posts.

 

[arnelgp]

short version without the other xlsm:

Dim wb As Workbook
Dim fs As Object

Set wb = ActiveWorkbook

'clean up here
Call strip_tokens

Set fs = CreateObject("Scripting.FilesystemObject")

On Error Resume Next
Kill "C:\exports\export_html.txt"
ActiveWorkbook.SaveCopyAs "C:\exports\export_html.txt"
wb.Close

With fs
    .CopyFile "C:\exports\export_html.txt", "C:\html_files\bm.html", True
    .CopyFile "C:\html_files\bm.html", "C:\temp\bm.html", True
End If
Set fs = Nothing
Exit Sub

 

[conception_native_0123]

thank you for all help everyone. i am so sorry i have been gone for a long time. i did resolve issue though. do i still need to post code for others to see?

 

[Isaac]

thank you for all help everyone. i am so sorry i have been gone for a long time. i did resolve issue though. do i still need to post code for others to see?

Your choice, but I think it's a nice gesture to do so, and doing so helps others in the future.

 

[conception_native_0123]

actually i havve found that i have another stumbling block. i'm sorry but this process i think i am making it too complicated. here what I am trying to do:

i am exporting html data onto a machine as html file. i want to manipulate that data inside of excel, minimizing it by taking all token and hash data out, then when I have the small version of the data, put the html file into another folder and re-upload it to our server. I think i am complicating it more than need be. here is all of my code, by using 2 excel files. i got a spam error again here, and i think the html tags in my vba code is causing it. so i upload txt files here.

the only thing that is not happening out of all of that code is that "C:\Users\jonathan\Desktop\n\web\HOSTS\LINUX\final.html" is not getting created when 2nd file's code runs. no errors are thrown. i just dont see file in folder. what am i doing wrong? is it too complex? thank you.

 

[arnelgp]

on runmigration():

Sub runmigration()
On Error GoTo err
    Workbooks(1).Close (-1)
    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"
    Exit Sub
err:
    MsgBox "Error has occured:" & Chr(10) & Chr(10) & "Description: " & err.Description
End Sub

 

[conception_native_0123]

What will that code do? Fix the only problem I have? Thank you

 

[arnelgp]

"C:\Users\jonathan\Desktop\n\web\HOSTS\LINUX\final.html" is not getting created

hopefully it will create that file.

 

[conception_native_0123]

ok i will test it and then get back to you. i need some time. i doing something else right now. thank you so much for your help.

 

[conception_native_0123]

it did not do anything. the whole process ran like before, with "break on all errors" set and i got no errors. but file still not there. can you say why?

 

[arnelgp]

can you post the Original.html?