Transpose query and grow / Shrink columns in excel

[Costefran]

Can anyone help with the following

I have a query in which the first column is based on a start date, and end date and all dates in between. I have some ideas on how to do this but would then want to transpose this and export to excel. Depending on user seletions the excel columns of my target template excel spreadsheet may grow or shrink depending on user selection of start and end dates

For example, the query results for user 1 may be

01/01/2010
02/01/2010
03/01/2010
04/01/2010

In excel it would look like this
Column A Column B Column C Column D
01/01/2010 02/01/2010 03/01/2010 04/01/2010

User 2 entries may be greater and mean that the columns for each date stretch to Column E or more (or even less)

I have copied a sample code from an existing export as I thought there may be a way to add code to achieve this. As the template excell cells may be formatted I assume it would be better to insert rows rather than add existing columns on the end

Function OpenSpecific_xlFile()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim sFullPath As String

Set db = CurrentDb
Set rs = db.OpenRecordset("" & Forms![Log in Form]![Name] & " Table" & "", dbOpenSnapshot)

'Start a new workbook in Excel
Dim oApp As New Excel.Application
Dim oBook As Excel.Workbook
Dim oSheet As Excel.Worksheet
sFullPath = Forms![Log in Form]![Log In Form Query for Subform].Form![File Path for Spend Reports] & "\Work Schedule Report Spreadsheet2.xls"
Set oBook = oApp.Workbooks.Open(sFullPath)

Set oSheet = oBook.Worksheets(1)
oSheet.Range("A9:X6123").ClearContents
'Add the data starting at cell A9
oSheet.Range("A9").CopyFromRecordset rs

oApp.Visible = True
oApp.UserControl = True

'Close the Database and Recordset
'rs.Close
'db.Close
End Function

 

[SkipVought]

Hi,

Is there ALWAYS data in A9:X6123?

Is there ONLY data in A9:X6123?

You are placing the recordset data at A9. Does your query transpose the data, or do you want to transpose in Excel?

Please answer ALL THREE of these questions.


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Costefran]

Thanks I have worked out that I can transpose once in excel but I now have another problem which I am trying to work out and its this
I now have my excel spreadsheet and would like to export 2 different tables (each having different numbers of records) to the same spreadsheet. The first table being exported to A9 and the 2nd table being exported to C9

Here is my code for exporting one table but not sure where to go from here (if its possible)


Function OpenSpecific_xlFile()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim sFullPath As String
'Set db = DAO.DBEngine.Workspaces(0).OpenDatabase( _
"C:\database.mdb")
Set db = CurrentDb
Set rs = db.OpenRecordset("" & Forms![Log in Form]![Name] & " Table" & "", dbOpenSnapshot)

'Start a new workbook in Excel
Dim oApp As New Excel.Application
Dim oBook As Excel.Workbook
Dim oSheet As Excel.Worksheet
sFullPath = Forms![Log in Form]![Log In Form Query for Subform].Form![File Path for Spend Reports] & "\Work Schedule Report Spreadsheet2.xls"
Set oBook = oApp.Workbooks.Open(sFullPath)


'Set oBook = oApp.Workbooks.Add
Set oSheet = oBook.Worksheets(1)
oSheet.Range("A9:X6123").ClearContents
'Add the data starting at cell A9
oSheet.Range("A9").CopyFromRecordset rs

oApp.Visible = True
oApp.UserControl = True

'Close the Database and Recordset
'rs.Close
'db.Close
End Function

 

[SkipVought]

If it were me, but I'm a heavy Excel guy, I'd do this ALL in Excel and use MS Query to grab whatever data from MS Access. Data > Import External data > New Database Query... Works very similar to Access QBE grid.

Once installed, a Data > Refresh is all you need to get new data into the sheet.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Costefran]

Many thanks I'll give that a go