Do some Excel Work

Closed Posted May 23, 2014 Paid on delivery
Closed Paid on delivery

We are looking for someone with experience writing VBA code or script in MS Excel 2003.

The VBA code would be in the form of a macro accessible via an Excel spreadsheet.

We need the VBA code to be able to open a specific .html or .php file and scan the HTML code to parse some strings, perform some simple math and copy the results into an Excel spreadsheet. The HTML file consists of a fixed set of underlying code that contains inventory from our local vendors. The HTML code does NOT need to be changed. We just need it to be read by the VBA script.

The VBA script needs to look for specific part numbers that are already in the HTML code. The part numbers are searched individually. For each part number found, what follows is a block of code that shows the names of our vendors who carry the part and the quantity they have in stock. There are usually several vendors who have the part number in stock.

There would be a specific list of vendor names to search for and this list would likely have to be included in the underlying VBA script. If the vendor name is located, the quantity would be parsed and added to a variable. If the vendor name is NOT located, the script would move on to the next vendor name and if found, parse the quantity and add it to the previous quantity and so forth. Vendor names will be listed in the HTML code as long as they have at least quantity 1 of the part number in stock.

Looking at the HTML code, the unique string “Part #: “ (no quotes) seems to proceed each block of code for a particular part number, for example, “Part #: X1563”. Note that there is a space between the colon and the first character of the part number. What follows is the name of a vendor in a unique string in the form “>Flagship Technologies<” (no quotes). The quantity is always proceeded by the code “<td align="right" class="search">X</td>” (no outer quotes) where X is the quantity as an integer.

So it would seem to us that the VBA script would have to look for the part number following “Part #: “ and perform a loop operation to scan the rest of the code in that block to look for each vendor name on the list and then parse the quantity for that vendor and add it to the quantity variable. The end of the search for that part number would be when another “Part #: “ string is reached containing a different part number. Prior to moving to the next part number, the VBA script would put the part number and the total quantity of all vendors for that part number on a single line in an excel file. The VBA script would then proceed to the next part number and the process continues until the end of the file. There would be hundreds to thousands of part numbers to process. The result is an Excel file with hundreds to thousands of lines. Each line is a part number and quantity. The vendor names to NOT need to be copied to the Excel file.

I have included a sample file of the HTML code to parse called main.php. This file would be included on the hard drive of the computer that is to run the Excel VBA script.

I believe the VBA script would start something like the following, although there might be a better way.

Sub OpenTextFile()

Dim FilePath As String

FilePath = "C:\Users\[login to view URL]\Desktop\[login to view URL]" 'the path to the HTML file to parse

Open FilePath For Input As #1

row_number = 0

Do Until EOF(1)

<various commands>

Loop

End Sub

We would like to pay a one-time fixed price to get this project completed in about a week or so.

Data Processing Excel HTML Visual Basic for Apps

Project ID: #5981327

About the project

28 proposals Remote project Active Jun 29, 2014

28 freelancers are bidding on average $174 for this job

zExcel

Hi, I'll be happy to help you with this project Please feel free to message me if you need to discuss any questions you might have prior to award

$222 USD in 3 days
(256 Reviews)
7.6
seaanddream

Hi, your 5-star excel & vba coding expert is ready to help your project. pls check my profile and feedbacks first to have some idea about the quality of my work... I had many similar excel/vba tasks, all achieved with More

$194 USD in 5 days
(273 Reviews)
7.7
maverick44000

HI there. This project shall not take more than 2 days to complete. I am ready to begin with it right away and would be able to deliver the exact results. Kindly discuss prior to offering. I am also willing to get the More

$160 USD in 2 days
(71 Reviews)
6.7
svet63

Excel/VBA developer since 1997. This is all doable, however, there might be an issue. Excel 2003 holds up to around 65,000 rows of data per sheet (unlike all newer versions which hold over 1,000,000 rows). Do you have More

$333 USD in 5 days
(67 Reviews)
6.5
fabest

Dear, I am Excel/VBA automation Expert since 11years. I propose to perform parse from Access and generate final result in Access Table, It would be more user friendly, or I can also do it in xls if you want. I will More

$148 USD in 4 days
(60 Reviews)
7.1
avinav606lx

Hey There, Thanks for posting the project. Its looks feasible and am Interested to do it. Next steps: Lets discuss/validate the complete requirement and I can start to get this done with required quality ou More

$249 USD in 3 days
(158 Reviews)
6.4
dpune

Hi, I have more than 14 years of exp and I am expert in this kind of work. I have completed more than 210 projects. Please look at the feedback left by my employers to know more about my work. Waiting for your positive More

$100 USD in 5 days
(125 Reviews)
6.3
MuradMurad

Hi, I have lots of experience with automation in Excel using vba macros or with php and can help you in short terms with this project. I will work on a demo with provided requirements.

$244 USD in 3 days
(11 Reviews)
5.3
tomislav1975

Yes I can, and I will help you if you pick me for your project. Check my profile, see my reviews and award me now. Thanks, Tom

$155 USD in 3 days
(34 Reviews)
5.3
JonNxt

Hi there, I've been working with Excel since before '98 and I've been using all possible versions of it. I have many years of experience working with macros and writing code / debugging Excel VBA code. You may che More

$180 USD in 3 days
(34 Reviews)
4.9
salih0vicx

Hi, I've done numerous tools for my HR team involving VBA automation. I most cases I have been bridging between Office (Excel, Word, PowerPoint, Access, Outlook) and PDF, SAP reports and so one... Thank you for More

$220 USD in 3 days
(13 Reviews)
4.7
diegogarcia0531

Hi, I can do the job, I need a html sample file to make sure the macro will work on both php and html files, I will develop an interactive form to make search customization. I hope you consider me to develop this inte More

$150 USD in 3 days
(21 Reviews)
4.9
abdellatif81

Hello, I am IT engineer with European diploma since 2005 Certified as Technical specialist in Microsoft SQL Server databases, i have more than 8 years in IT projects with 5 years working around Microsoft Excel data More

$263 USD in 3 days
(8 Reviews)
4.9
finecodervw

Hi, Let me introduce myself as an expert VBA programmer. I have used VBA extensively for Excel, MS Word etc.. I have more than 10 years of experience to my credit. I have also worked on advanced VB features li More

$150 USD in 3 days
(25 Reviews)
4.7
adim

Hello there, I'm a software engineer with more than 15 years experience in Excel, Access, VBA, VB6, .NET, SQL. I have done many similar projects in the past and can complete this without any problem. Regards, A More

$155 USD in 3 days
(9 Reviews)
4.1
sheyac

While I'm new to this site, I have extensive experience in doing exactly the sort of thing this project requires. I can guarantee the quality of my work and your complete satisfaction. My acute attention to detail, More

$199 USD in 3 days
(3 Reviews)
4.0
cheungkc

Hi Further to my suggestion of searching for <td>, the source code suggest we should search for "my_ven_edit" and/or "my_ven_add" leading to company names. Price and quantity will follow.

$166 USD in 7 days
(6 Reviews)
3.1
Subburaj01

Hi There , How are you doing? I have done a very similar project in the recent past ( you can see the project under my profile as in progress "Use Access VBA to parse an encapsulated XML File") for a UK employer. More

$177 USD in 3 days
(3 Reviews)
3.2
daveclary

I write similar scripts like this about 2-3 times a month. I can put one together for you this week for whatever version of Excel you'd like (except maybe Office 365. I don't have that one yet). Bid includes free re More

$166 USD in 5 days
(1 Review)
2.5
Humphrykim

My skills are ideal and I assure you quality work delivered within the agreed time. Customer satisfaction is one of my key objectives in all of my projects. I will ensure that I provide you with value for your money. More

$250 USD in 4 days
(0 Reviews)
0.0