Completed

Create VBScript to extract formulas from an Excel doc

This project was successfully completed by eugene2006 for $30 USD in 2 days.

Get free quotes for a project like this
Employer working
Completed by:
Skills Required
Project Budget
$30 - $250 USD
Completed In
2 days
Total Bids
14
Project Description

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).

Looking to make some money?

  • Set your budget and the timeframe
  • Outline your proposal
  • Get paid for your work

Hire Freelancers who also bid on this project

    • Forbes
    • The New York Times
    • Time
    • Wall Street Journal
    • Times Online