Export to Excel - Recordset

[blreid]

I'm new to VBA coding so bear with me.

I have a table that needs to get into Excel. The worksheet is important, but not necessarily the range. I would like to export using a recordset and have the user able to select the excel file (which is a template of sorts). I got a lot of help from previous posts, but with the following code, nothing is happening at all. No error message or anything. "GroupImport" is the range on the sheet "SB-UW".

On Error GoTo ErrHandler
Dim rst As New ADODB.Recordset
Dim strSQL As String
Dim xl As Excel.Application
Dim wb As Excel.Workbook
Dim sht As Excel.Worksheet
Dim rng As Excel.Range
Dim FileName As String

strSQL = "SELECT * FROM [tblGroups]"
strSQL = strSQL & "WHERE [AccountID] = '" & Me.[AccountID] & "'"
strSQL = strSQL & "ORDER BY [Group1-6]"

rst.Open strSQL, CurrentProject.Connection, adOpenKeyset, adLockOptimistic

With rst
If .RecordCount > 0 Then
Set xl = Excel.Application
FileName = xl.Application.GetOpenFilename _
("xls files(*.xls),*.xls,rpt02 files(*.rpt02),*.rpt02,all files (*.*),*.*,Text Files (*.TXT), *.txt,CSV Files (*.Csv), *.Csv&quot
xl.Visible = True
xl.DisplayAlerts = False
Set wb = xl.Workbooks.Open(FileName, AddToMRU:=False)
Set sht = wb.Sheets("SB-UW&quot
While Not .EOF
Set rng = sht.Range("GroupImport&quot
.MoveNext
Wend
End If
End With
ExitHere:
On Error Resume Next
wb.Close True
xl.Quit

Exit Sub
ErrHandler:
Debug.Print Err, Err.Description
Resume ExitHere

Any help would be greatly appreciated.

blreid

 

[ggriffit]

check out

http://www.greggriffiths.org/webdev/both/excel/

although the main thrust is web based, there should be enough info and useful links at the end to solve your problem.

 

[blreid]

I tried the ADO function example in the provided link, but it gives me an error on the open recordset line. Code on this line is below (part of the published function)


rs.Open sql, "Driver={Microsoft Access Driver (*.mdb)};Dbq=" & CurrentDb.Name & ";", adOpenForwardOnly, adLockOptimistic

Error is "The database has been placed in a state by an unknown user that prevents it from being opened or locked". There are not any tables open (only the form that the button that calls the function is located).

Code for the button is as follows:

Private Sub ExptoXL_Click()
Dim sql As String
Dim xl As Excel.Application
Dim WB As Workbook
Dim WS As Worksheet

sql = "SELECT * FROM [tblGroups]"
sql = sql & "WHERE [AccountID] = '" & Me.[AccountID] & "'"
sql = sql & "ORDER BY [Group1-6]"

Set xl = Excel.Application
FileName = xl.Application.GetOpenFilename _
("xls files(*.xls),*.xls,rpt02 files(*.rpt02),*.rpt02,all files (*.*),*.*,Text Files (*.TXT), *.txt,CSV Files (*.Csv), *.Csv&quot
Set WB = xl.Workbooks.Open(FileName)
Set WS = xl.Worksheets("Test&quot

Call TXLOut(sql, WS)
WB.Close False
xl.Quit
End Sub

The code in the first post hangs up on the same line as well. Either format looks like it should work, but something is amiss... Please help.

 

[blreid]

I'm still lost on this problem, if anyone has an idea. I looked through a post on Recordset Connection (I am running Access 2000) and used the udl wizard idea - which was cool by the way. Still having problems. Now I get a error which states "Syntax error in FROM clause" and debugger goes to the connection line in the code.

I can't find any error in the sql that I have. I even simplified it to just Select * FROM tblGroups with no success.

 

[StarPassing]

I just completed a project to export a large amount of data to Excel and reformat it with formulas, adding rows and columns by numerous accounts. So I know how you feel. I knew nothing about Excel last month and only very little today.

This part of your code looks odd:

While Not .EOF
Set rng = sht.Range("GroupImport&quot
.MoveNext
Wend

You do not appear to be sending anything to Excel.

I used commands like:

With myExcelApp.sheets("Quality&quot
.Cells(myRow, 1) = rsAwardPoints!Area
.Cells(myRow, 3) = rsAwardPoints!Title
.Cells(myRow, 3).VerticalAlignment = xlBottom
.Cells(myRow, 3).HorizontalAlignment = xlGeneral
.Cells(myRow, 3).WrapText = True
.Cells(myRow, 3).VerticalAlignment = xlBottom
.Range("A" & myRow & ":H" & myRow).Interior.Color = 11992989
End With

Where rsAwardPoints is the table. MyRow is the row to poke data into.

I found a sample that I used to start:

http://www.paragoncorporation.com/ArticleDetail.aspx?ArticleID=8

Hope this helps.

Tom

 

[blreid]

Thanks Tom. I'll take a look. In regards to the code looking odd, I agree. I have also tried the simplest method of all - TransferSpreadsheet. I can get this to work in a simple spreadsheet file. However, the file(s) that I need to get to are about 3.2Mb with macros and about 20 worksheets. For some reason, when I use TransferSpreadsheet for Export, it doubled the file size and I get an error "Excel.exe has generated errors..." (well, I guess we've all seen that one). Then the file is useless and I'm not even sure if it worked. The same thing happens when I just simply click export on the File menu.

Thanks for the help. If any ideas on the TransferSpreadsheet problem, it would be helpful. I would really like to get the ADO or DAO recordset running so I can say I learned something.

Brian