Error 9 Subscript Out Of Range when copying records from form (datasheet) to Excel

[panchitocarioca]

Hi,

I have a small question.

In Access I have created a function that copies all (filtered) records of a form (in datasheet view) to a newly created Excel file.

At home this function / datanase works fine. When I tried to run it at work today, it did not work. I get the following error “Error 9 Subscript Out Of Range“. I googled this error and looked in forums to try to get an idea of what it is and how to resolve this, but I could not find any clear lead. It seems to be a “very general” error that could have a wide range of causes.

I have included the code of the function below this message. Does anybody have an idea what the problem is?

In case you do not know how to resolve this, but you do know another method of copying data of all shown records of a form in datasheet view into a new excel file, then I would very much appreciate it if you could explain it to me.

PS The records in the form can be filtered by using the filter command buttons of Access, only the filtered records should be copied (so exporting the query on which the form is based is not the solution as this will include the non-filtered records as well). In addition, I only want the columns which are shown in the datasheet form to be copied (depending on the situation, some columns are hidden in the form datasheet, which in such case should also not be copied into Excel. All this works fine with below code, apart from the strange error 9 problem at work.

Many thanks in advance for the help!

Best regards,

Ronald



Public Function CopyToExcel()
On Error GoTo err_handler

Dim ApXL As Object
Dim xlWBk As Object
Dim xlWSh As Object

DoCmd.SelectObject acForm, "Frm1"
DoCmd.RunCommand acCmdSelectAllRecords
DoCmd.RunCommand acCmdCopy

Set ApXL = CreateObject("Excel.Application")
Set xlWBk = ApXL.Workbooks.Add
ApXL.Visible = True

Set xlWSh = xlWBk.Worksheets("Sheet1")

ApXL.ActiveWorkbook.ActiveSheet.Range("A1").PasteSpecial Paste:=xlPasteValues
' selects all of the cells
ApXL.ActiveSheet.Cells.Select
' does the "autofit" for all columns
ApXL.ActiveSheet.Cells.EntireColumn.AutoFit
' selects the first cell to unselect all cells
xlWSh.Range("A1:A1").Select

einde:
Exit Function

err_handler:
DoCmd.SetWarnings True
MsgBox Err.Description, vbExclamation, Err.Number
Exit Function

End Function

 

[SOS]

That is not the code to send a form's records to Excel. You can't use the copy and then paste. It won't work right.

Use this code in a standard module instead (all you have to do is pass the form to the function like this):

Send2Excel Me

 

[panchitocarioca]

Dear Smeghaed,

Many thanks for the quick feedback.

I tried the code suggested by you, but the same error appears when trying it at the office: “Error 9 Subscript Out Of Range“. By the way, I have tried to run the code from several computers at the office but the same problem occurs from all computers at the office. We all use Access 2003 (SP3) and at home I also use Access 2003.

Does anyone have an idea how to solve this problem?

Many thanks in advance for the feedback and help.

Best regards,

Ronald

 

[SOS]

I think I'd need to see your database to test things out. Not sure why it would have that problem given that the code I suggested seems to work for me for most situations.

 

[panchitocarioca]

Dear Smeghead,

Thanks again for the once again quick reply.

Please find enclosed a copy of the test database. I have kept it extremely limited to just a few minimal test tables and forms so that it will hopefully be easiest to isolate the problem.

1 table with a few fields and a few records

1 query of all fields of the table

2 forms
- "Frm 1": the form in datasheet view of which i would like to copy all (filtered) records (and only the fields which are not hidden) to a new Excel file.
- "Frm2": it contains 2 command buttons: "Copy form to Excel function 1" which starts the function (CopyToExcel) that I wrote myself (based on DoCmd.RunCommand acCmdCopy ), and a command button "Copy form to Excel function 2" which starts the function suggested by you (Send2Excel).

1 module with the 2 above described functions

Runnings both functions lead to the same result. At home it works perfectly, at the office (i tried on many different PC's there), i get the error "Error 9 subscript out of range".

By the way, the function you provided does not do exactly what I have in mind. I want that only the non-hidden columns of the datasheet form are copied to the new Excel file. Using Docmd.RunCommand acCmdCopy this works well. Using the code suggested by you, all columns, including hidden ones, are copied. However, the Error 9 problem is exactly the same using both functions.

Does anyone have any idea what could cause the problem. I also checked with the macro security settings of Excel could influence this, but changing them to minimum or maximum does not have any effect.

Many thanks in advance for the help!

Best regards,

Ronald

 

[SOS]

Okay, a couple of things.

1. You do NOT modify the Function (except for the formatting part).

2. You pass the form to the function using:

Private Sub Knop1_Click()
   Send2Excel Forms!Frm1
End Sub

3. Form1 has to be open for it to work.

See attached revised sample.

 

[panchitocarioca]

Dear Smeghead,

Many thanks once again for the reply. I have tried the version you uploaded, and exactly the same thing occurs. At home it works fine, at the office, i get the error "9. Subscript out of range". Once again, i have tried on several PC's at work. We have several other access databases running on al PC's and never encountered this problem.

Does anyone have any idea what could cause this, and more importantly how this could be resolved? Could it have anything to do with settings of Access, Excel, Windows, etc.?

Once again, many thanks in advance.

Best regards,

Ronald

 

[DCrake]

Have not looked at the example offered but here is a link to an MS ariticle. You need to be looking at the CopyFromRecordset Method.
http://support.microsoft.com/kb/247412

 

[SOS]

It has to be something on your computer OR something in your data. Hard sometimes to tell which. The code I gave you works flawlessly for me and it does not use referencing so it should be fine (uses late binding) as far as different versions of Excel.

Perhaps if you uploaded the actual data (or if you prefer you could send a copy to me via email), we could determine what it might be. But if the same thing works on one computer but not another one, I am more likely thinking it has to do with your computer's configuration than anything.

Are all of the computers running the same version of Access? What about the version of Excel? Do each have the same service packs installed as well?

 

[c_smithwick]

There is an easier way to do this. Use the query (or construct one) that the form is based on and transfer your data directly to a spreadsheet with the TransferSpreadsheet method

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryName", "filePath"

substituting the name of your query for 'qryName' and the path to your spreadsheet file (one will be created) for 'filePath' Then write code to open and format your spreadsheet like you want it.

 

[SOS]

There is an easier way to do this. Use the query (or construct one) that the form is based on and transfer your data directly to a spreadsheet with the TransferSpreadsheet method

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryName", "filePath"

substituting the name of your query for 'qryName' and the path to your spreadsheet file (one will be created) for 'filePath' Then write code to open and format your spreadsheet like you want it.

Only it won't take into account any filters applied. The code I use does.