Yet another VB form to Excel transfer question

[RobT]

I have this form built in VB 2010 that mirrors an Excel spreadsheet. I've been at this for a month and I can't get this to work. I need the user to run the program and fill out the form throughout the day. Then I need them to save it to a corresponding date in the workbook with the fields having their data populated and transferred from the form.

I can't for the life of me get this to work and I am running out of options.

The workbook already has formulas in place to do some calculating, but I can't get the data over from the form.

Here it is. The workbook is inside the zip file. Anyone want to take a crack at it?

[Stewart Ross]

Hi RobT. We will always try to help if you could post something specific for us - an excerpt from a code module which is not working as you would expect, say.

We can't help you if you simply post a VB project - there is no way that contributors can or will wade through it.

Bear in mind that unknown code could pose a security risk to the person downloading it, so asking others to download an unknown project on trust is just not going to get you anywhere.

-Stewart

[RobT]

Thanks Stewart- you've made some valid points and I respect what you said. With that, I have a text box that will have data put in. I have a spreadsheet- and I've been pulling my hair out trying to get data from the text box to a cell in the spreadsheet.

Any sample code? I don't have any code because nothing is working. It seems there are so many different ways to do the same thing, I'm starting to really get confused.

I've included a screenshot of the program, and the spreadsheet to help visualize the project.

For example, the first(row) start time is called Start1. It needs to go in A4. End1 (the second textbox) needs to go in B4. Time1 (the third) needs to go in C4. StartMilesBox needs to go in C30, EndMilesBox in C29, etc etc...

The spreadsheet has the formulas built in, then at the end of the month the workbook gets emailed to the Sergeant. Then he complies it and sends the stats to the Sheriff.

We use the spreadsheet, but it's a pain. I thought building a Daily Log program would be easier for us to compile this stuff- a nicer GUI and a better way to work.

Long term is to get this done, then integrate it with a database, then the Sheriff can compile his stats from the database. I'll deal with that later on though, I just need to get this squared away.

[Stewart Ross]

Hi RobT. Your daily log application would suit direct design within Access, for instance, if you can identify and define the base tables and their relationships involved. You'd save a lot of time and effort, but that's for later.

I show some example VBA code (not VB) below which transfers the contents of two controls from an Access form to an existing Excel worksheet. It's what I had to hand at present and does not relate to the log application you have, but the principles of accessing values in a form and copying them across to Excel are identical.

What you will need to be clear about for yourself is how to loop through the records in your log and transfer the contents of the records to your Excel sheet. This is something that if you were using an ADO recordset, say, as your recordsource for the log table you could do automatically, as there is a range method in Excel called CopyFromRecordSet which will copy all rows of the data from a given recordset as a single operation. If you are to do this yourself then you'd need to loop one row at a time through all the controls on your source form and copy them to Excel accordingly.

The Cells() object I am using in the sample code is a way of referring to individual cells within a worksheet without using the A1, B2 etc notation. This lends itself to processing within loops, where instead of trying to refer to B4, B5 and so on you just set up loop counters as row and column long int numbers, then refer to the rows and columns within the loops using their numeric offsets. Cell(1, 1) is A1, Cell(2, 1) is A2, Cell(1, 2) is B1, and so on.

You need to include a reference to the Excel object library in your project, otherwise you will not be able to use Excel objects and methods at all.

Private Sub cmdExcel_Click()
    Dim objExcel As Excel.Application
    Set objExcel = New Excel.Application
    With objExcel
        .Visible = True
        .Workbooks.Open ("C:\test.xls")
        With .ActiveSheet
            .Cells(1, 1) = "Date of Birth"
            .Cells(1, 2) = "Anniversary"
            .Cells(2, 1) = Me.DOB
            .Cells(2, 2) = Me.Anniversary
        End With
        .ActiveWorkbook.Save
        .Quit
    End With
    Set objExcel = Nothing
End Sub

-Stewart

[RobT]

Thanks for your reply, your answer makes sense. I'm going to work this out and make it happen.

Thanks again!