In my VB6 code, I am pulling up data from my 2012 SQL database. Then I would input the code into an excel sheet as you see from the code below. Everything was going well until I received an error. I have an error on line 140 The problem is when I try to input data into the excel sheet called Organizing. It works fine when transpose the data into the Cover Sheet. However, I get the error, subscript out of range when it tries to transpose into the Organizing page. I can't seem to figure out why that line specifically has that error. Can anyone see my issue?

Private Sub Command4_Click()Set conn = New ADODB.ConnectionSet rec = New ADODB.RecordsetSet rec1 = New ADODB.Recordsetconn.Open "Provider=sqloledb;Data Source=" & ConnectionIP & ",xxx;Network Library=DBMSSOCN;Initial Catalog= " & CAPDB & "; User ID=xx;Password=xxx"esql = ";With CTE_Hours as(select distinct RegID from tblOrgHours h inner join tblOrgActivities A on H.ActivityID = A.ActivityID Where [Hours] > 0 And h.Agency = 'Administrator' And H.Fiscal = 2018 And H.ActivityDate >= '20170701' And H.ActivityDate < '20180101')" & _ "select Count(DISTINCT CASE when R.Race = 'Asian' then h.regid else null end) as [Asian],Count(DISTINCT CASE when R.Race = 'African-American' then h.regid else null end) as [African-American] ,Count(DISTINCT CASE when R.Race = 'Caucasian' then h.regid else null end) as [Caucasian] ,Count(DISTINCT CASE when R.Race = 'Native-American' then h.regid else null end) as [Native-American] ,Count(DISTINCT CASE when R.Race = 'Multi-Racial' then h.regid else null end) as [Multi-Racial]" & _ ",Count(DISTINCT CASE when R.Race = 'Latino-Hispanic' then h.regid else null end) as [Latino-Hispanic] ,Count(DISTINCT CASE when R.AgeCurrent >= 11 and R.AgeCurrent <= 13 then h.regid else null end) as [Ages 11-13],Count(DISTINCT CASE when R.AgeCurrent >= 14 and R.AgeCurrent <= 18 then h.regid else null end) as [Ages 14-18] ,Count(DISTINCT CASE when R.AgeCurrent >= 19 and R.AgeCurrent <= 24 then h.regid else null end) as [Ages 19-24] ,Count(DISTINCT CASE when R.AgeCurrent >= 25 and R.AgeCurrent <= 65 then h.regid else null end) as [Ages 25-65]" & _ ",Count(DISTINCT CASE when R.AgeCurrent >= 66 then h.regid else null end) as [Ages 65+],Count(distinct CASE when R.Gender = 'Male' then h.regid else null end) as [Male]" & _ ",Count(distinct CASE when R.Gender = 'Female' then h.regid else null end) as [Female] from CTE_Hours H inner join tblOrgRegistrations R on H.Regid = R.RegID where R.AgeCurrent between 11 and 999" If rec.State = adStateOpen Then rec.CloseEnd If rec.CursorType = adOpenStatic rec.CursorLocation = adUseClient rec.LockType = adLockOptimistic rec.Open esql, conn, , , adCmdText rec.MoveFirst'Dumping into Cover Page-----------------------------------------------------------------------------------Set rec1 = New ADODB.Recordsetesql1 = "select GetDate()" rec1.Open (esql1), conn, adOpenStatic, adLockReadOnly ServerTime = rec1.Fields(0) rec1.Close'On Error GoTo CheckError1Set ApExcel = CreateObject("Excel.application") 'Creates an objectApExcel.Visible = True ' So you can see Excel'On Error GoTo CheckError2ApExcel.Workbooks.Open "http://www.xxxxx.org/CYSReport.xls" ApExcel.Workbooks("CYSReport.xls").Sheets("Cover Page").Cells(1, 1).Formula = Combo12.Text ApExcel.Workbooks("CYSReport.xls").Sheets("Cover Page").Cells(33, 2).Formula = rec![Asian] ApExcel.Workbooks("CYSReport.xls").Sheets("Cover Page").Cells(34, 2).Formula = rec![African-American] ApExcel.Workbooks("CYSReport.xls").Sheets("Cover Page").Cells(35, 2).Formula = rec![Native-American] ApExcel.Workbooks("CYSReport.xls").Sheets("Cover Page").Cells(36, 2).Formula = rec![Caucasian] ApExcel.Workbooks("CYSReport.xls").Sheets("Cover Page").Cells(37, 2).Formula = rec![Multi-Racial] ApExcel.Workbooks("CYSReport.xls").Sheets("Cover Page").Cells(40, 2).Formula = rec![Latino-Hispanic] ApExcel.Workbooks("CYSReport.xls").Sheets("Cover Page").Cells(42, 2).Formula = rec![Ages 11-13] ApExcel.Workbooks("CYSReport.xls").Sheets("Cover Page").Cells(43, 2).Formula = rec![Ages 14-18] ApExcel.Workbooks("CYSReport.xls").Sheets("Cover Page").Cells(44, 2).Formula = rec![Ages 19-24] ApExcel.Workbooks("CYSReport.xls").Sheets("Cover Page").Cells(45, 2).Formula = rec![Ages 25-65] ApExcel.Workbooks("CYSReport.xls").Sheets("Cover Page").Cells(46, 2).Formula = rec![Ages 65+] ApExcel.Workbooks("CYSReport.xls").Sheets("Cover Page").Cells(49, 2).Formula = rec![Female] ApExcel.Workbooks("CYSReport.xls").Sheets("Cover Page").Cells(50, 2).Formula = rec![Male]'--------------------------------'Organizing---------------------------------------------------------------------------esql = ";With CTE_Hours as (select distinct AgencyID, Agency,Classification,Objectives, Deliverables, Advocate, AdvocacyType, ActivityID, RegID, cast(ActivityDate as Date) ActivityDate, Fiscal From tblOrgHours Where [Hours] > 0) select H.Agency,A.ActivityName,H.Classification,H.ActivityDate,H.Objectives,H.Deliverables,H.Advocate,H.AdvocacyType,Count(H.RegID) as [# individuals]" & _ ",SUM(CASE when R.AgeCurrent >= 11 and R.AgeCurrent <= 13 then 1 else 0 end) as [Ages 11-13]" & _ ",SUM(CASE when R.AgeCurrent >= 14 and R.AgeCurrent <= 18 then 1 else 0 end) as [Ages 14-18]" & _ ",SUM(CASE when R.AgeCurrent >= 19 and R.AgeCurrent <= 24 then 1 else 0 end) as [Ages 19-24]" & _ ",SUM(CASE when R.AgeCurrent >= 25 and R.AgeCurrent <= 65 then 1 else 0 end) as [Ages 25-65]" & _ ",SUM(CASE when R.AgeCurrent >= 66 then 1 else 0 end) as [Ages 65+]" & _ ",SUM(CASE when R.Board = 1 then 1 else 0 end) as [CommunityCommittee],SUM(CASE when R.YouthCommittee = 1 then 1 else 0 end) as [YouthCommittee],SUM(CASE when R.Parentcheck = 1 then 1 else 0 end) as [Parentcheck],SUM(CASE when R.CommunityResident = 1 then 1 else 0 end) as [CommunityResident]" & _ ",SUM(CASE when R.Race = 'Asian' then 1 else 0 end) as [Asian]" & _ ",SUM(CASE when R.Race = 'African-American' then 1 else 0 end) as [African-American]" & _ ",SUM(CASE when R.Race = 'Caucasian' then 1 else 0 end) as [Caucasian]" & _ ",SUM(CASE when R.Race = 'Native-American' then 1 else 0 end) as [Native-American]" & _ ",SUM(CASE when R.Race = 'Multi-Racial' then 1 else 0 end) as [Multi-Racial]" & _ ",SUM(CASE when R.Race = 'Latino-Hispanic' then 1 else 0 end) as [Latino-Hispanic]" & _ ",SUM(CASE when R.Gender = 'Male' then 1 else 0 end) as [Male]" & _ ",SUM(CASE when R.Gender = 'Female' then 1 else 0 end) as [Female]" & _ ",SUM(CASE when R.Sector = 'Business' then 1 else 0 end) as [Business]" & _ ",SUM(CASE when R.Sector = 'Civic-Volunteer' then 1 else 0 end) as [Civic-Volunteer]" & _ ",SUM(CASE when R.Sector = 'Community Resident' then 1 else 0 end) as [Community Resident]" & _ ",SUM(CASE when R.Sector = 'Faith Based' then 1 else 0 end) as [Faith Based]" & _ ",SUM(CASE when R.Sector = 'Healthcare' then 1 else 0 end) as [Healthcare]" & _ ",SUM(CASE when R.Sector = 'Human Support Agencies' then 1 else 0 end) as [Human Support Agencies]" & _ ",SUM(CASE when R.Sector = 'Law Enforcement' then 1 else 0 end) as [Law Enforcement]" & _ ",SUM(CASE when R.Sector = 'Local Government' then 1 else 0 end) as [Local Government]" & _ ",SUM(CASE when R.Sector = 'Media' then 1 else 0 end) as [Media],SUM(CASE when R.Sector = 'Parent or Guardian' then 1 else 0 end) as [Parent or Guardian],SUM(CASE when R.Sector = 'Philanthropic' then 1 else 0 end) as [Philanthropic],SUM(CASE when R.Sector = 'Schools' then 1 else 0 end) as [Schools],SUM(CASE when R.Sector = 'Youth' then 1 else 0 end) as [Youth] from CTE_Hours H inner join tblOrgRegistrations R on H.Regid = R.RegID inner join tblOrgActivities A on H.ActivityID = A.ActivityID where R.AgeCurrent between 11 and 999 And h.Agency = '" & Combo12.Text & "' And H.Fiscal = 2018 And H.ActivityDate >= '" & DTPicker1 & "' And H.ActivityDate < '" & DTPicker2 & "' group by H.Agency, H.Classification,A.ActivityName,H.ActivityDate, H.Objectives, H.Deliverables, H.Advocate, H.AdvocacyType Order by H.Agency, H.Classification,H.ActivityDate,A.ActivityName" 'If rec.State = adStateOpen Then rec.Close'End If rec.CursorType = adOpenStatic rec.CursorLocation = adUseClient rec.LockType = adLockOptimistic rec.Open esql, conn, , , adCmdText rec.MoveFirst'Dumping into Organizing Page-----------------------------------------------------------------------------------j = 5Do Until rec.EOF j = j + 1 ApExcel.Workbooks("CYSReport.xls").Sheets("Organizing").Cells(j, 1).Formula = rec![Agency] ApExcel.Workbooks("CYSReport.xls").Sheets("Organizing").Cells(j, 2).Formula = rec![ActivityName] For i = 26 To 39 If rec.Fields(i) > 0 Then SectorString = SectorString + "1," End If Next i ApExcel.Workbooks("CYSReport.xls").Sheets("Organizing").Cells(j, 3).Formula = SectorStringExit Subrec.MoveNextLoopEnd Sub

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

it seems like it needs to have the Organizing tab open

0

al4629740Author Commented:

it initially opens the doc up just fine

0

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

j = 5Do Until rec.EOF j = j + 1 ApExcel.Workbooks("CYSReport.xls").Sheets("Cover Page").Activate ApExcel.Workbooks("CYSReport.xls").Sheets("Organizing").Cells(j, 1).Formula = rec![Agency] ApExcel.Workbooks("CYSReport.xls").Sheets("Organizing").Cells(j, 2).Formula = rec![ActivityName] For i = 26 To 39 If rec.Fields(i) > 0 Then SectorString = SectorString + "1," End If Next i ApExcel.Workbooks("CYSReport.xls").Sheets("Organizing").Cells(j, 3).Formula = SectorString

j = 5Do Until rec.EOF j = j + 1 ApExcel.Workbooks("CYSReport.xls").Sheets("Cover Page").Activate ApExcel.Workbooks("CYSReport.xls").Sheets("Organizing").Cells(j, 1).Formula = rec![Agency] ApExcel.Workbooks("CYSReport.xls").Sheets("Organizing").Cells(j, 2).Formula = rec![ActivityName] For i = 26 To 39 If rec.Fields(i) > 0 Then SectorString = SectorString + "1," End If Next i ApExcel.Workbooks("CYSReport.xls").Sheets("Organizing").Cells(j, 3).Formula = SectorString

no I corrected it and ran it. Same problem. I just forgot to recopy

In this one below, error on line 7

Do Until rec.EOF j = j + 1 ApExcel.Workbooks("CYSReport.xls").Sheets("Organizing").Activate ApExcel.Workbooks("CYSReport.xls").Sheets("Organizing").Cells(j, 1).Formula = rec![Agency] ApExcel.Workbooks("CYSReport.xls").Sheets("Organizing").Cells(j, 2).Formula = rec![ActivityName] For i = 26 To 39 If rec.Fields(i) > 0 Then SectorString = SectorString + "1," End If Next i ApExcel.Workbooks("CYSReport.xls").Sheets("Organizing").Cells(j, 3).Formula = SectorStringExit Sub

Have you checked all the workbook and worksheet names?

Check for things like leading/trailing space, spelling mistakes etc.

By the way, it might be an idea to create a reference to the workbook when you open it.

For example, something like this.

Set ApExcel = CreateObject("Excel.application") 'Creates an objectApExcel.Visible = True ' So you can see Excel'On Error GoTo CheckError2Set wbReport = ApExcel.Workbooks.Open( "http://www.xxxxx.org/CYSReport.xls")

I see the problem. In the workbook the name of the worksheet is "Organizing "; it has a space at the end.

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

If you expand the “Full Biography” section of my profile you’ll find links to some articles I’ve written that may interest you.

Marty - Microsoft MVP 2009 to 2017
Experts Exchange Most Valuable Expert (MVE) 2015, 2017
Experts Exchange Top Expert Visual Basic Classic 2012 to 2017

0

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for freeEdge Out The Competitionfor your dream job with proven skills and certifications.Get started todayStand Outas the employee with proven skills.Start learning today for freeMove Your Career Forwardwith certification training in the latest technologies.Start your trial today