Rx_
Nothing In Moderation
- Local time
- Today, 08:07
- Joined
- Oct 22, 2009
- Messages
- 2,803
SQL Server Linked Table (a view) --> MS Access --> Excel
MS Access report generator using Excel with remote automation.
Will be using a Recordset to move data (copy from recordset) into an Excel template.
Using the recordset, will evaluate 27 columns. On average, about half of them will have no data.
The idea is after the data is in Excel, hide the columns with no data.
For example: if Column F, J, M have no data, hide them.
The rows rarely go past 1,000.
Trying to decide for example to use SQL and create an Array of the empty columns to use in vba excel remote automation.
Or, to use something like a Sum function and loop through 27 columns to make the determination.
Or... Maybe someone else has an idea?
MS Access report generator using Excel with remote automation.
Will be using a Recordset to move data (copy from recordset) into an Excel template.
Using the recordset, will evaluate 27 columns. On average, about half of them will have no data.
The idea is after the data is in Excel, hide the columns with no data.
For example: if Column F, J, M have no data, hide them.
The rows rarely go past 1,000.
Trying to decide for example to use SQL and create an Array of the empty columns to use in vba excel remote automation.
Or, to use something like a Sum function and loop through 27 columns to make the determination.
Or... Maybe someone else has an idea?
Code:
Set rs = DB.OpenRecordset(TableName, dbOpenSnapshot)
rs.MoveLast
rs.MoveFirst
rsRowCount = rs.RecordCount
rsColumnCount = rs.Fields.Count
' Function - evaluate empty columns in rs and create array
ObjXL.Workbooks(1).Worksheets(TabName).Select '
ObjXL.Range(StartLocation).Select
ObjXL.Workbooks(1).Worksheets(TabName).Range(StartLocation).CopyFromRecordset rs, rsRowCount, rsColumnCount
DoEvents
' Data input keeps turning on Autowrap in some cases - fix that now
TotalSumRow = rsRowCount + 6 + 1 ' add start location here
ObjXL.Range("A" & TotalSumRow).Select
' use Function array to hide Objxl.Columns( xxxx) columns here