From Access to Excel 2

[JrClown]

Hello all. Here's my question.

I've been learning Active Server Pages (ASP) for the past 4 weeks and have learn it enough to support our intranet, but I've come to a obstacle and here it is.

How do I make an Access database report to an Excel workbook and make the workbook update it's contents when my datatbase is update?
Furthermore, I only want certain fields from my database to show on my workbook.

Am I crazy for asking this or is it possible?? Please advise. Thank you very much.
QUOTE OF THE DAY
Don't waste time telling people what you are doing or what you are going to do. Results have a way of informing the world.

<%
Jr Clown )
%>

 

[jimmythegeek]

You can use VBA to export data from Access to Excel. You can actually open a particular spreadsheet and insert data into particular sheets and cells if you want. It is pretty sophisticated code, and I would be happy to post it here if you would like. Let me know. Jim Lunde
compugeeks@hotmail.com
Custom Application Development

 

[JrClown]

Will this code Jimmy, update my workbook when I update data on my access table? is so. Please post it here so I can view it. Thank you very much bud. QUOTE OF THE DAY
Don't waste time telling people what you are doing or what you are going to do. Results have a way of informing the world.

<%
Jr Clown )
%>

 

[jimmythegeek]

No it won't update data in the spreadsheet automatically. You can however, create a spreadsheet that will in essence &quot;link&quot; to an Access table or query. Then when you open the spreadsheet, it grabs the data from Access. Do a search for &quot;Get External Data&quot; or &quot;Using Access&quot; in Excel. Jim Lunde
compugeeks@hotmail.com
Custom Application Development

 

[mutidjo]

Hi Jimmy,
I would appreciate if you could post the code to export data from access to excel.

Many thanks!

 

[jimmythegeek]

This is rather long, but is used as an example only, I did not trim it down, I simply pasted it here. Use whatever part of it you want, however you want. Copy and paste it into a module in Access, and it will be much easier to read.

====================
' Constants
Private Const XLS_LOCATION As String = &quot;C:\My Documents\Spreadsheets\Vault.xlt&quot;
Private Const XLT_LOCATION As String = &quot;C:\Windows\Vault.xlt&quot;
Private Const MC_START_ROW As Integer = 299
Private Const MC_END_ROW As Integer = 100
Private Const VISA_START_ROW As Integer = 999
Private Const VISA_END_ROW As Integer = 800

Public Sub populateExcel()
On Error GoTo Populate_Err
Dim rs As Recordset
Dim objXL As Object, objSheet As Object, objRange As Object
Dim strSaveAs As String, strVISA As String, strMC As String
Dim x As Integer, intRow As Integer

DoCmd.Hourglass True

' Set the SQL strings for the two recordsets that will be opened
strVISA = &quot;SELECT CardStyle, StartInv FROM WorkingInvStart WHERE PlasticType = 'VISA'&quot;
strMC = &quot;SELECT CardStyle, StartInv FROM WorkingInvStart WHERE PlasticType = 'MC'&quot;

' Open, and make visible the Excel Template (Vault.xlt) which resides on the desktop
Set objXL = GetObject(XLT_LOCATION)
objXL.Application.Visible = True
objXL.Parent.windows(1).Visible = True

' Open the VISA recordset, and activate the VISA sheet in the template
Set rs = CurrentDb.OpenRecordset(strVISA, dbOpenSnapshot)
Set objSheet = objXL.Worksheets(&quot;Visa&quot
objSheet.Activate
rs.MoveFirst
x = 4

' Insert the data from the VISA recordset into the VISA worksheet
Do Until rs.EOF
objXL.ActiveSheet.Cells(x, 1).Value = rs!CardStyle
objXL.ActiveSheet.Cells(x, 2).Value = rs!StartInv
x = x + 1
rs.MoveNext
Loop

' Delete all unnecessary rows making the VISA worksheet only as long as it needs to be
intRow = VISA_START_ROW
With objSheet
.select
Do Until intRow = VISA_END_ROW
If .Range(&quot;A&quot; & intRow).Value = &quot;&quot; Then
Set objRange = .Range(&quot;A&quot; & intRow & &quot;:B&quot; & intRow & &quot;:C&quot; & intRow & &quot;&quot; & intRow & &quot;:E&quot; & intRow _
& &quot;:F&quot; & intRow & &quot;:G&quot; & intRow & &quot;:H&quot; & intRow & &quot;:I&quot; & intRow & &quot;:J&quot; & intRow _
& &quot;:K&quot; & intRow & &quot;:L&quot; & intRow & &quot;:M&quot; & intRow & &quot;:N&quot; & intRow & &quot;:O&quot; & intRow & &quot;&quot; & intRow)
objRange.Delete 'Shift:=objXLUp
End If
intRow = intRow - 1
Loop
End With
rs.Close

' Open the MC recordset, and activate the MC sheet in the template
Set rs = CurrentDb.OpenRecordset(strMC, dbOpenSnapshot)
Set objSheet = objXL.Worksheets(&quot;MC&quot
objSheet.Activate
rs.MoveFirst
x = 4

' Insert the data from the MC recordset into the MC worksheet
Do Until rs.EOF
objXL.ActiveSheet.Cells(x, 1).Value = rs!CardStyle
objXL.ActiveSheet.Cells(x, 2).Value = rs!StartInv
x = x + 1
rs.MoveNext
Loop

' Delete all unnecessary rows making the MC worksheet only as long as it needs to be
intRow = MC_START_ROW
With objSheet
.select
Do Until intRow = MC_END_ROW
If .Range(&quot;A&quot; & intRow).Value = &quot;&quot; Then
Set objRange = .Range(&quot;A&quot; & intRow & &quot;:B&quot; & intRow & &quot;:C&quot; & intRow & &quot;&quot; & intRow & &quot;:E&quot; & intRow _
& &quot;:F&quot; & intRow & &quot;:G&quot; & intRow & &quot;:H&quot; & intRow & &quot;:I&quot; & intRow & &quot;:J&quot; & intRow _
& &quot;:K&quot; & intRow & &quot;:L&quot; & intRow & &quot;:M&quot; & intRow & &quot;:N&quot; & intRow & &quot;:O&quot; & intRow & &quot;&quot; & intRow)
objRange.Delete
End If
intRow = intRow - 1
Loop
End With

' Calculate totals on spreadsheet
objXL.Application.calculate

' Set the save string, and save the spreadsheet
strSaveAs = &quot;C:\Windows\Desktop\&quot; & Format(DATE, &quot;mmddyyyy&quot & &quot;.xls&quot;
objXL.SaveCopyAs strSaveAs

' Quit Excel
objXL.Application.DisplayAlerts = False
objXL.Application.Quit

Set objXL = Nothing
Set objSheet = Nothing
Set objRange = Nothing
Set rs = Nothing

Populate_Exit:
DoCmd.Hourglass False
Exit Sub

Populate_Err:
MsgBox Err.Number & &quot;: &quot; & Err.Description
GoTo Populate_Exit
End Sub
==================== Jim Lunde
compugeeks@hotmail.com
We all agree your theory is crazy, but is it crazy enough?

 

[mutidjo]

Thank you! I'll give it a try...

 

[Guest_imported]

hi,

i have a column of text data and a corresponding column of numbers in a worksheet. i want to be able to enter part of the text data in a different worksheet with respective numbers and have excel search for the corresponding cells in the former worksheet and update the numbers alone in the matching cells. is there any way i can do this??? i wud be much obliged to you if anyone can help me out with this problem.