I am trying to run a loop of transferspreadsheet commands, this has worked for several months and all of a sudden it's not running.
I get an error message "Unable to close database c:\analysis.xls" followed by invalid databse object reference and runtime error 3276 internal buffer locked by another user.
Can anybody shed some light onto what is going on? It'll get to 200403 and then fall over, everytime without fail.
Here is the code:
'C:\Documents and Settings\jamesbald\My Documents\Triangulation\200509
Dim currmnth As String
currmnth = InputBox("Please enter current month in yyyymm format")
If Right(currmnth, 2) > 12 Then
Function Analysis4(currmnth As String)
Dim mnth As String
Dim a As Integer
Dim b As Integer
Dim c As Integer
Dim Query As String
For a = 2003 To Left(currmnth, 4)
c = 12
If a = Left(currmnth, 4) Then c = Right(currmnth, 2)
For b = 1 To c
mnth = a & b
If b <= 9 Then mnth = a & "0" & b
Query = "CTQ" & mnth
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, Query, "C:\analysis.xls", 1
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, Query & "£", "C:\analysis.xls", 1