method ‘range of object’ global failed

have an access data base where i generate 2 excel reports as the final step. when i run report 1, the excel part works fine. when i try to run report 2, i get the message ‘method range of object’ global failed. if i close the database, reopen and run report 2, works fine. have tried multi things, none of which work.  at first thought, the excel app was still open from the first report and that was causing the issue.  put in code to make sure 1st excel app was closed. nothing worked.  i am calling a routine to insert a blank row when there is a change in a specific field. here is the code for the excel part….Public Sub Excelapp2()
On Error GoTo Err_excelapp2_click

‘OutFileName = “C:temp” + “claims for –  ” & strname & ” ” & Format(Now(), “mmDDYYYYHHMM”) + “.xls”

OutFileName = “C:temphosp_claims_report.xls”
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, strname, OutFileName, True, “”
 
Set xlApp = CreateObject(“excel.application”)
Set xlbook = xlApp.ActiveWorkbook
Set xlsheet = xlApp.Workbooks.Open(OutFileName).Sheets(1)

xlsheet.Activate
xlsheet.Columns(“A:BA”).Select

Selection.Sort Key1:=Range(“A:A”), Order1:=xlAscending, _
               Key2:=Range(“B:B”), Order2:=xlAscending, _
               Header:=xlYes, OrderCustom:=1, Orientation:=xlTopToBottom, _
               DataOption1:=xlSortTextAsNumbers, DataOption2:=xlSortNormal, _
               DataOption3:=xlSortNormal

xlsheet.Range(“A2:BA2”).Select

xlApp.ActiveWindow.FreezePanes = True
xlsheet.Range(“a1:BA1”).WrapText = True
xlsheet.Range(“a1:BA1”).Font.Bold = True

‘xlsheet.Range(“U1:AA1”).Interior.ColorIndex = 6
‘xlsheet.Range(“BH1:BJ1”).Interior.ColorIndex = 44
xlsheet.Cells(1, 27).Value = “Procedure code”
xlsheet.Range(“A:A”).ColumnWidth = 11
xlsheet.Range(“B:B”).ColumnWidth = 14
xlsheet.Range(“C:C”).ColumnWidth = 44
xlsheet.Range(“D:D”).ColumnWidth = 13
xlsheet.Range(“E:E”).ColumnWidth = 7
xlsheet.Range(“F:F”).ColumnWidth = 14
xlsheet.Range(“G:H”).ColumnWidth = 16
xlsheet.Range(“I:I”).ColumnWidth = 19
xlsheet.Range(“J:J”).ColumnWidth = 12
xlsheet.Range(“K:K”).ColumnWidth = 14
xlsheet.Range(“M:M”).ColumnWidth = 12
xlsheet.Range(“N:N”).ColumnWidth = 14
xlsheet.Range(“N:N”).EntireColumn.Insert
xlsheet.Cells(1, 14).Value = “Benefit listed on benefit grid”
xlsheet.Range(“N1:V1”).Interior.ColorIndex = 4
xlsheet.Range(“O:O”).ColumnWidth = 17
xlsheet.Range(“U:W”).ColumnWidth = 13
xlsheet.Range(“X:Z”).ColumnWidth = 12
xlsheet.Range(“AA:AA”).ColumnWidth = 14
xlsheet.Range(“AB:AB”).ColumnWidth = 12
xlsheet.Range(“AF:AF”).ColumnWidth = 13
xlsheet.Range(“AG:AG”).ColumnWidth = 11
xlsheet.Range(“AH:AH”).ColumnWidth = 16
xlsheet.Range(“AI:AI”).ColumnWidth = 13
xlsheet.Range(“AJ:AK”).ColumnWidth = 12
xlsheet.Range(“AL:AL”).ColumnWidth = 13
xlsheet.Range(“AN:AP”).ColumnWidth = 12
xlsheet.Range(“AP:AS”).ColumnWidth = 15
xlsheet.Range(“BA:BA”).ColumnWidth = 12
xlsheet.Cells(1, 53).Value = “Pass/Fail”

Call InsertRowAtChangeInValue

MsgBox “file has been processed – hospital claims report available”
xlApp.ActiveWorkbook.Save
xlApp.ActiveWorkbook.Close “C:temp” + “hosp_claims_report.xls”
xlApp.Application.Quit

Set xlApp = Nothing
Set xlsheet = Nothing
Set xlbook = Nothing

Exit_excelapp2_Click:
 
    Exit Sub

Err_excelapp2_click:
    MsgBox Err.Description
    Resume Exit_excelapp2_Click
   
End Sub

 

 

here is the code for inserting the row…..

Private Sub InsertRowAtChangeInValue()
      ‘column B contains then scenario id, so want to insert a blank line when that changes
  For lRow = Cells(Cells.Rows.Count, “B”).End(xlUp).row To 2 Step -1
      If Cells(lRow, “B”) <> Cells(lRow – 1, “B”) Then
           Rows(lRow).EntireRow.Insert
      End If
  Next lRow
 
End Sub

 

 

thanks for your help

 

By: john newlander

2,295 thoughts on “method ‘range of object’ global failed

  1. hiking

    Having read this I thought it was extremely informative. I appreciate you spending some time and energy to put this information together. I once again find myself spending a significant amount of time both reading and leaving comments. But so what, it was still worthwhile!

  2. Baixar App Lista Iptv Gratis

    Ponto IPTV a melhor programacao de canais IPTV do Brasil, filmes, series, futebol, lutas, shows, documentarios em alta definicao de imagem e som em SD, HD, FULL HD, 4K. compativel com todos aparelhos do mercado Brasileiro.

  3. udfauzi.com

    Hi, I do believe this is a great site. I stumbledupon it 😉 I am going to revisit once again since I book marked it. Money and freedom is the greatest way to change, may you be rich and continue to guide others.

  4. App Iptv Brasil Gratuito 3.0

    Ponto IPTV a melhor programacao de canais IPTV do Brasil, filmes, series, futebol, lutas, shows, documentarios em alta definicao de imagem e som em SD, HD, FULL HD, 4K. compativel com todos aparelhos do mercado Brasileiro.

  5. genolyze

    May I just say what a relief to discover somebody who actually knows what they’re talking about on the internet. You certainly realize how to bring an issue to light and make it important. A lot more people ought to read this and understand this side of the story. I was surprised that you’re not more popular since you most certainly possess the gift.

  6. harga-jual.com

    You have made some really good points there. I looked on the web for more info about the issue and found most people will go along with your views on this web site.

  7. vivoslot.net

    An interesting discussion is worth comment. I do think that you ought to write more about this subject matter, it might not be a taboo subject but generally people don’t speak about such issues. To the next! Cheers!!

  8. Apk Tv Iptv

    Ponto IPTV a melhor programacao de canais IPTV do Brasil, filmes, series, futebol, lutas, shows, documentarios em alta definicao de imagem e som em SD, HD, FULL HD, 4K. compativel com todos aparelhos do mercado Brasileiro.

  9. download vivoslot apk

    I blog frequently and I truly appreciate your content. This great article has really peaked my interest. I’m going to take a note of your website and keep checking for new details about once per week. I opted in for your Feed as well.

  10. vivoslot

    I need to to thank you for this fantastic read!! I absolutely enjoyed every bit of it. I’ve got you bookmarked to check out new things you post…

  11. togel sidny

    Everything is very open with a very clear description of the challenges. It was truly informative. Your website is very helpful. Thanks for sharing!

  12. daftar vivoslot

    Your style is very unique compared to other people I’ve read stuff from. Thanks for posting when you have the opportunity, Guess I’ll just bookmark this blog.

  13. vivoslot ios

    Right here is the right site for anybody who wants to find out about this topic. You know so much its almost hard to argue with you (not that I personally will need to…HaHa). You certainly put a brand new spin on a subject that’s been discussed for a long time. Wonderful stuff, just excellent!

  14. dingdong 36d live

    Can I simply just say what a relief to find somebody that actually understands what they’re talking about over the internet. You certainly understand how to bring an issue to light and make it important. More and more people should look at this and understand this side of the story. I was surprised that you are not more popular because you most certainly have the gift.

  15. agen dingdong togel live

    You’re so cool! I do not believe I’ve truly read a single thing like this before. So wonderful to discover somebody with genuine thoughts on this subject matter. Seriously.. thanks for starting this up. This web site is one thing that’s needed on the internet, someone with some originality!

  16. dindong togel

    Next time I read a blog, I hope that it doesn’t fail me just as much as this one. After all, Yes, it was my choice to read, but I really believed you would probably have something interesting to say. All I hear is a bunch of whining about something you could possibly fix if you were not too busy looking for attention.

  17. bandar qq

    An impressive share! I have just forwarded this onto a colleague who was doing a little research on this. And he actually bought me dinner due to the fact that I discovered it for him… lol. So allow me to reword this…. Thank YOU for the meal!! But yeah, thanx for spending time to talk about this matter here on your web site.

  18. agen judi online

    I’d like to thank you for the efforts you have put in penning this website. I’m hoping to check out the same high-grade blog posts by you in the future as well. In truth, your creative writing abilities has motivated me to get my own website now 😉

  19. dominoqq

    An interesting discussion is worth comment. I believe that you need to publish more about this subject matter, it might not be a taboo subject but typically people don’t talk about these issues. To the next! Kind regards!!

  20. keluaran hk

    I was very happy to uncover this page. I wanted to thank you for ones time just for this wonderful read!! I definitely liked every little bit of it and i also have you saved to fav to look at new things on your web site.

  21. keluaran hk

    I’m impressed, I must say. Seldom do I come across a blog that’s both equally educative and entertaining, and let me tell you, you have hit the nail on the head. The problem is something too few men and women are speaking intelligently about. Now i’m very happy that I found this in my hunt for something relating to this.

  22. bola88 link alternatif

    The very next time I read a blog, I hope that it doesn’t disappoint me as much as this one. I mean, I know it was my choice to read, nonetheless I truly believed you would probably have something helpful to talk about. All I hear is a bunch of whining about something you could possibly fix if you weren’t too busy looking for attention.

  23. pengeluaran king4d

    An impressive share! I have just forwarded this onto a co-worker who was doing a little homework on this. And he actually bought me breakfast due to the fact that I discovered it for him… lol. So let me reword this…. Thank YOU for the meal!! But yeah, thanks for spending some time to discuss this issue here on your internet site.

  24. 출장안마

    You completed various You completed various nice points there. I did a search on the subject matter and found mainly folks will agree with your blog.

Comments are closed.