Creating Excel Worksheets based on conditions

[owuraku]

At the end of each year (i.e. on every December 31) I want to create a new Excel Workbook (spreadsheet). Thus on December 31, 2008, I want to create a workbook called 2009. Now at the day 1of each month in 2009, I want to create a worksheet named after that month. So for on Jan 31, 2009, I want to create a worksheet called January in the workbook 2009. Clearly I have to use a loop to generate the worksheets but I will go a SELECT CASE instead. Also I am inclined to go with an "IF...ELSE" statement for the creation of the workbooks.

The skeleton of my code would probably be along these lines:

If Format(Now, "dd-mmm") = 01-Feb
Create workbook named [Format(Now, "yyyy")] and a worksheet named [DateAdd("m",-1,Date()]and dump in Location "x".
Else 
Select Case MonthDay (Where MonthDay = Format(Now, "dd-mmm") )
Case 01-March
Create Worksheet named [DateAdd("m",-1,Date()] in workbook named [Format(Now, "yyyy")] 
Case 01-April
Create Worksheet named [DateAdd("m",-1,Date()] in workbook named [Format(Now, "yyyy")] 
.
.
.
Case 01-Dec
Create Worksheet named [DateAdd("m",-1,Date()] in workbook named [Format(Now, "yyyy")] 
Case 01-Jan
Create Worksheet named [DateAdd("m",-1,Date()] in workbook named [Format(Now, "yyyy")]

I am yet to provide the actual coding according to the above guide. Any expert can test out my guide with a real code and let me know if it works. I am working on it and I will let you guys know how far I get and what errors I generate.

Thanks guys!!

[GazMathias]

What needs to be written to the worksheets?

If nothing that comes from Access itself then I have to ask the question why not simply set up a workbook that runs the code when you click a button instead?

Or is this a "lets do it, see if it can be done" type of question?

[owuraku]

What needs to be written to the worksheets?

If nothing that comes from Access itself then I have to ask the question why not simply set up a workbook that runs the code when you click a button instead?

Or is this a "lets do it, see if it can be done" type of question?

\

Basically I need a code translation for the skeleton I generated. So in a way I suppose it will be a "lets do it, see if it can be done" type of question.

[owuraku]

Oh I probably didnt mention this but the coding should be done in ACCESS VBA.

[GazMathias]

Oh I probably didnt mention this but the coding should be done in ACCESS VBA.

You didn't need to mention that ;-)

[owuraku]

I have been able to create the excel sheet according to the following specification:
[1] When the code is run the first time in a year, an excel spreadsheet is created which contains 12 worksheets, for each month of the year.
[2] Whenever the code is run again, data should only be added to the existing spreadsheet, no new excel workbooks should be created.

Now what I need is to transfer data from ACCESS to a specific worksheet on the workbook. That is if the code is run in say, August, Data should be transfered into the AUGUST worksheet. When it is ran in DECEMBER, for instance, data should be transferred into the DECEMBER worksheet.

I know I could use an ADO or DAO connection but I am slightly confused as to the exact coding mechanics involved in pulling this off with these methods.

Here's my code thus far:

Option Compare Database

Function bFileExists(sFile As String) As Boolean
If Dir(sFile) <> "" Then bFileExists = True
End Function
Sub Archif()
Dim sFileName As String
Dim wkb1 As Workbook
Dim wks As Worksheet
Dim ExcelSheet As Object
Dim i As Integer
Dim x As Integer
If Month(Date) > 1 Then
sFileName = "H:\Excel\Advance Tracking Archive " & Format(Date, "yyyy") & ".xls"
Else
sFileName = "H:\Excel\Advance Tracking Archive " & Format(DateAdd("yyyy", -1, Date), "yyyy") & ".xls"
End If
If bFileExists(sFileName) = True Then
Set wkb1 = Workbooks.Open(Filename:=sFileName)
For i = 1 To ActiveWorkbook.Sheets.Count
    If Month(Date) > 1 And Month(Date) = Worksheets(i).Index Then
    Worksheets(i - 1).Select
    Range("A1").Value = Format(Date, "ddd mmm dd, yyyy")
    Range("A1").EntireColumn.AutoFit
    End If
    If Month(Date) = 1 And Month(Date) = Worksheets(i).Index Then
    Worksheets(12).Select
    Range("A1").Value = Format(Date, "ddd mmm dd, yyyy")
    Range("A1").EntireColumn.AutoFit
    End If
Next
wkb1.Close SaveChanges:=True
Else
Set ExcelSheet = CreateObject("Excel.Sheet")
    ExcelSheet.SaveAs sFileName
    ExcelSheet.Application.Quit
Set wkb1 = Workbooks.Open(Filename:=sFileName)
Do While Worksheets.Count < 12
    ActiveWorkbook.Sheets.Add
Loop
For x = 1 To ActiveWorkbook.Sheets.Count
    Sheets(x).Name = MonthName(x)
    If Month(Date) > 1 And Month(Date) = Worksheets(x).Index Then
    Worksheets(x - 1).Select
    Range("A1").Value = Format(Date, "ddd mmm dd, yyyy")
    Range("A1").EntireColumn.AutoFit
    End If
    If Month(Date) = 1 And Month(Date) = Worksheets(x).Index Then
    Worksheets(12).Select
    Range("A1").Value = Format(Date, "ddd mmm dd, yyyy")
    Range("A1").EntireColumn.AutoFit
    End If
Next
wkb1.Close SaveChanges:=True
End If
End Sub

[Stewart Ross]

Hi. You do not give enough detail to provide clear guidance, so I'll just make some observations here and leave it to you to investigate their use more thoroughly.

Firstly, there is a very useful Excel method called CopyFromRecords et which will transfer data from an Access recordset directly to an Excel worksheet. There are examples of the use of CopyFromRecords et on this site which you can find using our search facilities. As its name implies, it works on an Access recordset (DAO or ADO). Although you need to open the recordset you do not otherwise need to do any loop processing, as CopyFromRecords et transfers all rows of the recordset data to Excel in the one operation.

Secondly, it is straightforward to select a specific worksheet for the corresponding month in your workbook given that the current month can easily be found in several different ways (as a number by using the month function on the current date, or as a string by formatting the current date to return the current month, and so on). For example, if you had a Case statement to select the month and you know that it is December you could use the Worksheets object to return the December worksheet through its name:

set objWorkSheet = objExcel.WorkSheets("December")

Thirdly, you can use the Excel specialcells method to return a reference to the lastcell of the selected worksheet. This in turn can be used with a Range object to return the lastrow used in the current sheet. That value tells you where the next blankrow starts so that you can use CopyFromRecords et to place the Access data in the range starting from the next row. Again, there are examples of this on this site.

Finally, a warning: don't use references to ActiveWorkbook, Range or other Excel objects in Access VBA code without explicitly using a suitable Excel Workbook object (or a worksheet or range object) set to the current Excel instance (or a worksheet or range within it). You are not doing so in the code you have listed. Whilst this approach works in Excel, which can interpret the implicit references to activeworkbooks and ranges, it WILL NOT WORK in automation code running within Access. There are many examples again on this site where posters have spent many fruitless hours trying to trace errors that arise from implicit references to Excel ranges and worksheet objects. ALWAYS use an explicit object reference for such automation code.

-Stewart

[owuraku]

Thanks y'all I got it to work. Used the Recordset technique. Extracted the data from a query and then used to a For Loop to copy it into specified cells.