access... excel

[antonov]

Hello again everybody.
I have this program I am working on and I have a question:
I need to export data from access to excel (a specific sheet in a specific
workbook). The thing is that this excel sheet need to contain all data from
januari 1st til december 31st. This means that I need the data to go to the
next available free row. The other thing is that the data has to go into
specific cells otherwise the calculations (in excel) won't work. Last
problem (most important): I am not very good with access......
thanks again for any help (you have been of great help so far... thanks)

 

[Danny J. Lesandrini]

This should get you started with automation ...
Set a reference to Excel and modify the code with your SQL and FILE names.
The intLastRow piece is where you find the last used row.

Cheers


Function ExportToExcel()

' Excel object variables
Dim appExcel As Excel.Application
Dim strFile As String
Dim wbk As Excel.Workbook
Dim wks As Excel.Worksheet
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strData As String
Dim intLastRow As Integer
Dim strSQL As String
Dim strMsg As String
Dim intCol As Integer
Dim intRow as Integer

On Error GoTo Err_Handler

' Create the Excel Applicaiton, Workbook and Worksheet objects
Set appExcel = Excel.Application

strFile "C:\YourExcelFileHere.xls"
Set wbk = appExcel.Workbooks.Open(strFile)
'wbk.Unprotect ("YourPasswordHere")

Set wks = appExcel.Worksheets(cbytThirdParty)
'wks.Unprotect ("YourPasswordHere")

' Find the last row
strData = wks.UsedRange.Address
intLastRow = CInt(Mid(strData, InStrRev(strData, "$")))
intRow = intLastRow + 1

Set dbs = CurrentDb
strSQL = "SELECT FName, LName, SSN, DOB FROM Employee"
Set rst = dbs.OpenRecordset(strSQL, dbOpenSnapshot)

Do Until rst.EOF
For intCol = 1 To rst.Fields.Count
wks.Cells(intRow, intICol) = rst.Fields(intCol - 1)
wks.Cells(intRow, intICol).WrapText = False

' set formatting
Select Case rst.Fields(intCol - 1).Type
Case dbDate
' set format for date fields
wks.Cells(intRow, intCol).NumberFormat = "mm/dd/yyyy"
Case dbText
' set cell background to red for text fields
wks.Cells(intRow, intCol).Interior.Color = vbRed
End Select
Next

intRow = intRow + 1
rst.MoveNext
Loop

Exit_Here:
On Error Resume Next
'wkb.Protect ("YourPasswordHere")
'wks.Protect ("YourPasswordHere")
Set wks = Nothing
Set wbk = Nothing
Set appExcel = Nothing
Set rst = Nothing
Set dbs = Nothing
Exit Function

Err_Handler:
MsgBox Err.Description, vbCritical, "Error"
Resume Exit_Here

End Function

 

[aaron.kempf]

excel is a disease; dont feed your data to the dogs

 

[Tom Wickerath]

Hey Aaron,

Since you despise Excel so much, several of us (Harlan, myself, Gunny) are wondering when or if
you are ever going to answer Harlan's challenge to you:

http://groups.google.com/group/microsoft.public.excel/msg/2c1e73ffd974b4d2?dmode=source&hl=en


Tom
___________________________________________


Our buddy Aaron wrote in message
excel is a disease; dont feed your data to the dogs

 

[antonov]

in this all I didn't understand much... next question is: is it possible to
transfer the data from access to excel even if the cells are not adjacent to
each other?

 

[Douglas J. Steele]

Not through methods such as TransferSpreadsheet, but you can using
Automation.

 

[antonov]

Ehmm... Doug... do you mind being more specific?

Not through methods such as TransferSpreadsheet, but you can using
Automation.

 

[Douglas J. Steele]

Actually, Automation _is_ specific! <g>

For a simple example, see http://support.microsoft.com/?id=210288

Do a search at
http://search.microsoft.com/search/search.aspx?view=msdn&st=a&c=0&s=1 on all
of the words automation excel access, or do a search at Google on
+automation +excel +access for more details

 

[aaron.kempf]

tom tom tom

go and play with your spreadsheets; kid

and i noticed your picture is in northwind or something the other day
lol

 

['69 Camaro]

Please cross my name off of the list of those wondering whether Aaron can
answer the challenge. While I've seen evidence that Aaron thinks he can
fool people by posting someone else's incomplete code snippet written in a
programming language Aaron doesn't understand as proof that he could have
written it himself in VB (the only programming language he thinks he'll ever
need), I've never seen any evidence that Aaron has the programming skills
and critical thinking skills necessary to solve Harlan's challenges.

Gunny

 

[aaron.kempf]

gunny

you're funny you know that?

im absolutely confident that there ins't a math problem in the world
that excel can do that database can't.

i didnt say i knew all of the math in the world. to be honest; i took
5 semesters of math in college-- 2 review and 3 calc-- and it wasn't
half as much math as what i learned in high school

so if you want to quiz me about shit that i learned while i was still
screwing your mother-- you can-- just dont expect tangible answers

anything that you try to do in excel; i can do easier; faster in the
database world.

'ohhh-- but it's soooooo hard to setup tables <gag>'

go play with your spreadsheets little boy

 

[aaron.kempf]

you of course you can import cells that aren't next to each other

a) import the whole xls into a database
b) grab the columns; rows you want
c) push them where you want them with an append query
d) uninstall excel from every machine at your company
e) fire the beancounters who can't do a single goddamn thing without
copying and pasting