Writing to Excel Programmatically

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I've written this code that writes data to an Excel file and for some reason
if I create an index (make it the primary key or unique) on a table in this
code, some data on one of my worksheets disappears. Now if I create an index
and I don't make it a primary key or unique, the data does not disappear.

Does anyone know what's going on here? Is there something that needs to be
turned off or cleared after I create this index or before I create it?

Any advice would be greatly appreciated.

Thank you,

Sarah
 
Can't see your code from here, Sarah, could you use a larger font or maybe
post it here :)
 
SourceName = "Seq"
strFile = XLSFileName
Set Db = New ADODB.Connection
Db.CursorLocation = adUseClient
Db.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" &
Application.CurrentProject.FullName
Set xlApp = CreateObject("Excel.Application")
Set xlwbBook = xlApp.Workbooks.Open(strFile)
Set xlwsSheet1 = xlwbBook.worksheets(SourceName)
xlwsSheet1.Activate

' Append Sequence data to Excel File
SQL = "SELECT RevNo, [Resource], [NewSeqNo], FuncSeq1, ToolNo,
TheDevice, DevNameDesc, ResultPos, ResultSensor, PPSwitch, IntefSwitch,
[StartTime], [TotalTime], [EndTime], Comments, LinkIt, SeqNo " _
& "FROM " & TheIDRU & " Where (Not (Indnum) Is Null) Order by IndNum;"
y = xlApp.ActiveCell.Column - 1
xlApp.ActiveCell.Offset(0, -y).SELECT
x = xlwsSheet1.Application.ActiveCell.Cells.Address
rs.CursorLocation = adUseClient
If rs.State = adStateOpen Then
rs.Close
End If
rs.Open SQL, Db
If rs.RecordCount > 0 Then
rs.MoveFirst
x = Replace(x, "$", "")
y = Mid(x, 2)
Set rng = xlwsSheet1.Range(x)
xlwsSheet1.Range(x).CopyFromRecordset rs
End If
rs.Close
' Delete Temporary Tables
DoCmd.DeleteObject acTable, TheIDRU

SourceName = "Sensors"
Set xlwsSheet5 = xlwbBook.worksheets(SourceName)
xlwsSheet5.Activate
SQL = "SELECT Rev, ToolNo, Sensor, [NACCL#], NDesc, NStock, '' as
PlaceHold, ToolSwitch from " & TheIDST & " ORDER BY ToolNo, Sensor;"
Db.Close
Set Db = New ADODB.Connection
Db.CursorLocation = adUseClient
Db.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" &
Application.CurrentProject.FullName
y = xlApp.ActiveCell.Column - 1
xlApp.ActiveCell.Offset(0, -y).SELECT
x = xlwsSheet5.Application.ActiveCell.Cells.Address
rs.CursorLocation = adUseClient
If rs.State = adStateOpen Then
rs.Close
End If
rs.Open SQL, Db
If rs.RecordCount > 0 Then
rs.MoveFirst
x = Replace(x, "$", "")
y = Mid(x, 2)
Set rng = xlwsSheet5.Range(x)
xlwsSheet5.Range(x).CopyFromRecordset rs
End If
rs.Close
' Delete Temporary Tables
DoCmd.DeleteObject acTable, TheIDST

SourceName = "Seq"
Set xlwsSheet1 = xlwbBook.worksheets(SourceName)
xlwsSheet1.Activate

xlwbBook.Close True
xlApp.Quit
Set xlwsSheet1 = Nothing
Set xlwsSheet5 = Nothing
Set xlwbBook = Nothing
Set xlApp = Nothing
Db.Close

You asked for it... this is the jist of it. If I try to create an index in
TheIDST table, I get missing information on my first worksheet in the
workbook.
 
I don't see anything obvious, but one thing I would check is to see if there
are any records that have Null in either the ToolNo or Sensor fields.
Creating an unique or primary index on a table will cause it to ignore
existing nulls; therefore, the associated record will not be included.
I can't guarantee this is the problem, but that is where I would look first.
--
Dave Hargis, Microsoft Access MVP


Sarah said:
SourceName = "Seq"
strFile = XLSFileName
Set Db = New ADODB.Connection
Db.CursorLocation = adUseClient
Db.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" &
Application.CurrentProject.FullName
Set xlApp = CreateObject("Excel.Application")
Set xlwbBook = xlApp.Workbooks.Open(strFile)
Set xlwsSheet1 = xlwbBook.worksheets(SourceName)
xlwsSheet1.Activate

' Append Sequence data to Excel File
SQL = "SELECT RevNo, [Resource], [NewSeqNo], FuncSeq1, ToolNo,
TheDevice, DevNameDesc, ResultPos, ResultSensor, PPSwitch, IntefSwitch,
[StartTime], [TotalTime], [EndTime], Comments, LinkIt, SeqNo " _
& "FROM " & TheIDRU & " Where (Not (Indnum) Is Null) Order by IndNum;"
y = xlApp.ActiveCell.Column - 1
xlApp.ActiveCell.Offset(0, -y).SELECT
x = xlwsSheet1.Application.ActiveCell.Cells.Address
rs.CursorLocation = adUseClient
If rs.State = adStateOpen Then
rs.Close
End If
rs.Open SQL, Db
If rs.RecordCount > 0 Then
rs.MoveFirst
x = Replace(x, "$", "")
y = Mid(x, 2)
Set rng = xlwsSheet1.Range(x)
xlwsSheet1.Range(x).CopyFromRecordset rs
End If
rs.Close
' Delete Temporary Tables
DoCmd.DeleteObject acTable, TheIDRU

SourceName = "Sensors"
Set xlwsSheet5 = xlwbBook.worksheets(SourceName)
xlwsSheet5.Activate
SQL = "SELECT Rev, ToolNo, Sensor, [NACCL#], NDesc, NStock, '' as
PlaceHold, ToolSwitch from " & TheIDST & " ORDER BY ToolNo, Sensor;"
Db.Close
Set Db = New ADODB.Connection
Db.CursorLocation = adUseClient
Db.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" &
Application.CurrentProject.FullName
y = xlApp.ActiveCell.Column - 1
xlApp.ActiveCell.Offset(0, -y).SELECT
x = xlwsSheet5.Application.ActiveCell.Cells.Address
rs.CursorLocation = adUseClient
If rs.State = adStateOpen Then
rs.Close
End If
rs.Open SQL, Db
If rs.RecordCount > 0 Then
rs.MoveFirst
x = Replace(x, "$", "")
y = Mid(x, 2)
Set rng = xlwsSheet5.Range(x)
xlwsSheet5.Range(x).CopyFromRecordset rs
End If
rs.Close
' Delete Temporary Tables
DoCmd.DeleteObject acTable, TheIDST

SourceName = "Seq"
Set xlwsSheet1 = xlwbBook.worksheets(SourceName)
xlwsSheet1.Activate

xlwbBook.Close True
xlApp.Quit
Set xlwsSheet1 = Nothing
Set xlwsSheet5 = Nothing
Set xlwbBook = Nothing
Set xlApp = Nothing
Db.Close

You asked for it... this is the jist of it. If I try to create an index in
TheIDST table, I get missing information on my first worksheet in the
workbook.

Klatuu said:
Can't see your code from here, Sarah, could you use a larger font or maybe
post it here :)
Click to expand...
Click to expand...
 
Thanks, but I came up with a solution that works. I'm writing to sheet5
before I write to sheet1 and theat seems to have solved the problem. Thanks
for your input

Sarah

Klatuu said:
I don't see anything obvious, but one thing I would check is to see if there
are any records that have Null in either the ToolNo or Sensor fields.
Creating an unique or primary index on a table will cause it to ignore
existing nulls; therefore, the associated record will not be included.
I can't guarantee this is the problem, but that is where I would look first.
--
Dave Hargis, Microsoft Access MVP


Sarah said:
SourceName = "Seq"
strFile = XLSFileName
Set Db = New ADODB.Connection
Db.CursorLocation = adUseClient
Db.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" &
Application.CurrentProject.FullName
Set xlApp = CreateObject("Excel.Application")
Set xlwbBook = xlApp.Workbooks.Open(strFile)
Set xlwsSheet1 = xlwbBook.worksheets(SourceName)
xlwsSheet1.Activate

' Append Sequence data to Excel File
SQL = "SELECT RevNo, [Resource], [NewSeqNo], FuncSeq1, ToolNo,
TheDevice, DevNameDesc, ResultPos, ResultSensor, PPSwitch, IntefSwitch,
[StartTime], [TotalTime], [EndTime], Comments, LinkIt, SeqNo " _
& "FROM " & TheIDRU & " Where (Not (Indnum) Is Null) Order by IndNum;"
y = xlApp.ActiveCell.Column - 1
xlApp.ActiveCell.Offset(0, -y).SELECT
x = xlwsSheet1.Application.ActiveCell.Cells.Address
rs.CursorLocation = adUseClient
If rs.State = adStateOpen Then
rs.Close
End If
rs.Open SQL, Db
If rs.RecordCount > 0 Then
rs.MoveFirst
x = Replace(x, "$", "")
y = Mid(x, 2)
Set rng = xlwsSheet1.Range(x)
xlwsSheet1.Range(x).CopyFromRecordset rs
End If
rs.Close
' Delete Temporary Tables
DoCmd.DeleteObject acTable, TheIDRU

SourceName = "Sensors"
Set xlwsSheet5 = xlwbBook.worksheets(SourceName)
xlwsSheet5.Activate
SQL = "SELECT Rev, ToolNo, Sensor, [NACCL#], NDesc, NStock, '' as
PlaceHold, ToolSwitch from " & TheIDST & " ORDER BY ToolNo, Sensor;"
Db.Close
Set Db = New ADODB.Connection
Db.CursorLocation = adUseClient
Db.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" &
Application.CurrentProject.FullName
y = xlApp.ActiveCell.Column - 1
xlApp.ActiveCell.Offset(0, -y).SELECT
x = xlwsSheet5.Application.ActiveCell.Cells.Address
rs.CursorLocation = adUseClient
If rs.State = adStateOpen Then
rs.Close
End If
rs.Open SQL, Db
If rs.RecordCount > 0 Then
rs.MoveFirst
x = Replace(x, "$", "")
y = Mid(x, 2)
Set rng = xlwsSheet5.Range(x)
xlwsSheet5.Range(x).CopyFromRecordset rs
End If
rs.Close
' Delete Temporary Tables
DoCmd.DeleteObject acTable, TheIDST

SourceName = "Seq"
Set xlwsSheet1 = xlwbBook.worksheets(SourceName)
xlwsSheet1.Activate

xlwbBook.Close True
xlApp.Quit
Set xlwsSheet1 = Nothing
Set xlwsSheet5 = Nothing
Set xlwbBook = Nothing
Set xlApp = Nothing
Db.Close

You asked for it... this is the jist of it. If I try to create an index in
TheIDST table, I get missing information on my first worksheet in the
workbook.

Klatuu said:
Can't see your code from here, Sarah, could you use a larger font or maybe
post it here :)
--
Dave Hargis, Microsoft Access MVP


:

I've written this code that writes data to an Excel file and for some reason
if I create an index (make it the primary key or unique) on a table in this
code, some data on one of my worksheets disappears. Now if I create an index
and I don't make it a primary key or unique, the data does not disappear.

Does anyone know what's going on here? Is there something that needs to be
turned off or cleared after I create this index or before I create it?

Any advice would be greatly appreciated.

Thank you,

Sarah
Click to expand...
Click to expand...
Click to expand...
 
Back
Top