?
Solved

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

Posted on 2005-03-25
3
Medium Priority
?
178 Views
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
0
Comment
Question by:gregbems
[X]
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
3 Comments
 
LVL 8

Accepted Solution

by:
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
            Err.Clear
    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
        DoEvents
        xlsWBook.Worksheets(m).Name = CStr(Format(CDate(m & "/1/1900"), "mmmm"))
   
   
        'select one and put data on it
        DoEvents
        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
            DoEvents
            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
            DoEvents
            For j = 0 To 11
                DoEvents
                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
            DoEvents
            xlsWSheet.Columns(i).AutoFit
        Next i
       
        'Move to first cell to unselect
        xlsWSheet.Range("A1").Select
    Next
   
    'Save Excel Workbook and quite excel
    Dim fName As String
    fName = "C:\myExcelFile.xls"
    xlsApp.Workbooks(1).SaveAs fName
    xlsApp.Quit
   
    'Clean Up
    Set xlsApp = Nothing
    Set xlsWBook = Nothing
    Set xlsWSheet = Nothing
    Beep
End Sub
0

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