Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


How to store data on different excel sheets and lines based on "date" and data  gathered from a source.

Posted on 2005-03-25
Medium Priority
Last Modified: 2010-05-02
I have created code to get the data in and place it on sheets in a specific format, now i need figure out how to automate the process.

Importing data from other systems.  Here is what i need to do.

Gather the data (data storage date)  (Data storage time) (Data itself)
Decide based (Data Storage Date) what spread sheet to store data into (Janurary thru December)
Then decide which cell areas to store data into based on the (data storage Date)

In The end, each monthly spreadsheet would have a filled out sheet for the month and have gathered all of the proper values and time for each day.

This data could be printed out as just a data sheet or some sort of graph depending on the my needs.

I have wants for this data to collect data on cooling, power consumption, totalization and peak power readings and peak times.

Everything revolves around sample dates and times.

Any ideas?

PS using excel from office 2000 thru 2003.  Greg
Question by:gregbems
1 Comment

Accepted Solution

wraith821 earned 2000 total points
ID: 13644916
you need to add the reference to "Microsoft Excel 9.0 Object Library" to your application.
then the following code should get you started. Its just alot about how to navigate between the worksheets and put the data onto the cells you want. In this particualr case i just created a workbook, then added 9 more sheets to it then put the names of the month on it. then i just placed that same monthname string in the cells on that worksheet. you can see that the cells are represented by integer coordinates like "A1" is really (1,1) or "B5" is (2,5)

Hope this helps:

Private Sub BuildxlsFile()
    Dim xlsApp As Excel.Application
    Dim xlsWBook As Excel.Workbook
    Dim xlsWSheet As Excel.Worksheet
    Dim i, j, m As Integer
    ' Get or Create Excel Object
    On Error Resume Next
    Set xlsApp = New Excel.Application
    xlsApp.StandardFont = "Arial"
    xlsApp.StandardFontSize = 8

    If Err.Number <> 0 Then
        Set xlsApp = New Excel.Application
    End If
    ' Create Workbook
    xlsApp.DisplayAlerts = False
    Set xlsWBook = xlsApp.Workbooks.Add
    'workbooks come with 3 worksheets by default. lets just use them and
    'add 9 more sheets to it
    xlsWBook.Worksheets.Add , , 9
    'put the months on them and add the data
    For m = 1 To 12
        xlsWBook.Worksheets(m).Name = CStr(Format(CDate(m & "/1/1900"), "mmmm"))
        'select one and put data on it
        Set xlsWSheet = xlsWBook.Worksheets(m)
        'Write Header
        xlsWSheet.Range("A1", "L1").Merge
        xlsWSheet.Cells(1, 1).HorizontalAlignment = xlHAlignCenter
        xlsWSheet.Cells(1, 1) = "Data For the Month of " & CStr(Format(CDate(m & "/1/1900"), "mmmm"))
        xlsWSheet.Cells(1, 1).Font.Bold = True
        'Add ColumnHeaders
        For j = 0 To 11
            xlsWSheet.Cells(2, j + 1) = CStr(Format(CDate(m & "/1/1900"), "mmmm"))
            xlsWSheet.Cells(2, j + 1).Font.Bold = True
        Next j
        'Insert Data into Cells
        For i = 1 To 12
            For j = 0 To 11
                xlsWSheet.Cells(i + 2, j + 1) = CStr(Format(CDate(m & "/1/1900"), "mmmm"))
            Next j
        Next i
        'Autofit column headers
        For i = 1 To 12
        Next i
        'Move to first cell to unselect
    'Save Excel Workbook and quite excel
    Dim fName As String
    fName = "C:\myExcelFile.xls"
    xlsApp.Workbooks(1).SaveAs fName
    'Clean Up
    Set xlsApp = Nothing
    Set xlsWBook = Nothing
    Set xlsWSheet = Nothing
End Sub

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Suggested Courses
Course of the Month11 days, 16 hours left to enroll

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question