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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

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

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses

752 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