10 Replies - 814 Views - Last Post: 31 December 2012 - 07:47 PM Rate Topic: -----

#1 JonesZoid  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 7
  • Joined: 29-December 12

Working Excel Program - Transfer To C#

Posted 29 December 2012 - 02:42 PM

Hi All,

I currently have a perfectly working excel program - or coded workbook if you like. But feel that a fully functioning
Software program would be better suited. I am very new to the programming world, and i have started by creating a form with a couple of textboxes for the users input required data and 5 or so buttons to do the do....so to speak.

I have searched many forums - and i can see that it MAY be possible, but im not entirely sure how to go about it.

My Excel Program does the following:
The user - fills in 3 cells (Hoping to replicate with the textboxes on the C# Form)
The user - then presses a button - one for odd or one for even.

No for the hard bit (I Think)

In excel there is an odd sheet and an even sheet both with a lot of concatenation on it from a main page which will also
include the 3 cells the user inputted earlier.

This then produces a script for the user - which is a copy and paste of either the odd or even script.

-------

I suppose im asking - can this be done in C# or can i place the information in the background to work with to produce the usable script.

I dont want it to be done for me - i would like to try and do that myself - just some pointers or snippets of use.

Yours

Craig

Is This A Good Question/Topic? 0
  • +

Replies To: Working Excel Program - Transfer To C#

#2 andrewsw  Icon User is online

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3517
  • View blog
  • Posts: 12,007
  • Joined: 12-December 12

Re: Working Excel Program - Transfer To C#

Posted 29 December 2012 - 02:56 PM

Quote

This then produces a script for the user - which is a copy and paste of either the odd or even script.


I don't follow: what is the final output for the user? And by odd and even do you mean that these are the names of two existing worksheets?

Do you want someone to run your C# application, which opens a form; they then fill in the textboxes and click a button, which opens (automates) Excel?
Was This Post Helpful? 0
  • +
  • -

#3 JonesZoid  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 7
  • Joined: 29-December 12

Re: Working Excel Program - Transfer To C#

Posted 29 December 2012 - 03:07 PM

I wish i could upload a copy of the workbook - but there is far too much sensitive info.

Basically - if the user requires an odd script making. (In Excel)
The 3 cells get filled in by the user and the odd button would be pressed.

In Excel - a hidden ODD sheet would be selected and in column c - there would be the main script.
Within that script there are lots of places where the original 3 cells would be concatenated.
Next - Column c would be copy and pasted (Just Text) into column A on the same ODD sheet
Next - Column a would be copy and pasted onto a scripting sheet for the user to copy and paste from.

This would also be the same for the even.

So could this be done within C# or would it need to manipulate Excel to do this.

Hope this helps.
Was This Post Helpful? 0
  • +
  • -

#4 JonesZoid  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 7
  • Joined: 29-December 12

Re: Working Excel Program - Transfer To C#

Posted 29 December 2012 - 04:28 PM

All,
If it helps - i have stripped all the sensitive material and put the workbook to its very basic.

The Copy To Notepad does work normally - but not in this example - i already have got this function to work in C#

So i suppose the answer to my question - can this be reproduced in C#??

Craig

Attached File(s)


Was This Post Helpful? 0
  • +
  • -

#5 andrewsw  Icon User is online

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3517
  • View blog
  • Posts: 12,007
  • Joined: 12-December 12

Re: Working Excel Program - Transfer To C#

Posted 29 December 2012 - 05:04 PM

Well, yes this could all be done in C#; it's simply copying some data around and outputting it as either a string (for copying) or to a text file.

The question is, will you need to work with the Excel file? Do you need it at all? Perhaps it is a much bigger file than you have shown.

The following information demonstrates Excel/COM Automation, or there is newer VSTO technology to do the same thing. It is in VB.Net but would be very similar for C#.

#####
Firstly, you need to add a reference to Excel for your Project. Choose the Project menu, Add Reference. Then the COM tab, and double-click "Microsoft Excel 14.0 Object Library" – 14.0 refers to Office 2010.

Add the following at the beginning of your class or code module:

Imports Excel = Microsoft.Office.Interop.Excel

'Excel' is then an alias to the Excel Object Model within your module – you could use a different alias. Refer to the following example, which includes "clean up" code, most of which may not be necessary with simple examples.

Public Sub AutomateExcel()
    Dim xl As Excel.Application
    Dim wb As Excel.Workbook
    Dim ws As Excel.Worksheet

    Try
        xl = New Excel.Application
        wb = xl.Workbooks.Add()
        ws = CType(wb.Worksheets(1), Excel.Worksheet)
        xl.Visible = True
        ws.Range("A1").Value = "Hi Excel"
    Catch ex As Exception
        Debug.WriteLine(ex.Message)
    Finally
        If Not xl Is Nothing Then
            'Cleanup:
            GC.Collect()
            GC.WaitForPendingFinalizers()
            GC.Collect()
            GC.WaitForPendingFinalizers()
            Marshal.FinalReleaseComObject(ws)
            If Not wb Is Nothing Then
                wb.Close(False) 'SaveChanges:=False
                Marshal.FinalReleaseComObject(wb)
            End If

            xl.Quit()
            Marshal.FinalReleaseComObject(xl)
        End If
    End Try
End Sub


.NET now includes Office Developer tools (VSTO) to assist in Office development and the creation of add-ins.
#####

BTW using SendKeys is not recommended; preferable would be to use simple File IO to save the text into a text-file, and then open it in Notepad if required.

This post has been edited by andrewsw: 29 December 2012 - 05:05 PM

Was This Post Helpful? 1
  • +
  • -

#6 JonesZoid  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 7
  • Joined: 29-December 12

Re: Working Excel Program - Transfer To C#

Posted 29 December 2012 - 05:07 PM

thanks for your help andrewsw.

This will be a great help i think - it is bed time for me currently, but i will look at it tomorrow.

thanks again

Craig
Was This Post Helpful? 0
  • +
  • -

#7 andrewsw  Icon User is online

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3517
  • View blog
  • Posts: 12,007
  • Joined: 12-December 12

Re: Working Excel Program - Transfer To C#

Posted 30 December 2012 - 06:49 AM

View PostJonesZoid, on 29 December 2012 - 05:07 PM, said:

thanks for your help andrewsw.

This will be a great help i think - it is bed time for me currently, but i will look at it tomorrow.

thanks again

Craig


If the primary purpose of your C# application will be to manipulate Excel then I would question the efficacy of this. There is nothing that C# might do that you couldn't do directly from Excel VBA, and more efficiently.

The only difference is that the C# would run as an independent application. However, if you wanted to, you could create a UserForm in VBA, display it on opening the Excel-file, minimize the Ribbon, etc., so that it (almost) behaves as an application.

The alternative would be to drop the Excel file entirely and program everything in C#; but I assume you still require the Excel file for its other content. Andy.
Was This Post Helpful? 0
  • +
  • -

#8 JonesZoid  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 7
  • Joined: 29-December 12

Re: Working Excel Program - Transfer To C#

Posted 30 December 2012 - 03:01 PM

You have it all in a nutshell there.

I did want to create just an application - which i know i can do excel and have done to an extent.

I would still probably require the excel file - unless there is a way to do it within the C# environment, but being relatively new to programming, probably would find this above my capabilities for my first attempt.

Craig
Was This Post Helpful? 0
  • +
  • -

#9 andrewsw  Icon User is online

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3517
  • View blog
  • Posts: 12,007
  • Joined: 12-December 12

Re: Working Excel Program - Transfer To C#

Posted 30 December 2012 - 03:31 PM

If the data in the spreadsheet were completely static then, if you want to create an entirely independent C# application, you might store this data in a text or XML file within the application; after all, you won't have a spreadsheet to keep the data in.

If I assume that the Excel data is not static, and it needs to be modified at certain points, then the data would need to be stored in a (perhaps shared) database. This makes it a much bigger C# project.

I suggest that you might explore C# completely independently of your Excel work. You would need to start here anyway :smile2:.
Was This Post Helpful? 0
  • +
  • -

#10 Curtis Rutland  Icon User is online

  • (╯□)╯︵ (~ .o.)~
  • member icon


Reputation: 4496
  • View blog
  • Posts: 7,850
  • Joined: 08-June 10

Re: Working Excel Program - Transfer To C#

Posted 31 December 2012 - 10:05 AM

Quote

I suggest that you might explore C# completely independently of your Excel work. You would need to start here anyway


That's a good suggestion. Office Interop or VSTO isn't exactly the best place to start learning C#; they're advanced topics that will frustrate a new user more than teach them.

Don't try to bite off more than you can chew. Also, don't reinvent the wheel. I think you've gotten great advice in this thread: Excel is more suited to solving this problem than a standalone program.
Was This Post Helpful? 0
  • +
  • -

#11 AdamSpeight2008  Icon User is offline

  • MrCupOfT
  • member icon


Reputation: 2263
  • View blog
  • Posts: 9,468
  • Joined: 29-May 08

Re: Working Excel Program - Transfer To C#

Posted 31 December 2012 - 07:47 PM

Start out with pencil and paper first.
- What problem are you attempting to solve?
- Design the solution.
- Do you really need to program? Is it non-technical problem? Solvable by training?
- What algorithm are you going to use?
- Code up the solution.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1