In Progress

Create VBScript to extract formulas from an Excel doc

The purpose is to create a VBScript program that can be run from the windows command line. The command will invoked like this:

[url removed, login to view] [url removed, login to view] [url removed, login to view] last_cell_address [url removed, login to view]

[url removed, login to view] - name of a valid Microsoft Excel file with multiple sheets

last_cell_address - excel cell name such as B27 or AJ43 which is the bottom right cell to be scanned

[url removed, login to view] - name of file to send output results (overwrite if file already exists)

The script should produce a CSV formatted text output file that contains one row in the output file for each numeric or formula cell in the excel document. Blank cells or cells that contain text or mixed text and numbers are not written to the output file.

The format of the output CSV file has three columns:

sheet_name,cell_address,number_or_formula

sheet_name - name of the sheet where this cell is found

cell_address - numeric address of the cell such as K15

number_or_formula - displays the formula text if a formula is used or numeric value if the cell does not contain a formula

The script should scan each Worksheet in the Excel document. For each sheet it should scan all the cells from A1 to the "last_cell_address" passed in on the command line. For example, if the last cell address were given as "C3" then the script should scan A1, A2,A3,B1,B2,B3,C1,C2,C3 of each worksheet in the document. As mentioned above, if a cell does not contain a number or a formula, it should not be sent produce a row in the output file.

The developer will need to have Microsoft Excel installed on their computer to be able to write this program because the user will have to use CreateObject("[url removed, login to view]") in order to open the Excel file. At the bottom of this description is the code for a similar VBScript program that shows most of what needs to be done (although the code is not exactly correct).

FOR REFERENCE:

[url removed, login to view](v=[url removed, login to view]).aspx - VB Script reference

[url removed, login to view] - Using VBScript from the command line via Windows Scripting Host

[url removed, login to view] - Microsoft's object model for Microsoft Excel

EXAMPLE PROGRAM:

sub main()

dim app

set app = CreateObject("[url removed, login to view]")

[url removed, login to view] = False ' Hide the application while this script is running

dim fso

set fso = CreateObject("[url removed, login to view]")

if not [url removed, login to view]([url removed, login to view](0)) then

msgbox "Fatal Error! Unable to locate specified file:" & vbcrlf & _

[url removed, login to view](0),0,"Aborting!"

[url removed, login to view] -1

end if

dim doc_path,workbook

dim sheet_name, cell_address, value, formula, cell

doc_path = [url removed, login to view]( [url removed, login to view](0)).Path

set workbook= [url removed, login to view] (doc_path)

For Each sheet In [url removed, login to view]

sheet_name = [url removed, login to view]

Set cell = [url removed, login to view]("A1")

value = [url removed, login to view]

formula = [url removed, login to view] ' Need code to handle when the Formula is Null indicating no formula

msgbox sheet_name & vbCrLf & "A1: " & value & vbCrLf & formula

Next

end sub

Please do not offer to build this program in Visual Basic. It must be built in Visual Basic Script (VBS).

Skills: Excel, Microsoft, Visual Basic

See more: create vbscript, script sheets, microsoft excel formulas, formula it, excel command line, developer of visual basic, command line excel, blank model sheet, vbscript for, excel sheet formula, developer in excel, Visual Basic Excel, vbscript, vb excel, scan to text, formulas sheets, excel visual basic, excel to app, EXCEL MODEL, excel developer, en excel, create excel worksheet, create an excel, c3, c1

About the Employer:
( 25 reviews ) Tempe, United States

Project ID: #5157008

Awarded to:

eugene2006

Hi. Your task looks interesting. I see that most of coding is already done, and just need to save data in csv file if formulas exist in cell. Regards, Eugene.

$30 USD in 2 days
(14 Reviews)
3.9

14 freelancers are bidding on average $84 for this job

nayabsiddiqi

can provide you the vb script that will run on the workbook, read each cell of each worksheet and provide you the formula with reference in CSV format. hope to hear from you Regars

$89 USD in 0 days
(20 Reviews)
5.7
MimiFL

Well hi, i m serious freelancer with 100% completion rate (check my profile), can you please just open chat, and i will give you script to test. Also tell me whats your budget for this program? Btw i've never took a More

$55 USD in 0 days
(10 Reviews)
3.8
mrbrave

Hi...I am expert in programming Excel Through VBS...I am available to start now and can provide complete script within few hours...I have read all details and available to start now....lets start.

$64 USD in 0 days
(125 Reviews)
5.8
microk

Dear PrecisionProject, VBA, batch and vbs expert here.. Please award project. I'll deliver this within 24 Hrs. Best Regards, Microk http://www.extremeexcelsolutions.weebly.com

$50 USD in 3 days
(29 Reviews)
4.4
CARLOSHUGGINS

Consider it done.... I'm 12 year experienced developer.... I will deliver high quality results meeting all you requirements.... please have a look at mu profile and see what employers are saying about me.... thank for More

$55 USD in 3 days
(6 Reviews)
3.8
dpune

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

$138 USD in 3 days
(27 Reviews)
4.8
amelnyk

Hello, I checked project description and ready to create vbs script as per your specification. Ready to start after my bid is selected and complete in half a day. Have solid experience in software development. Yo More

$30 USD in 1 day
(12 Reviews)
3.9
gangabass

Hi, I'm Excel expert that's why I'm sure you'll be impressed with my work. I can create such VBScript program for you in less than two days and I can offer you best price here. Thanks. Roman

$154 USD in 2 days
(53 Reviews)
5.0
mantislin

Hi sir, I am scraping expert, I have did too many similar projects, please check my feedback then you will know. Can you tell me more details? then I will provide demo data for you. Thanks, Kimi

$250 USD in 5 days
(39 Reviews)
5.6
bharanisrihari

Hi, I'm an Excel expert / VBA programmer. I can get your requirements done perfectly! Please refer the excellent reviews for my completed projects in Freelancer.com. One of the reviews for your reference: "Where More

$66 USD in 1 day
(67 Reviews)
6.4
burhan393

Hey! I have done a lot of projects in VBA and have also written VBA script for a Fortune 500 company - Emerson Electric Co. I have also successfully completed two VBA projects over here at Freelancer, the review and fi More

$50 USD in 1 day
(2 Reviews)
1.8
SajithaSunil24

I have extensive work experience in Vbscript and excel macros. I have handled similar tasks before. The Vbscript development and testing will be completed in a day. Extended testing at host site or minor change requ More

$83 USD in 2 days
(0 Reviews)
0.0
sanutv

Hi, I would like to take up this project. I understood the requirements specified and can finish the same according to your timeline. I do have good experience in .Net Technologies (ASP.Net MVC, C#, WCF, WPF, Web AP More

$55 USD in 3 days
(1 Review)
0.1