-----Original Message-----
Here's something to get you started. It's Excel VBA code that (assuming
I've interpreted your sample correctly) exports it into a non-normalised
textfile that you can then link or import to Access and use as thebasis
of queries to get the data into your normalised structure.
If you want a more fully automated solution, you can use Automation to
use code like this running in Access to get the data out of the
worksheet and more directly into the Access tables.
Sub MungeToTextFile()
Const F_NAME = "C:\Temp\Jay\Patti.txt"
Const DELIM = vbTab
Dim lngFN As Long
Dim dtDate As Date
Dim wksW As Worksheet
Dim raR As Range
Dim raC As Range
Dim S As String
Dim strLine
Dim lngCol As Long
Dim lngHour As Long
'Open output file and write header with fieldnames
lngFN = FreeFile()
Open F_NAME For Output As #lngFN
S = "UsageDate" & DELIM & "Company" & DELIM _
& "ScheduleType" & DELIM & "UsageHour" & DELIM _
& "Usage"
Print #lngFN, S
Set wksW = ActiveWorkbook.ActiveSheet
'Get the date
S = wksW.Cells(1, 1).Value
dtDate = DateSerial(Right(S, 2), Left(S, 2), Mid(S, 3, 2))
lngCol = 2
With wksW
'Main loop, once per company
Do Until .Cells(2, lngCol).Value = ""
'Start building output string
S = "#" & Format(dtDate, "mm/dd/yyyy") & "#" & DELIM 'Date
S = S & .Cells(2, lngCol).Value & DELIM 'Company
S = S & .Cells(3, lngCol).Value & DELIM 'Sched type
'inner loop, once per hour, only if there's usage in that hour
For lngHour = 1 To 24
If IsNumeric(.Cells(lngHour + 3, lngCol).Value) _
And Len(CStr(.Cells(lngHour + 3, lngCol).Value)) > 0 Then
Print #lngFN, S & lngHour & DELIM & _
.Cells(lngHour + 3, lngCol).Value
End If
Next
lngCol = lngCol + 1
Loop
End With
Close #lngFN
End Sub
Thanks, John. I was afraid that was going to be the
answer. This spreadsheet was design several years ago
(in fact, its a DOS=based SMARTWARE program!!!) that has
just been recently upgraded to a windows version. We're
in the process of re-developing the entire system to a
database-type format, but in the interim are hoping to at
least get the data extracted on a daily basis into ACCESS
for after-the-fact analysis, billing, etc.
Since its not even an Excel spreadsheet, looks like I
have my work cut out for me by resorting to the Access
code. I'm sure you will be hearing back from me since my
VB skills are not very advanced. THANKS!
Patti
-----Original Message-----
Hi Patti,
It's not possible to import stuff like this using the standard import
routines. If this is a routine task, far the best
thing
is to persuade
whoever or whatever creates the Excel sheet -
presumably
it's a database
system on another computer - to supply the data in a more
database-friendly format, e.g. in a single non- normalised table like
this:
Date Company Schedype Hour Usage
092904 Company1 AAA 1 10
092904 Company1 AAA 2 20
....
The alternative is much more work: either write Excel VBA code to
re-arrange the data yourself, or else write code that runs in Access
and uses Automation to extract the data piece by piece from the present
layout and create the appropriate records in your tables.
On Wed, 29 Sep 2004 14:09:54 -0700, "Patti"
I have an existing spreadsheet with several columns used
for daily energy sales. Each day is saved as a separate
file so the date is listed once at the top of the sheet.
Row 1 contains the company names
Row 2 contains a schedule type
Row 3-27 is the megawatts sold for each hour of the day .
the first column of the spreadsheet lists each of the
hours 1-24.
Example:
092904 (the date)
Company1 Company2 Company3
.
AAA BBB AAA
Hour1 10 10
Hour2 20 5
Hour3 10 10 20
.
.
.
Hour24 10
Total 40 25 30
Note that not all hours necessarily contain MW.
Is it possible to export this data into an ACCESS
database from this format? I currently have a database
that I'm in the process of developing. I have the
following tables:
TblCompany
=============
CompanyID (PK)
CompanyName
TblScheduleType
=================
SchedTypeID (PK)
SchedTypeName
TblMasterTransaction
=====================
MasterID (PK)
Date
Comments
CompanyID (FK)
SchedTypeID (FK)
TblTransactions
================
TransID (PK)
Hour
MW
Any help would be much appreciated!!!
Tia,
Patti
--
John Nurick [Microsoft Access MVP]
Please respond in the newgroup and not by email.
.
--
John Nurick [Microsoft Access MVP]
Please respond in the newgroup and not by email.
.
