Can I export table data to Excel In a set Format? (1 Viewer)

[mission2java_78]

hmm.

There is a second argument of the recordset method. Try looking up the Open recordset method in access help. Ill try to do it later this afternoon.

Jon

 

[chewy]

thanks, Ill take a look

 

[mission2java_78]

OpenRecordset Method


Creates a new Recordset object and appends it to the Recordsets collection.

Syntax

For Connection and Database objects:

Set recordset = object.OpenRecordset (source, type, options, lockedits)

For QueryDef, Recordset, and TableDef objects:

Set recordset = object.OpenRecordset (type, options, lockedits)

The OpenRecordset method syntax has these parts.

Part Description
recordset An object variable that represents the Recordset object you want to open.
object An object variable that represents an existing object from which you want to create the new Recordset.
source A String specifying the source of the records for the new Recordset. The source can be a table name, a query name, or an SQL statement that returns records. For table-type Recordset objects in Microsoft Jet databases, the source can only be a table name.
type Optional. A constant that indicates the type of Recordset to open, as specified in Settings.
options Optional. A combination of constants that specify characteristics of the new Recordset, as listed in Settings.
lockedits Optional. A constant that determines the locking for the Recordset, as specified in Settings.


Settings

You can use one of the following constants for the type argument.

Constant Description
dbOpenTable Opens a table-type Recordset object (Microsoft Jet workspaces only).
dbOpenDynamic Opens a dynamic-type Recordset object, which is similar to an ODBC dynamic cursor. (ODBCDirect workspaces only)
dbOpenDynaset Opens a dynaset-type Recordset object, which is similar to an ODBC keyset cursor.
dbOpenSnapshot Opens a snapshot-type Recordset object, which is similar to an ODBC static cursor.
dbOpenForwardOnly Opens a forward-only-type Recordset object.

There you go..try dbOpenDynaset, or dbOpenDynamic, or dbOpenSnapshot as the second argument.

Jon

 

[chewy]

I tried them before. Same problem.

 

[mission2java_78]

MAKE A BACKUP OF THE EXCEL FILE BEFORE YOU DO THIS!

Do me a favor on all the code replace:

("H" & lngStartRange) with the letter plus 1

So ("H1"), ("A1")

etc...I dont think excel is liking the append.

Then compile the code and get back to me.

Jon

 

[mission2java_78]

I think you will need to use the cells method rather than range.

objXL.Cells(Zeilennummer, "A").Value = rst("TCPos").Value
objXL.Cells(Zeilennummer, "G").Value = rst("N").Value
objXL.Cells(Zeilennummer, "H").Value = rst
("Quantity").Value

so in your case you will use

Ok...lets get down and dirty...wait that didnt sound good.

Here goes
On Error Goto Err_Handler

Dim db as DAO.Database
Dim rs as DAO.Recordset
Dim strSQL As String
Dim objXL As Excel.Application
Dim objXLWrkBk As Excel.Workbook
Dim objXLWrkSht As Excel.Worksheet
Dim lngStartRange as Long

strSQL = "SELECT * FROM YourTableNameGoesHERE"

Set db=currentdb
set rs=db.openrecordset(strSQL)

if rs.bof then
'no records
'so what to do...prolly nothing?
'or you could put a messagebox here
else
lngStartRange = 5 'start at row 5
Set objXL = CreateObject("Excel.Application")
Set objXLWrkBk = objXL.Workbooks.Open("PATH TO THAT EXCEL FILE")
Set objXLWrkSht = objXLWrkBk.Worksheets("Sheet1") 'takes the name of the tab in Excel in your case sheet1
objXLWrkSht.Range("A1").Activate
while not rs.eof

objXL.Cells(lngStartRange, "A").Value = rst("TCPos").Value
objXL.Cells(lngStartRange, "G").Value = rst("N").Value
objXL.Cells(lngStartRange, "H").Value = rst
("Quantity").Value
lngStartRange=lngStartRange + 1 'increment the row for next customer
rs.MoveNext 'move to next customer
wend
objXLWrkBk.Close SaveChanges:=True
objXL.Quit
end if

Done:
Set rs = Nothing
Set db = Nothing
Set objXL = Nothing
Set objXLWrkBk = Nothing
Set objXLWrkSht = Nothing
Exit Sub

Just use your columns A/B/C/D/etc, and your fields instead of mine.

This was a mistake on my end.

Jon

 

[chewy]

The thing is it's not even getting that far down in teh code. It is still looking for the 2 parameters

 

[mission2java_78]

Look at my previous post...

It works on my end with just one argument.

Its not that that is causing the problem.

Jon

 

[chewy]

ok ill try

 

[chewy]

two parameter error

 

[chewy]

here is what I changed

lngStartRange = 5 'start at row 5
Set objXL = CreateObject("Excel.Application")
Set objXLWrkBk = objXL.Workbooks.Open("M:\Book Keeping\Matt Majewski Procedures\Ferris Lockbox\Ferris Ind.xls")
Set objXLWrkSht = objXLWrkBk.Worksheets("Sheet1") 'takes the name of the tab in Excel in your case sheet1
objXLWrkSht.Range("A1").Activate
While Not rs.EOF

objXL.Cells(lngStartRange, "A").Value = rs("CustomerID").Value
objXL.Cells(lngStartRange, "B").Value = rs("CustomerNumber").Value
objXL.Cells(lngStartRange, "C").Value = rs("CheckNumber").Value
objXL.Cells(lngStartRange, "D").Value = rs("CheckAmount").Value
objXL.Cells(lngStartRange, "E").Value = rs("InvoiceNumber").Value

 

[mission2java_78]

You have got to post all your code.

Jon

 

[mission2java_78]

Send all code

 

[chewy]

Private Sub Command17_Click()
'On Error GoTo Err_Handler

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim objXL As Excel.Application
Dim objXLWrkBk As Excel.Workbook
Dim objXLWrkSht As Excel.Worksheet
Dim lngStartRange As Long

strSQL = "SELECT * FROM qryMain"

Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL)

If rs.BOF Then
'no records
'so what to do...prolly nothing?
'or you could put a messagebox here
Else
lngStartRange = 5 'start at row 5
Set objXL = CreateObject("Excel.Application")
Set objXLWrkBk = objXL.Workbooks.Open("M:\Book Keeping\Matt Majewski Procedures\Ferris Lockbox\Ferris Ind.xls")
Set objXLWrkSht = objXLWrkBk.Worksheets("Sheet1") 'takes the name of the tab in Excel in your case sheet1
objXLWrkSht.Range("A1").Activate
While Not rs.EOF

objXL.Cells(lngStartRange, "A").Value = rs("CustomerID").Value
objXL.Cells(lngStartRange, "B").Value = rs("CustomerNumber").Value
objXL.Cells(lngStartRange, "C").Value = rs("CheckNumber").Value
objXL.Cells(lngStartRange, "D").Value = rs("CheckAmount").Value
objXL.Cells(lngStartRange, "E").Value = rs("InvoiceNumber").Value

lngStartRange = lngStartRange + 1 'increment the row for next customer
rs.MoveNext 'move to next customer
Wend
objXLWrkBk.Close SaveChanges:=True
objXL.Quit
End If

Done:
Set rs = Nothing
Set db = Nothing
Set objXL = Nothing
Set objXLWrkBk = Nothing
Set objXLWrkSht = Nothing

Exit Sub
End Sub

 

[mission2java_78]

looks good.
send db

jon

 

[chewy]

Thanks for the help!

 

[mission2java_78]

How did you expect this to work? Your query requires PARAMETERS...
Look into the design view of your query...how is access going to open this query if you do not provide the parameters to it?


Jon

 

[mission2java_78]

Would you look at that..:

strSQL = "SELECT * FROM qryMain WHERE DATE BETWEEN #01/01/03# AND #05/01/03#"


Now it works because I provided 2 dates...now you have to do the same thing.

Jon

 

[mission2java_78]

Copy that query and get rid of the parameters in it...there should be no criteria.

Behind the button use this:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim objXL As Excel.Application
Dim objXLWrkBk As Excel.Workbook
Dim objXLWrkSht As Excel.Worksheet
Dim lngStartRange As Long
Dim startDate As Date
Dim endDate As Date

startDate = 0
endDate = 0

While startDate = 0
startDate = InputBox("Enter start date:")
Wend

While endDate = 0 Or endDate < startDate
endDate = InputBox("Enter end date:")
Wend

strSQL = "SELECT * FROM qryMain WHERE Date BETWEEN #" & startDate & "# AND #" & endDate & "#"

Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL)

If rs.BOF Then
'no records
'so what to do...prolly nothing?
'or you could put a messagebox here
Else
lngStartRange = 5 'start at row 5
Set objXL = CreateObject("Excel.Application")
Set objXLWrkBk = objXL.Workbooks.Open("M:\Book Keeping\Matt Majewski Procedures\Ferris Lockbox\Ferris Ind.xls")
Set objXLWrkSht = objXLWrkBk.Worksheets("Sheet1") 'takes the name of the tab in Excel in your case sheet1
objXLWrkSht.Range("A1").Activate
While Not rs.EOF

objXL.Cells(lngStartRange, "A").Value = rs("CustomerID").Value
objXL.Cells(lngStartRange, "B").Value = rs("CustomerNumber").Value
objXL.Cells(lngStartRange, "C").Value = rs("CheckNumber").Value
objXL.Cells(lngStartRange, "D").Value = rs("CheckAmount").Value
objXL.Cells(lngStartRange, "E").Value = rs("InvoiceNumber").Value

lngStartRange = lngStartRange + 1 'increment the row for next customer
rs.MoveNext 'move to next customer
Wend
objXLWrkBk.Close SaveChanges:=True
objXL.Quit
End If

Done:
Set rs = Nothing
Set db = Nothing
Set objXL = Nothing
Set objXLWrkBk = Nothing
Set objXLWrkSht = Nothing

Exit Sub

Jon

 

[chewy]

YOU ARE THE MAN! Now I can incorporate my calendar to pick the dates. Thanks sooo much!