I've been trying to get this coded to work for about 5 hours now without progress. My code aims to split a large table into several smaller tables and export these into excel (the actual tables will exceed 1000000 records). The code continues to produce an error 3011, stating it can not find the object 'tmpdata1' in the transferspreadsheet command. The current code is as follows:
NOTE: DTable is an existing table within the database, and is defined earlier in the coding as a public string.
Private Sub Export_over_Multiple_Sheets_Click()
Dim rs As New ADODB.Recordset
Dim cn As New ADODB.Connection
Set cn = CurrentProject.Connection
Dim rowcount As Long
Dim tblcount As Integer
Dim i As Integer
Dim t As TableDef
Dim tblx As String
Dim dbsDatas As DAO.Database
Set dbsDatas = CurrentDb
dbsDatas.TableDefs.Refresh
Dim strWorksheetPathTable As String
Dim xlApp As Object
Dim xlWB As Object
'----Set File Path
strWorksheetPathTable = "O:\Data\Downstream POC\DWN Data Mgmt\Reports\"
strWorksheetPathTable = strWorksheetPathTable & "" & DTable & "\" & DTable & ".xlsb"
Set xlApp = CreateObject("Excel.Application")
Set xlWB = xlApp.Workbooks.Open("" & strWorksheetPathTable & "")
SQL = "SELECT * INTO tmpdata FROM " & DTable & ""
DoCmd.RunSQL SQL
SQL = "ALTER TABLE tmpdata ADD COLUMN id COUNTER"
DoCmd.RunSQL SQL
SQL = "SELECT count(*) as rowcount from " & DTable & ""
rs.Open SQL, cn
rowcount = rs!rowcount
rs.Close
tblcount = rowcount / 50000 + 1
For i = 1 To tblcount
SQL = "SELECT * into tmpdata" & i & " FROM tmpdata" & _
" WHERE id<=50000*" & i
DoCmd.RunSQL SQL
SQL = "DELETE * FROM tmpdata" & _
" WHERE id<=50000*" & i
DoCmd.RunSQL SQL
dbsDatas.TableDefs.Refresh
Set t = Nothing
Set t = dbsDatas.TableDefs("tmpdata" & i & "")
tblx = "tmpdata" & i & ""
DoCmd.TransferSpreadsheet transfertype:=acExport, _
spreadsheettype:=acSpreadsheetTypeExcel12, _
TableName:=tblx, FileName:=strWorksheetPathTable, _
hasfieldnames:=True
Next i
xlWB.Save
xlWB.Close
End Sub
I understand that my coding may be a bit messy, I've only been teaching myself vba for a couple months so far. Any help would be greatly appreciated.
Dane I
Figured it out. Some of the old variables no longer being used by my coding (t, xlApp, xlWB) were faulting the export. Removing these from the coding fixed the issue.