Multiple activites - Convert format - delete all rows above the table- change tab name to: X

[david gabra]

Hello
1. I have a mutiople files in a network folder.
I required the folowing steps to work for ALL files in aspecified folder. This save me doing the below step to 100s of files manually.

1. To change the format of each file from .htm to .xlsx.
2. To remove the delete all rows above the table in each file, so the table will start at cell reference A1.
3. To change the tab name to X

Example of the before:

before



after



Thank you kindly

David.

 

[david gabra]

i just wanted to add that it is not a real table - just noted that -

 

[BobBridges]

I don't know about converting from HTML to xlsx; maybe someone else will have a great idea for that. Items 2 and 3 are easy.

To run through every file in a folder, I would use the FileSystemObject. The code for that would look something like this (untested) example:

Set ofs = CreateObject("Scripting.FileSystemObject")
Set ofo = ofs.GetFolder("C:\path\") 'or whatever
For Each ofi in ofo.Files 'loop through the files in the folder
  ' Here you do what you want to each file, perhaps these steps:
  ' 1) Check ofi.Name to be sure it ends in .html
  ' 2) Read the file and convert to Excel (we'll have to think about how that's done)
  ' 3) Change the worksheet name to "x".  (But if we're converting from HTML, why would there be a worksheet name at all?)
  ' 4) Find the cell in col A that equals "User", and delete all the rows above it.
  ' 5) Close the new workbook, saving as xlsx.
  ' Do NOT delete the old file yet; not until you know your program worked.  But you could move the html file to another location if you want.
  Next ofi

I know you don't know how to do all this, but once you start writing as much of the code as you can, I'll explain how to correct any errors and add the parts you don't know about.

 

[Marc L]

Hello, first two ways to try :​

• via Open option from the File menu
• via Copy / Paste.

Maybe other ways but as it depends on the missing source file attachment …​

​

 

[david gabra]

manythanks