473,322 Members | 1,314 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Export data from Access to Excel

489 256MB
I have a Excel template that I have some formulas in I want to export from a Access Query into this excel spread sheet. I found some code in this forum that I have modified to fit my spread sheet. When I run the process I get a "Subscript out of range" error 9
Expand|Select|Wrap|Line Numbers
  1. Dim fichier As String
  2. Dim CurMatch As DAO.Recordset
  3.  reportfolder = "C:\tempfolder"
  4.  fichier = "\Scoring Template1.xlsx"
  5.  Dim x1Obj As Object
  6.  Set x1Obj = CreateObject("excel.application")
  7.   Dim fso As Object
  8.   Set fso CreateObject("Scripting.FileSystemObject")
  9.   fso.CopyFile Application.CurrentProject.path & "\scoring template.xlsx", reportfolder & fichier, True
  10.   x1Obj.workbooks.Open reportfolder & fichier
  11. ' script to fill in data
  12.   strsql = "select * from currentmatchexcel"
  13.   Set CurMatch = db.OpenRecordset(strsql)
  14.    Do While Not CurMatch.EOF
  15.    x1Obj.sheets("Data").Range("a2").Value = CurMatch("alias")
  16.    xlObj.sheets("data").Range("B2").Value = CurMatch("Class")
  17.    x1Obj.sheets("data").Range("C2").Value = CurMatch("Time1")
  18.    x1Obj.sheets("data").Range("E2").Value = CurMatch("Misses1")
  19.    x1Obj.sheets("data").Range("F2").Value = CurMatch("Penelties1")
  20.    x1Obj.sheets("data").Range("G2").Value = CurMatch("Bonus1")
  21.    x1Obj.sheets("data").Range("I2").Value = CurMatch("Time2")
  22.    x1Obj.sheets("data").Range("J2").Value = CurMatch("Misses2")
  23.    x1Obj.sheets("data").Range("K2").Value = CurMatch("Penelties2")
  24.    x1Obj.sheets("data").Range("L2").Value = CurMatch("Bonus2")
  25.    x1Obj.sheets("data").Range("O2").Value = CurMatch("Time3")
  26.    x1Obj.sheets("data").Range("P2").Value = CurMatch("Misses3")
  27.    x1Obj.sheets("data").Range("Q2").Value = CurMatch("Penelties3")
  28.    x1Obj.sheets("data").Range("R2").Value = CurMatch("Bonus3")
  29.    x1Obj.sheets("data").Range("U2").Value = CurMatch("Time4")
  30.    x1Obj.sheets("data").Range("V2").Value = CurMatch("Misses4")
  31.    x1Obj.sheets("data").Range("W2").Value = CurMatch("Penelties4")
  32.    x1Obj.sheets("data").Range("X2").Value = CurMatch("Bonus4")
  33.    x1Obj.sheets("data").Range("AA2").Value = CurMatch("Time5")
  34.    x1Obj.sheets("data").Range("AB2").Value = CurMatch("Misses5")
  35.    x1Obj.sheets("data").Range("AC2").Value = CurMatch("Penelties5")
  36.    x1Obj.sheets("data").Range("AD2").Value = CurMatch("Bonus5")
  37.    x1Obj.sheets("data").Range("AG2").Value = CurMatch("Time6")
  38.    x1Obj.sheets("data").Range("AH2").Value = CurMatch("Misses6")
  39.    x1Obj.sheets("data").Range("AI2").Value = CurMatch("Penelties6")
  40.    x1Obj.sheets("data").Range("AJ2").Value = CurMatch("Bonus6")
  41.     CurMatch.MoveNext
  42.   Loop
  43. End If
  44.  
I get the error on the very first line when trying to load the data.
I plan on using some subscript to change the row but need to find out why I'm getting the subscript out of range error.
Any help would be appreciated.
Sep 8 '11 #1

✓ answered by CD Tom

I sorry for all the misunderstanding. I've changed the code to reflect your suggestions.
Expand|Select|Wrap|Line Numbers
  1.         Dim fichier As String
  2.         Dim CurMatch As DAO.Recordset
  3.         reportfolder = "C:\TempFolder"
  4.         fichier = "\Scoring Template1.xlsx"
  5.         Dim xbj As Object
  6.         Set xbj = CreateObject("excel.application")
  7.         Dim fso As Object
  8.         Dim shtWS As Excel.Worksheet
  9.         Set fso = CreateObject("Scripting.FileSystemObject")
  10.         fso.CopyFile Application.CurrentProject.path & "\scoring template.xlsx", reportfolder & fichier, True
  11.         xbj.workbooks.Open reportfolder & fichier
  12.         ' script to fill in data
  13.         strsql = "Select * from CurrentMatchExcel"
  14.         Set CurMatch = db.OpenRecordset(strsql)
  15.         Set shtWS = xbj.Worksheets("Data")
  16.         Do While Not CurMatch.EOF
  17.             shtWS.Range("a2:G2").Value = Array(CurMatch("Alias"), CurMatch("Class"), CurMatch("Time1"), CurMatch("Misses1"), CurMatch("Penelties1"), CurMatch("Bonus1"))
  18.             shtWS.Range("H2:M2").Value = Array(CurMatch("Time2"), CurMatch("Misses2"), CurMatch("Penelties2"), CurMatch("Bonus2"))
  19.             shtWS.Range("O2:S2").Value = Array(CurMatch("Time3"), CurMatch("Misses3"), CurMatch("Penelties3"), CurMatch("Bonus3"))
  20.             shtWS.Range("U2:Y2").Value = Array(CurMatch("Time4"), CurMatch("Misses4"), CurMatch("Penelties4"), CurMatch("Bonus4"))
  21.             shtWS.Range("AA2:AE2").Value = Array(CurMatch("Time5"), CurMatch("Misses5"), CurMatch("Penelties5"), CurMatch("Bonus5"))
  22.             shtWS.Range("AG2:AJ2").Value = Array(CurMatch("Time6"), CurMatch("Misses6"), CurMatch("Penelties6"), CurMatch("Bonus6"))
  23.             Call CurMatch.MoveNext
  24.         Loop
  25.  
the error subscript out of range now happens on line 15. I've changed the x1Obj to just xbj so as not to confuse letters and numbers. When I tried using the !Alias in the array I also got an error so I added the CurMatch("Alias") as noted on line 17.
The references I'm using are as follows"
Visual Basic For Applications
Microsoft Access 12.0 Object Library
OLE Automation
Microsoft ActiveX Data Objects 2.8 Library
Microsoft Office 12.0 Object Library
Microsoft Scripting Runtime
Microsoft Visual Basic for Applications Extensibility 5.3

I also put in
Microsoft Excel 12.0 Object Library
but that didn't make any difference.

20 4569
NeoPa
32,556 Expert Mod 16PB
I was working on this and noticed that half the references are to xlObj while the other half were to x1Obj. Now you needn't bother specifying properly which line you saw the error on. What you do need to do though (urgently, to avoid wasting any more of your, or our, time), is to read and digest When Posting (VBA or SQL) Code before posting again. I can guarantee you won't even have this problem in future if you do that.

While I was looking at the code anyway, I decided a little rewrite wouldn't hurt and can illustrate a cleaner way to approach this (Much of it is too broken to fix, as you'll see when you try to compile either version, but at least there are some ideas in here that should help.) :

Expand|Select|Wrap|Line Numbers
  1.     Dim Fichier As String, ReportFolder As String
  2.     Dim db As DAO.Database
  3.     Dim xlObj As Excel.Application
  4.     Dim fso As Scripting.FileSystemObject
  5.  
  6.     ReportFolder = "C:\TempFolder"
  7.     Fichier = "\Scoring Template1.xlsx"
  8.     Set xlObj = CreateObject("Excel.Application")
  9.     Set fso = CreateObject("Scripting.FileSystemObject")
  10.     Call fso.CopyFile(Application.CurrentProject.Path & "\scoring template.xlsx", ReportFolder & Fichier, True)
  11.     xlObj.Workbooks.Open(ReportFolder & Fichier)
  12.     ' script to fill in data
  13.     Set db = CurrentDb
  14.     With db.OpenRecordset("SELECT * FROM [CurrentMatchExcel]")
  15.         Do While Not .EOF
  16.             xlObj.Worksheets("Data").Range("A2:AJ2").Value = 
  17.                 Array(!Alias, !Class, !Time1, !Misses1, !Penalties1, !Bonus1, _
  18.                       !Time2, !Misses2, !Penalties2, !Bonus2, !Time3, _
  19.                       !Misses3, !Penalties3, !Bonus3, !Time4, !Misses4, _
  20.                       !Penalties4, !Bonus4, !Time5, !Misses5, !Penalties5, _
  21.                       !Bonus5, !Time6, !Misses6, !Penalties6, !Bonus6)
  22.             Call CurMatch.MoveNext
  23.         Loop
  24.     End With
  25. End If
As I say, this still won't compile properly, but it will be closer to it, and easier to fix, than your previous version.
Sep 8 '11 #2
CD Tom
489 256MB
I thought I mentioned that it happens on the very first line in the "fill in data" section. Also I like your idea about doing this in a array but in the template there are total columns after each set of time1,misses1,penalties1 etc so I'm going to try using your code and breaking it at each line of times.
Sep 8 '11 #3
CD Tom
489 256MB
I've also looked at each of the x1Obj and they are all the same. The first line of the "fill in Data" looks different but it's the same I tried to bold that line to show which line I received the error on. I've tried the code using the Array and still get the subscript out of range error.
Sep 8 '11 #4
NeoPa
32,556 Expert Mod 16PB
CD Tom:
I thought I mentioned that it happens on the very first line in the "fill in data" section.
That would have been more helpful if I'd known exactly where you understood the "fill in data" section to have started ;-) The main point I was trying to draw your attention to though, was that we have code displayed in boxes with line numbers on each line. It's just so much easier if you refer to the line of code by its number. That way there is no room for confusion or misunderstanding.

CD Tom:
but in the template there are total columns after each set of time1,misses1,penalties1 etc so I'm going to try using your code and breaking it at each line of times.
I'm sorry I missed that. Not too clever I must admit.

Your solution is sensible, but let me suggest something to help keep the code tidy under those circumstances :

Use a new Excel.Worksheet object so that you don't need to repeat the more complicated xlObj.Worksheets("Data") reference.
Expand|Select|Wrap|Line Numbers
  1.     Dim shtWS As Excel.Worksheet
  2. ...
  3.     Set shtWS = xlObj.Worksheets("Data")
  4.     Set db = CurrentDB
  5. ...
  6.             shtWS.Range("A2:F2").Value = _
  7.                 Array(...)
  8.             shtWS.Range("H2:K2").Value = _
  9.                 Array(...)
It's also possible to include the total formulas within the Array() call if you change the code to set one of the .FormulaX properties instead of .Value, but I'm guessing that would not be appropriate if you're dealing with a template. I only mention it to illustrate what flexibility is available.
Sep 8 '11 #5
CD Tom
489 256MB
I've changed the code to include the new Object
Expand|Select|Wrap|Line Numbers
  1.  Set shtWS = x1OBJ.Worksheets("data") 
and I now get the subscript error on that line.
Sep 8 '11 #6
NeoPa
32,556 Expert Mod 16PB
If you could post the full code of the routine and any definitions relevant to the code (I assume you have already followed all the instructions found in When Posting (VBA or SQL) Code - posted earlier), then I will see what I can do for you.

Also, if there are any References you use beyond the standard/default ones, then I will need those in order to check the usage of any code that uses them.
Sep 8 '11 #7
NeoPa
32,556 Expert Mod 16PB
Sorry. Unless I'm thoroughly mistaken you are talking about uncompiled code again (as it appears the name of the object used is x1Obj - with a number-1 rather than letter-l). If that had been compiled there would be no need for the question. Surely? Am I missing something here? I await your explanation.
Sep 8 '11 #8
CD Tom
489 256MB
I sorry for all the misunderstanding. I've changed the code to reflect your suggestions.
Expand|Select|Wrap|Line Numbers
  1.         Dim fichier As String
  2.         Dim CurMatch As DAO.Recordset
  3.         reportfolder = "C:\TempFolder"
  4.         fichier = "\Scoring Template1.xlsx"
  5.         Dim xbj As Object
  6.         Set xbj = CreateObject("excel.application")
  7.         Dim fso As Object
  8.         Dim shtWS As Excel.Worksheet
  9.         Set fso = CreateObject("Scripting.FileSystemObject")
  10.         fso.CopyFile Application.CurrentProject.path & "\scoring template.xlsx", reportfolder & fichier, True
  11.         xbj.workbooks.Open reportfolder & fichier
  12.         ' script to fill in data
  13.         strsql = "Select * from CurrentMatchExcel"
  14.         Set CurMatch = db.OpenRecordset(strsql)
  15.         Set shtWS = xbj.Worksheets("Data")
  16.         Do While Not CurMatch.EOF
  17.             shtWS.Range("a2:G2").Value = Array(CurMatch("Alias"), CurMatch("Class"), CurMatch("Time1"), CurMatch("Misses1"), CurMatch("Penelties1"), CurMatch("Bonus1"))
  18.             shtWS.Range("H2:M2").Value = Array(CurMatch("Time2"), CurMatch("Misses2"), CurMatch("Penelties2"), CurMatch("Bonus2"))
  19.             shtWS.Range("O2:S2").Value = Array(CurMatch("Time3"), CurMatch("Misses3"), CurMatch("Penelties3"), CurMatch("Bonus3"))
  20.             shtWS.Range("U2:Y2").Value = Array(CurMatch("Time4"), CurMatch("Misses4"), CurMatch("Penelties4"), CurMatch("Bonus4"))
  21.             shtWS.Range("AA2:AE2").Value = Array(CurMatch("Time5"), CurMatch("Misses5"), CurMatch("Penelties5"), CurMatch("Bonus5"))
  22.             shtWS.Range("AG2:AJ2").Value = Array(CurMatch("Time6"), CurMatch("Misses6"), CurMatch("Penelties6"), CurMatch("Bonus6"))
  23.             Call CurMatch.MoveNext
  24.         Loop
  25.  
the error subscript out of range now happens on line 15. I've changed the x1Obj to just xbj so as not to confuse letters and numbers. When I tried using the !Alias in the array I also got an error so I added the CurMatch("Alias") as noted on line 17.
The references I'm using are as follows"
Visual Basic For Applications
Microsoft Access 12.0 Object Library
OLE Automation
Microsoft ActiveX Data Objects 2.8 Library
Microsoft Office 12.0 Object Library
Microsoft Scripting Runtime
Microsoft Visual Basic for Applications Extensibility 5.3

I also put in
Microsoft Excel 12.0 Object Library
but that didn't make any difference.
Sep 8 '11 #9
NeoPa
32,556 Expert Mod 16PB
NeoPa:
I await your explanation.
I'm more interested in knowing whether or not you have been following the instructions in the linked article. It seems not, and until you assure me that you have, and will always in future, I'm not interested in dealing with your code at all. It's frankly a waste of my time to find things for you that you can find yourself with a tiny bit of effort. If/When I can rely on working with code that doesn't contain the most basic errors, that are easily found and fixed by following those instructions, then I'll be happy to continue. Otherwise, your last post provides everything I could expect.

I hope you appreciate how important an issue this is.
Sep 8 '11 #10
CD Tom
489 256MB
Yes I am following the article you posted. I do the compile and no errors happen, I have cut and pasted the code in. I do realize how important doing this is and thank you for all your help.
Sep 8 '11 #11
NeoPa
32,556 Expert Mod 16PB
That answer suits me. On we go. I would just like to confirm you have Option Explicit set in your module (That's also included in the article), but I will look at your code anyway following your last post.

Well, the first thing I notice is that this seems more like your original code than anything I suggested. Possibly you feel tidyness of code is simply an aesthetic issue. It's certainly that, but it's also a very powerful tool for minimising errors. Including Dim statements throughout your code instead of in a separate section at the top of a procedure is likely to make working with code more confusing and therefore easier in which to introduce errors. Dimming objects as Object, instead of the actual class you intend to use them for is denuding yourself of the extra help the development environment can give you. It's about so much more than making your code readable and understandable, but of course there's always that too.

As for your error on line #15, it seems that somehow .Worksheets("Data") is not available. From the fact that on line #11 you have :
Expand|Select|Wrap|Line Numbers
  1. xbj.workbooks.Open reportfolder & fichier
I can deduce that either :
  1. You have, somewhere in your project, an item named workbooks with a lower-case 'w'.
  2. This is not copied and pasted from your code window at all.
The chances are good that line #11 didn't open the file as you expected.

Before going any further I would investigate exactly what's going on. Debugging might be a good idea (See Debugging in VBA) but you'll need to be very careful when dealing with an controlled copy of Excel. It will start as invisible to the operator. See Application Automation for more on working with such things.
Sep 8 '11 #12
CD Tom
489 256MB
Ok, I've got this to work except for a couple of things that I need help with. First I'm not sure what I did differently but here's the code that works
Expand|Select|Wrap|Line Numbers
  1.         Dim Reportfolder As String, Fichier As String
  2.         Dim CurMatch As DAO.Recordset
  3.         Dim xbj As Excel.Application
  4.         Dim fso As Scripting.FileSystemObject
  5.         Reportfolder = "C:\sass premier"
  6.         Fichier = "\Scoring Template1.xlsx"
  7.         Set xbj = Excel.Application
  8.         Set xbj = CreateObject("Excel.Application")
  9.         Set fso = CreateObject("Scripting.FileSystemObject")
  10.         Call fso.CopyFile(Application.CurrentProject.path & "\scoring template.xlsx", Reportfolder & Fichier, True)
  11.         xbj.Workbooks.Open (Reportfolder & Fichier)
  12.         xbj.Visible = True
  13.         ' script to fill in data
  14.         With db.OpenRecordset("select * from [currentmatchexcel]")
  15.         Do While Not .EOF
  16.             xbj.Range("A2:G2").Value = Array(!Alias, !Class, !Time1, !Misses1, !Penelties1, !MSafety1, !Bonus1)
  17.             xbj.Range("I2:M2").Value = Array(!Time2, !Misses2, !Penelties2, !MSafety2, !Bonus2)
  18.             xbj.Range("O2:S2").Value = Array(!Time3, !Misses3, !Penelties3, !MSafety3, !Bonus3)
  19.             xbj.Range("U2:Y2").Value = Array(!Time4, !Misses4, !Penelties4, !MSafety4, !Bonus4)
  20.             xbj.Range("AA2:AE2").Value = Array(!Time5, !Misses5, !Penelties5, !MSafety5, !Bonus5)
  21.             xbj.Range("AG2:AJ2").Value = Array(!Time6, !Misses6, !Penelties6, !MSafety6, !Bonus6)
  22.             Call CurMatch.MoveNext
  23.         Loop
  24.         End With
  25.  
Now my problems are I want to increment the row by 1 for each row of the currentmatchexcel file, so the ("A2:G2') in row 16 will become ("A3:G3") also how do I move to the next row the Call CurMatch.movenext in row 22 doesn't work and I know it's because there is no CurMatch set. Most of this code is very new to me. I have ran the compile and no errors, this is a copy of the code in the program.
I hope I did everything right this time. With all your help I'm sure I'll finally learn.
Thanks for your help.
Sep 9 '11 #13
NeoPa
32,556 Expert Mod 16PB
It certainly looks a lot better now I must say. It's also much easier to read and follow of course. You seem to be making good progress, but I just want to repeat my question again (This is not tautology but a repeat of the repetition.) - Do you have Option Explicit set in your code? I ask because I see indications that it is not there (The code as you've posted it should not work at all as it's missing some important lines that need to be there for the rest even to execute). We can still continue but please, answer the question in your next post by confirming or denying that Option Explicit is a line at the top of this module.

I'm surprised line #7 compiled. That's not required and I'm not even sure what it would mean. If it compiles that would indicate is does something, but nothing you need for sure.

Lines #15 through #23, the Do Loop code effected by the With statement in line #14, should be indented. This makes it clearer which code is, and which isn't, effected by the With statement.

Line #2 shouldn't exist. It's a hang-over from the earlier version of the code. That is now handled by the With of line #14. The reference you still have to CurMatch on line #22 should not be there. It makes no logical sense as CurMatch is never even set. It should read : Call .MoveNext

The variable db appears to be neither defined nor set in this code. If you refer back to lines #2 & #13 in the code example I posted in post #2 you'll see how it should be defined and set. Without these lines line #14 cannot possibly execute, or even compile (Assuming the Option Explicit line is present as it should be).

That's enough for one post. Let's get these outstanding issues sorted out then we can get on to handling the changes required for this code to manage subsequent records in succeeding rows of the worksheet.
Sep 9 '11 #14
CD Tom
489 256MB
Ok I've fixed what you suggested and yes there is an Option Explicit up at the very top to the routine, also the dim db as database is also at the very top of the routine. I did the compile and no errors the copy paste of the new code is as follows:
Expand|Select|Wrap|Line Numbers
  1.         Dim Reportfolder As String, Fichier As String
  2.         Dim xbj As Excel.Application
  3.         Dim fso As Scripting.FileSystemObject
  4.         Reportfolder = "C:\Tempfolder"
  5.         Fichier = "\Scoring Template1.xlsx"
  6.         Set xbj = CreateObject("Excel.Application")
  7.         Set fso = CreateObject("Scripting.FileSystemObject")
  8.         Call fso.CopyFile(Application.CurrentProject.path & "\scoring template.xlsx", Reportfolder & Fichier, True)
  9.         xbj.Workbooks.Open (Reportfolder & Fichier)
  10.         xbj.Visible = True
  11.         ' script to fill in data
  12.         With db.OpenRecordset("select * from [currentmatchexcel]")
  13.             Do While Not .EOF
  14.                 xbj.Range("A2:G2").Value = Array(!Alias, !Class, !Time1, !Misses1, !Penelties1, !MSafety1, !Bonus1)
  15.                 xbj.Range("I2:M2").Value = Array(!Time2, !Misses2, !Penelties2, !MSafety2, !Bonus2)
  16.                 xbj.Range("O2:S2").Value = Array(!Time3, !Misses3, !Penelties3, !MSafety3, !Bonus3)
  17.                 xbj.Range("U2:Y2").Value = Array(!Time4, !Misses4, !Penelties4, !MSafety4, !Bonus4)
  18.                 xbj.Range("AA2:AE2").Value = Array(!Time5, !Misses5, !Penelties5, !MSafety5, !Bonus5)
  19.                 xbj.Range("AG2:AJ2").Value = Array(!Time6, !Misses6, !Penelties6, !MSafety6, !Bonus6)
  20.                 Call .MoveNext
  21.             Loop
  22.         End With
  23.  
Now all I need is to manage the subsequent records and I'll be ready to go. Again thanks for all your help.
Sep 9 '11 #15
NeoPa
32,556 Expert Mod 16PB
Try this. I've identified the changes I've made by putting them in italics. It uses a function to determine the full specification of the Range object which it returns :
Expand|Select|Wrap|Line Numbers
  1.     Dim Reportfolder As String, Fichier As String
  2.     Dim lngRow As Long
  3.     Dim xlApp As Excel.Application
  4.     Dim fso As Scripting.FileSystemObject
  5.  
  6.     Reportfolder = "C:\Tempfolder"
  7.     Fichier = "\Scoring Template1.xlsx"
  8.     Set fso = CreateObject("Scripting.FileSystemObject")
  9.     Call fso.CopyFile(Application.CurrentProject.path & "\scoring template.xlsx", Reportfolder & Fichier, True)
  10.     Set xlApp = CreateObject("Excel.Application")
  11.     xlApp.Workbooks.Open (Reportfolder & Fichier)
  12.     xlApp.Visible = True
  13.     ' script to fill in data
  14.     With db.OpenRecordset("SELECT * FROM [CurrentMatchExcel]")
  15.         For lngRow = 2 To &H7FFFFFFF
  16.             If .EOF Then Exit For
  17.             GetRange(xlApp, "A:G", lngRow).Value = _
  18.                 Array(!Alias, !Class, !Time1, !Misses1, !Penelties1, !MSafety1, !Bonus1)
  19.             GetRange(xlApp, "I:M", lngRow).Value = _
  20.                 Array(!Time2, !Misses2, !Penelties2, !MSafety2, !Bonus2)
  21.             GetRange(xlApp, "O:S", lngRow).Value = _
  22.                 Array(!Time3, !Misses3, !Penelties3, !MSafety3, !Bonus3)
  23.             GetRange(xlApp, "U:Y", lngRow).Value = _
  24.                 Array(!Time4, !Misses4, !Penelties4, !MSafety4, !Bonus4)
  25.             GetRange(xlApp, "AA:AE", lngRow).Value = _
  26.                 Array(!Time5, !Misses5, !Penelties5, !MSafety5, !Bonus5)
  27.             GetRange(xlApp, "AG:AJ", lngRow).Value = _
  28.                 Array(!Time6, !Misses6, !Penelties6, !MSafety6, !Bonus6)
  29.             Call .MoveNext
  30.         Next lngRow
  31.     End With
The extra function GetRange() is defined as :
Expand|Select|Wrap|Line Numbers
  1. Private Function GetRange(xlApp As Excel.Application, _
  2.                           strRan As String, _
  3.                           lngRow As Long) As Excel.Range
  4.     Set GetRange = xlApp.Range(Replace(strRan, ":", lngRow & ":") & lngRow)
  5. End Function
Sep 9 '11 #16
CD Tom
489 256MB
This looks great, I'm trying to follow it through but then I get to line 17 I get a type mismatch and don't know why because I'm not sure how everything works. Also does the extra function GetRange() go in the same control?
I feel real stupid when I see the knowledge you have about this Access.
Thanks for your patience and help.
Sep 9 '11 #17
CD Tom
489 256MB
By the way I did copy and paste your code into my routine and ran the compile and got no errors.
Sep 9 '11 #18
NeoPa
32,556 Expert Mod 16PB
Rather than reposting the code in here, I've updated the earlier post (#16) to reflect the changes required, which I'll clarify below.

Line #17 doesn't make much sense because I got it wrong. It's an easy enough fix, but illustrates the importance of sensible names for objects. To avoid this problem in future I've renamed xbj in your code to a more apposite xlApp. I've also changed the code in the function to handle an Application object instead of the Worksheet object I thought I was dealing with.

I also moved the current line #10 from where it was before to be with the other code which references xlApp. This is just tidier.

I changed the case of the SQL string as SQL commands are written, as standard, in upper case. Again it makes no difference to the execution, but provides clues to a developer of what they're working with.

PS. This won't effect the project directly, but are you aware that penalty is spelled with a single 'e'? It would make sense to change your field names and all references to them to reflect this.
Sep 9 '11 #19
CD Tom
489 256MB
Worked perfect, thank you, thank you. I know I can sometime be dense but your understanding is greatly appreciated.
Sep 9 '11 #20
NeoPa
32,556 Expert Mod 16PB
Good to hear it.

It was a bit of a struggle I suppose, but I'm sure it will stand you in good stead for future questions. Things can go quite smoothly when you follow the instructions.

That sounds like I'm having a go, but what I'm really trying to say is that now you've been through this, you can expect things to go more smoothly in future.
Sep 9 '11 #21

Sign in to post your reply or Sign up for a free account.

Similar topics

0
by: Funbeat | last post by:
Hi everybody, I'm facing with the following problem (bug ?) : A page is calling another one (export.aspx) for exporting data to excel. The tecnhique used is to create a Excel-MIME stream...
5
by: Jonny | last post by:
Hello, I have created a button on my form which when pressed does the following : 1) Run a pre-defined Macro, which is picking up a query and running my data to excel. However, I need the...
4
by: Agnes | last post by:
I can export the data to excel, but it is really really slow. need 5-6 mins to export 30 fields (a hundred records) . for my old vfp application, less than 3 minutes. for 500-800 records. Does any...
1
by: C | last post by:
Hi, Does anyone know of any sites that have sample code that shows how to export data to Excel using VS Tools for Office? What is the advantage of using VS Tools for Office over uisng Interop?...
5
by: bimalkumar | last post by:
hi 2 all, i m using some txt boxes and few combo box in my form. how do i export the txt box and combobox data to excel sheet directly without using any database.just get the data from txt...
7
by: Vanessa | last post by:
hi Everyone, I have two questions on exporting data to Excel using ASP (w/o converting formatted excel file into web page and then plug in the dynamic data): 1. Can we export data into...
1
by: forumaic | last post by:
Hello, I am trying to export data to excel from datagrid, and I am getting an error: "The Controls collection cannot be modified because the control contains code blocks (i.e. <% ... %>)." ...
2
by: hal | last post by:
Hello all, I've been searching all day for an article or tutorial on how to get data from a SQL Server 2000 database and export the data to excel 2003 so that multiple worksheets are created,...
2
by: teneesh | last post by:
I have the following query behind a button in Access. But I'd like for this button, after running the query to export this data to excel. When I look this up online, I find nothing, not sure if I'm...
2
by: kashif73 | last post by:
Hi, I want to export data from 4 Access tables to Excel using VBA. The user selects 2 different DATES from the form & based on DATEs criteria all record from 4 tables should be extracted to...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.