exporting to excel via code

[Edward Letendre via AccessMonster.com]

Hello, My name is Edward Letendre. I am new to access monster, but I have
posed in the microsoft access news groups before. I am trying to develope an
access application that will export data from access to excel so that I can
create the excel file in a specific format and then save and convert the file
to a text file. So here is my problem:

I have a set of tables in access 97 that contains various seismic data. In
my case, I have a series of tables that when combined make up a header file.

for all of the data in the header file, I have a second table that has many
seismic lines, associated with the single header file mentioned above. Thus
the relationship is one header file to many lines records.

My first goal here is to gather the data that makes up a header file and
send it to an excel file. My second goal is then to add all of the line
records to that header file and have it saved as a single excel file.

The structure of the basic header file is as follows:

line header with the following structure:

UniqueId
LineName
Prospect
ClientCode

and other columns about the header

The clientCode column can be linked to the clients table and give you the
name of the client

The UniqueId and station value from the line points can then be linked back
to the line_header table to create the one to many relationship. And in this
case there are usually many stations (or if you will shot points) associated
with a single header as described above.

The output from such a connection should look like the following (not the H
in the first part identifies this as the Header section):

H
Line : R05
Client : Seis Energry
Prospect : Garfield_01
Contractor : Field Services

[Line ][Point] [LAT] [LONG] [EAST] [NORTH] [ELE] *[COMMENT]
R05 1201 45331529 100753410
R05 1202 45332029 100773459
R05 1203 45769910 100734598

note: from the H to [Line] heading, there are always 20 rows and the line
data begins on row 21 and the informaition that makes up a particular line is
80 characters per column, even if data is missing.

As for programming, I was thinking of creating the following type of code

header_count as Interger
line_count as Integer

header_count = 1
line_count = 1

loop until no headers are found (not sure how to phrase this start of the
loop)
--the start of the loop will go until there are not more header files
available

select line, client_name, prospect, etc..
from line_header, client
where line_header.client_code = clients.client_code
and line_header.UniqueId = head_count

(note the data from above would have to be loaded into a variable and then
the data needs to be written to an excel file)

second loop here to run until there are no line files associated with the
header file:


do until no lines found

select LineName, station, Latitute, Longitude, north_local, east_local,
elevation
from Line_header, line_points
where line_header.uniqueid = line_points.uniqueid
and line_points.uniqueid = line_count

the data from above would then have to be appended to the same excel file
that was created in the loading the header part of the loop)

line_count = line_count + 1
loop

Then before starting the next loading of the header and after the line
points or stations are all loaded and saved into the excel file, the excel
file should be closed and a new excel file should be created again in the
loading of the header file section.

Thus if this works the way I hope it does, I would end of with a series of
excel files with one header record for each of the header records in the
line_header table (each line header table can contain anywhere from 100 to
3500 header records) and each line point table can have multiple line point
entries for a single line header record.

Something else to add to this point, is that I have seen some examples as to
where you can code in specific columns or row (a20, b20, etc.) and have data
placed in these columns. This many or many not help me out, but I think that
it maybe necessary as the data is loaded into each cell. Again, could
someone please get me started on this as I would like to have a basic file to
work with in the next few days, if possible.

Edward Letendre

 

[Guest]

Edward,

What you want to do is not really that difficult. Have you done any VBA
from Access into Excel before? If you have not, I can send you some examples
that will get you started.

Also, I could not determine whether the header data would appear on each
line with the line detail or as a single line with the line detail lines
below it. It would be useful to know because it will affect how we structure
this.

One useful hint based on your post is that you may not have to create
variables to hold the data to put into Excel, you can put it directly into
Excel.

It would be useful for you to open the Object Browser in the VB Editor,
select Excel as your Library and stroll around looking at the Excel Object
Model. It is different from Acess, and takes a little getting used to.

I will wait to hear back from you on the questions, and we can proceed from
there.

Hello, My name is Edward Letendre. I am new to access monster, but I have
posed in the microsoft access news groups before. I am trying to develope an
access application that will export data from access to excel so that I can
create the excel file in a specific format and then save and convert the file
to a text file. So here is my problem:

I have a set of tables in access 97 that contains various seismic data. In
my case, I have a series of tables that when combined make up a header file.

for all of the data in the header file, I have a second table that has many
seismic lines, associated with the single header file mentioned above. Thus
the relationship is one header file to many lines records.

My first goal here is to gather the data that makes up a header file and
send it to an excel file. My second goal is then to add all of the line
records to that header file and have it saved as a single excel file.

The structure of the basic header file is as follows:

line header with the following structure:

UniqueId
LineName
Prospect
ClientCode

and other columns about the header

The clientCode column can be linked to the clients table and give you the
name of the client

The UniqueId and station value from the line points can then be linked back
to the line_header table to create the one to many relationship. And in this
case there are usually many stations (or if you will shot points) associated
with a single header as described above.

The output from such a connection should look like the following (not the H
in the first part identifies this as the Header section):

H
Line : R05
Client : Seis Energry
Prospect : Garfield_01
Contractor : Field Services

[Line ][Point] [LAT] [LONG] [EAST] [NORTH] [ELE] *[COMMENT]
R05 1201 45331529 100753410
R05 1202 45332029 100773459
R05 1203 45769910 100734598

note: from the H to [Line] heading, there are always 20 rows and the line
data begins on row 21 and the informaition that makes up a particular line is
80 characters per column, even if data is missing.

As for programming, I was thinking of creating the following type of code

header_count as Interger
line_count as Integer

header_count = 1
line_count = 1

loop until no headers are found (not sure how to phrase this start of the
loop)
--the start of the loop will go until there are not more header files
available

select line, client_name, prospect, etc..
from line_header, client
where line_header.client_code = clients.client_code
and line_header.UniqueId = head_count

(note the data from above would have to be loaded into a variable and then
the data needs to be written to an excel file)

second loop here to run until there are no line files associated with the
header file:


do until no lines found

select LineName, station, Latitute, Longitude, north_local, east_local,
elevation
from Line_header, line_points
where line_header.uniqueid = line_points.uniqueid
and line_points.uniqueid = line_count

the data from above would then have to be appended to the same excel file
that was created in the loading the header part of the loop)

line_count = line_count + 1
loop

Then before starting the next loading of the header and after the line
points or stations are all loaded and saved into the excel file, the excel
file should be closed and a new excel file should be created again in the
loading of the header file section.

Thus if this works the way I hope it does, I would end of with a series of
excel files with one header record for each of the header records in the
line_header table (each line header table can contain anywhere from 100 to
3500 header records) and each line point table can have multiple line point
entries for a single line header record.

Something else to add to this point, is that I have seen some examples as to
where you can code in specific columns or row (a20, b20, etc.) and have data
placed in these columns. This many or many not help me out, but I think that
it maybe necessary as the data is loaded into each cell. Again, could
someone please get me started on this as I would like to have a basic file to
work with in the next few days, if possible.

Edward Letendre

 

[Edward Letendre via AccessMonster.com]

What you want to do is not really that difficult. Have you done any >VBA from Access into Excel before?

Acutally I have done very little coding of any kind myself within an access
application, but I have done some access conversion from Access 97 to access
2000 (I have not learned much, but got a limited understanding of DAO code).

Also, I could not determine whether the header data would appear on >each line with the line detail or as a single line with the line detail >lines below it. It would be useful to know because it will affect how >we structure this.

From what I believe in a seg p1 file, the header would appear once at the
beginning and all of the line information would appear below so one header to
many lines, and in this case a line is defined by the header name (in my
previous example R01 and a point (1101). The line name is the same for all
lines (R01) but the point or shot point is the differnt each time (1101, 1102,
1103, etc.). So, as I had stated before the header would be first:

H
Line : R01 (note: physical name assoicated with the line)
Client : Canadian Resources (note: client seg p1 was created for
Prospect : Red Line (note: name of the project give to this file by the
client)

more data here up to 20 lines of data

then the line data begins,
[line] [point] [lat] [long] etc. note this is the column heading for the line
and this is only displayed once at the beginning of the line values below
(note: the line values for this header begin on line 21 of the seg p1 file)

R01 1101 573345N 100235146W
R01 1102 433345N 101345135W

and as stated before, there are several lines to one header in the file.
When all the lines for this particular header are listed in the header to
line ratio, the excel file would be saved and a new header and that set of
lines would be created. Thus the variable UniqueId for the line header would
be 1, and within the linepoints table, the header woudl be 1 for line 1, 2,
for line 2, 3 for line 3, etc.

thus you would see the following values in the lineheader to linepoints
tables:

header table
uniqueID line_name client_code, prospect
1, ro1 client_1 Red_line

uniqueId_link, line_unique_id,line name shot point lat long, etc.
1 1 R01 1101, etc
1 2 R01 1102
1 3 R01 1103

and then with the second header and line it would be new values

header table

uniqueid, line name, client_code, prospect
2 R02 client_1 blue_line

uniqueid_link, line_unique_id, line name shot point lat long, etc.
2 1 R02 1001
2 2 R02 1002
2 3 R02 1003
etc.

The table line_header is directly linked to line points (header being the
first table and line points being the second table as shown above)
etc.

and as stated before the excel table would be as follows with the inital
values for the first seg p1 file:

H
Line : R01
Client : Canadian Resources
Prospect : Red Line
Contractor : Field Services

[Line ][Point] [LAT] [LONG] [EAST] [NORTH] [ELE] *[COMMENT]
R05 1101 45331529 100753410
R05 1102 45332029 100773459
R05 1103 45769910 100734598

the line data coming from the line table and the H or header information
coming from the header table, client table (converted from client code in
header table to client name value found in client table).

I hope that this gives you some more info.

Edward Letendre

 

[Guest]

Edward,

Without some pretty good VBA skills, this is going to be pretty difficult.
I have pulled some sample code from some Excel work I have done that might
help with some ideas.

Here are some Dim statements you will need to be able to create Excel Object
references:

Dim xlapp As Excel.Application 'Application Object
Dim xlbook As Excel.Workbook 'Workbook Object
Dim xlsheet As Excel.Worksheet 'Worksheet Object

Here is how you create a workbood:

Set xlapp = New Excel.Application
Set xlbook = xlapp.Workbooks.Add
xlbook.Activate
Set xlsheet = xlbook.ActiveSheet

Here is an example of loading values into cells:

xlsheet.Name = Me.cboResource & " Hours " & strMonth & " YTD"
With xlsheet
.Cells(1, 1) = "ITM"
.Cells(1, 2) = Me.txtCurrYear & _
" Activity # Description"
.Cells(1, 3) = "Budget " & Me.txtCurrYear
.Cells(1, 4).Value = Me.txtCurrYear & " YTD Budget"
.Cells(1, 5) = "Actuals YTD"
.Cells(1, 6) = "Variance YTD"
.Cells(1, 7) = "TO GO"
.Cells(1, 8) = IIf(Me.cboPeriod >= 1, "JAN ACT", "JAN ETC")
.Cells(1, 9) = IIf(Me.cboPeriod >= 2, "FEB ACT", "FEB ETC")
.Cells(1, 10) = IIf(Me.cboPeriod >= 3, "MAR ACT", "MAR ETC")
.Cells(1, 11) = IIf(Me.cboPeriod >= 4, "APR ACT", "APR ETC")
.Cells(1, 12) = IIf(Me.cboPeriod >= 5, "MAY ACT", "MAY ETC")
.Cells(1, 13) = IIf(Me.cboPeriod >= 6, "JUN ACT", "JUN ETC")
.Cells(1, 14) = IIf(Me.cboPeriod >= 7, "JUL ACT", "JUL ETC")
.Cells(1, 15) = IIf(Me.cboPeriod >= 8, "AUG ACT", "AUG ETC")
.Cells(1, 16) = IIf(Me.cboPeriod >= 9, "SEP ACT", "SEP ETC")
.Cells(1, 17) = IIf(Me.cboPeriod >= 10, "OCT ACT", "OCT ETC")
.Cells(1, 18) = IIf(Me.cboPeriod >= 11, "NOV ACT", "NOV ETC")
.Cells(1, 19) = IIf(Me.cboPeriod >= 12, "DEC ACT", "DEC ETC")
End With

Here are some formatting examples:

With xlsheet
For Each Cell In xlsheet.Range("A1", "S1")
Cell.Font.Size = 10
Cell.Font.Name = "Arial"
Cell.Font.Bold = True
Cell.Interior.Color = conLightGray
Cell.HorizontalAlignment = xlHAlignCenter
Cell.WrapText = True
Next
.Cells(1, 2).HorizontalAlignment = xlHAlignLeft
.Columns("A").ColumnWidth = 9
.Columns("B").ColumnWidth = 39
.Columns("C:S").ColumnWidth = 9
.Rows(1).RowHeight = 25.5
End With

Here I load data from a query into the sheet at a specific location. This
would be useful for you where you want to put your line data in:

Set qdf = CurrentDb.QueryDefs("qselSCCBrpt")
qdf.Parameters(0) = Me.cboResource
qdf.Parameters(1) = Me.cboPeriod
Set rstSCCB = qdf.OpenRecordset(dbOpenSnapshot, dbReadOnly)
xlsheet.Cells(intX + 3, 1).CopyFromRecordset rstSCCB
lngDetailCount = rstSCCB.RecordCount
rstSCCB.Close
Set rstSCCB = Nothing
Set qdf = Nothing

Here is an example of creating new sheets in the workbook:

'Create a new worksheet
xlapp.Worksheets.Add.Move after:=xlapp.Worksheets(xlapp.Worksheets.Count)
xlbook.Worksheets(xlbook.Worksheets.Count).Activate
Set xlsheet = xlbook.ActiveSheet
xlsheet.Name = Switch(Me.cboResource = "SEL", "systems", Me.cboResource
= "PSOL", _
"prog mgmt", Me.cboResource = "SUP", "support") _
& IIf(strItmPM = "PM", " pm", "") _
& IIf(blnRecurring, " Rec_", " Nrec_") & rstItms![itm]


Here is an example of saving the workbook when it is done:

'Get the File Name To Save
varGetFileName = ahtCommonFileOpenSave(ahofn_overwriteprompt, _
"\\rsltx1-bm01\busmgmt\", "Excel Spreadsheets (*.xls) *.xls", , _
"xls", varGetFileName, "Save Report", , False)

If varGetFileName = "" Then 'User Clicked CANCEL
blnSaveFile = True
Exit Do
End If
'Parse the name to do a file search
varFileName = Right(varGetFileName, Len(varGetFileName) - _
InStrRev(varGetFileName, "\"))
varFolderName = Left(varGetFileName, InStrRev(varGetFileName, "\") -
1)

'See if the File already exisits
With xlapp.FileSearch
.NewSearch
.LookIn = varFolderName
.SearchSubFolders = False
.FileName = varFileName
If .Execute > 0 Then 'The file already exists
'Ask if the user wants to overwrite the old file
If MsgBox("The file " & varFileName & " already exists. " & _
"Do you want to replace the existing file?",
vbExclamation + vbYesNo, _
"Microsoft Excel") = vbYes Then
xlbook.SaveAs FileName:=varGetFileName
blnSaveFile = True
blnOkToShow = True
End If
Else
'The file is new, save it
xlbook.SaveAs FileName:=varGetFileName
blnSaveFile = True
blnOkToShow = True
End If
End With
Loop
'

'Time to Go
Build_XL_Report_Exit:
Me.txtStatus.Visible = False
Me.Repaint

xlbook.Close
xlapp.Quit
Set xlsheet = Nothing
Set xlbook = Nothing
Set xlapp = Nothing
DoCmd.Hourglass (False)

'Show the Spreadsheet if we saved it
If blnOkToShow Then
Call Shell("excel " & Chr$(34) & varGetFileName & Chr$(34),
vbMaximizedFocus)
End If

I hope this will help. Good Luck

What you want to do is not really that difficult. Have you done any >VBA from Access into Excel before?

Acutally I have done very little coding of any kind myself within an access
application, but I have done some access conversion from Access 97 to access
2000 (I have not learned much, but got a limited understanding of DAO code).

Also, I could not determine whether the header data would appear on >each line with the line detail or as a single line with the line detail >lines below it. It would be useful to know because it will affect how >we structure this.

From what I believe in a seg p1 file, the header would appear once at the
beginning and all of the line information would appear below so one header to
many lines, and in this case a line is defined by the header name (in my
previous example R01 and a point (1101). The line name is the same for all
lines (R01) but the point or shot point is the differnt each time (1101, 1102,
1103, etc.). So, as I had stated before the header would be first:

H
Line : R01 (note: physical name assoicated with the line)
Client : Canadian Resources (note: client seg p1 was created for
Prospect : Red Line (note: name of the project give to this file by the
client)

more data here up to 20 lines of data

then the line data begins,
[line] [point] [lat] [long] etc. note this is the column heading for the line
and this is only displayed once at the beginning of the line values below
(note: the line values for this header begin on line 21 of the seg p1 file)

R01 1101 573345N 100235146W
R01 1102 433345N 101345135W

and as stated before, there are several lines to one header in the file.
When all the lines for this particular header are listed in the header to
line ratio, the excel file would be saved and a new header and that set of
lines would be created. Thus the variable UniqueId for the line header would
be 1, and within the linepoints table, the header woudl be 1 for line 1, 2,
for line 2, 3 for line 3, etc.

thus you would see the following values in the lineheader to linepoints
tables:

header table
uniqueID line_name client_code, prospect
1, ro1 client_1 Red_line

uniqueId_link, line_unique_id,line name shot point lat long, etc.
1 1 R01 1101, etc
1 2 R01 1102
1 3 R01 1103

and then with the second header and line it would be new values

header table

uniqueid, line name, client_code, prospect
2 R02 client_1 blue_line

uniqueid_link, line_unique_id, line name shot point lat long, etc.
2 1 R02 1001
2 2 R02 1002
2 3 R02 1003
etc.

The table line_header is directly linked to line points (header being the
first table and line points being the second table as shown above)
etc.

and as stated before the excel table would be as follows with the inital
values for the first seg p1 file:

H
Line : R01
Client : Canadian Resources
Prospect : Red Line
Contractor : Field Services

[Line ][Point] [LAT] [LONG] [EAST] [NORTH] [ELE] *[COMMENT]
R05 1101 45331529 100753410
R05 1102 45332029 100773459
R05 1103 45769910 100734598

the line data coming from the line table and the H or header information
coming from the header table, client table (converted from client code in
header table to client name value found in client table).

I hope that this gives you some more info.

Edward Letendre

 

[Edward Letendre via AccessMonster.com]

Okay, I have begun to work on this project. I am trying to develop an
understanding of access basic code. Here is what I have created so far:


Dim xlapp As Excel.Application
Dim xlbook As Excell.Workbook
Dim xlsheet As Excel.Worksheet
Dim hcount As Integer
Dim headquery As QueryDef

Set db = DBEngine.Workspaces(0).Databases(0)

Set headquery = db.CreateQueryDef()
headquery.Name = "header_3"

I realize that the code is thin but this is just the start. From what I have
seen in the past, I believe that the line set headerquery = db.createquerydef
() will open my query (called header_3) and can then be accessed by the
headquery.Name command or I could be wrong.

From this point, I was hoping that I could take the columns wthin my query to
be able to add them to a few cells of the spreadsheet, but I am at a loss as
to where to start from there. I have the basic variables down from your
examples and I am just trying to work it out.

Any extra advice would be of help at this point.

Edward Letendre