Create VBScript to extract formulas from an Excel doc
$30-250 USD
Bezahlt bei Lieferung
The purpose is to create a VBScript program that can be run from the windows command line. The command will invoked like this:
[login to view URL] [login to view URL] [login to view URL] last_cell_address [login to view URL]
[login to view URL] - 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
[login to view URL] - 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("[login to view URL]") 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:
[login to view URL](v=vs.84).aspx - VB Script reference
[login to view URL] - Using VBScript from the command line via Windows Scripting Host
[login to view URL] - Microsoft's object model for Microsoft Excel
EXAMPLE PROGRAM:
sub main()
dim app
set app = CreateObject("[login to view URL]")
[login to view URL] = False ' Hide the application while this script is running
dim fso
set fso = CreateObject("[login to view URL]")
if not [login to view URL]([login to view URL](0)) then
msgbox "Fatal Error! Unable to locate specified file:" & vbcrlf & _
[login to view URL](0),0,"Aborting!"
[login to view URL] -1
end if
dim doc_path,workbook
dim sheet_name, cell_address, value, formula, cell
doc_path = [login to view URL]( [login to view URL](0)).Path
set workbook= [login to view URL] (doc_path)
For Each sheet In [login to view URL]
sheet_name = [login to view URL]
Set cell = [login to view URL]("A1")
value = [login to view URL]
formula = [login to view URL] ' 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).
Projekt-ID: #5157008
Über das Projekt
Vergeben an:
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.
13 Freelancer bieten im Durchschnitt $85 für diesen Job
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
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.
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
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
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 Mehr
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 Mehr