Export data from Access to Excel

**NeoPa** · Sep 8 '11, 01:29 PM

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.) :

Code:

    Dim Fichier As String, ReportFolder As String
    Dim db As DAO.Database
    Dim xlObj As Excel.Application
    Dim fso As Scripting.FileSystemObject

    ReportFolder = "C:\TempFolder"
    Fichier = "\Scoring Template1.xlsx"
    Set xlObj = CreateObject("Excel.Application")
    Set fso = CreateObject("Scripting.FileSystemObject")
    Call fso.CopyFile(Application.CurrentProject.Path & "\scoring template.xlsx", ReportFolder & Fichier, True)
    xlObj.Workbooks.Open(ReportFolder & Fichier)
    ' script to fill in data
    Set db = CurrentDb
    With db.OpenRecordset("SELECT * FROM [CurrentMatchExcel]")
        Do While Not .EOF
            xlObj.Worksheets("Data").Range("A2:AJ2").Value = 
                Array(!Alias, !Class, !Time1, !Misses1, !Penalties1, !Bonus1, _
                      !Time2, !Misses2, !Penalties2, !Bonus2, !Time3, _
                      !Misses3, !Penalties3, !Bonus3, !Time4, !Misses4, _
                      !Penalties4, !Bonus4, !Time5, !Misses5, !Penalties5, _
                      !Bonus5, !Time6, !Misses6, !Penalties6, !Bonus6)
            Call CurMatch.MoveNext
        Loop
    End With
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.

**CD Tom** · Sep 8 '11, 01:44 PM

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,p enalties1 etc so I'm going to try using your code and breaking it at each line of times.

**CD Tom** · Sep 8 '11, 02:13 PM

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.

**NeoPa** · Sep 8 '11, 02:14 PM

Originally posted by CD Tom

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 misunderstandin g.

Originally posted by CD Tom

CD Tom:
but in the template there are total columns after each set of time1,misses1,p enalties1 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.Worksheet s("Data") reference.

Code:

    Dim shtWS As Excel.Worksheet
...
    Set shtWS = xlObj.Worksheets("Data")
    Set db = CurrentDB
...
            shtWS.Range("A2:F2").Value = _
                Array(...)
            shtWS.Range("H2:K2").Value = _
                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.

**CD Tom** · Sep 8 '11, 02:35 PM

I've changed the code to include the new Object

Code:

 Set shtWS = x1OBJ.Worksheets("data")

and I now get the subscript error on that line.

**NeoPa** · Sep 8 '11, 02:42 PM

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.

**NeoPa** · Sep 8 '11, 02:47 PM

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.

**CD Tom** · Sep 8 '11, 03:05 PM

I sorry for all the misunderstandin g. I've changed the code to reflect your suggestions.

Code:

        Dim fichier As String
        Dim CurMatch As DAO.Recordset
        reportfolder = "C:\TempFolder"
        fichier = "\Scoring Template1.xlsx"
        Dim xbj As Object
        Set xbj = CreateObject("excel.application")
        Dim fso As Object
        Dim shtWS As Excel.Worksheet
        Set fso = CreateObject("Scripting.FileSystemObject")
        fso.CopyFile Application.CurrentProject.path & "\scoring template.xlsx", reportfolder & fichier, True
        xbj.workbooks.Open reportfolder & fichier
        ' script to fill in data
        strsql = "Select * from CurrentMatchExcel"
        Set CurMatch = db.OpenRecordset(strsql)
        Set shtWS = xbj.Worksheets("Data")
        Do While Not CurMatch.EOF
            shtWS.Range("a2:G2").Value = Array(CurMatch("Alias"), CurMatch("Class"), CurMatch("Time1"), CurMatch("Misses1"), CurMatch("Penelties1"), CurMatch("Bonus1"))
            shtWS.Range("H2:M2").Value = Array(CurMatch("Time2"), CurMatch("Misses2"), CurMatch("Penelties2"), CurMatch("Bonus2"))
            shtWS.Range("O2:S2").Value = Array(CurMatch("Time3"), CurMatch("Misses3"), CurMatch("Penelties3"), CurMatch("Bonus3"))
            shtWS.Range("U2:Y2").Value = Array(CurMatch("Time4"), CurMatch("Misses4"), CurMatch("Penelties4"), CurMatch("Bonus4"))
            shtWS.Range("AA2:AE2").Value = Array(CurMatch("Time5"), CurMatch("Misses5"), CurMatch("Penelties5"), CurMatch("Bonus5"))
            shtWS.Range("AG2:AJ2").Value = Array(CurMatch("Time6"), CurMatch("Misses6"), CurMatch("Penelties6"), CurMatch("Bonus6"))
            Call CurMatch.MoveNext
        Loop

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.

**NeoPa** · Sep 8 '11, 03:13 PM

Originally posted by NeoPa

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.

**CD Tom** · Sep 8 '11, 03:20 PM

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.

**NeoPa** · Sep 8 '11, 04:07 PM

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("Da ta") is not available. From the fact that on line #11 you have :

Code:

xbj.workbooks.Open reportfolder & fichier

I can deduce that either :

You have, somewhere in your project, an item named workbooks with a lower-case 'w'.
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.

**CD Tom** · Sep 9 '11, 12:27 AM

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

Code:

        Dim Reportfolder As String, Fichier As String
        Dim CurMatch As DAO.Recordset
        Dim xbj As Excel.Application
        Dim fso As Scripting.FileSystemObject
        Reportfolder = "C:\sass premier"
        Fichier = "\Scoring Template1.xlsx"
        Set xbj = Excel.Application
        Set xbj = CreateObject("Excel.Application")
        Set fso = CreateObject("Scripting.FileSystemObject")
        Call fso.CopyFile(Application.CurrentProject.path & "\scoring template.xlsx", Reportfolder & Fichier, True)
        xbj.Workbooks.Open (Reportfolder & Fichier)
        xbj.Visible = True
        ' script to fill in data
        With db.OpenRecordset("select * from [currentmatchexcel]")
        Do While Not .EOF
            xbj.Range("A2:G2").Value = Array(!Alias, !Class, !Time1, !Misses1, !Penelties1, !MSafety1, !Bonus1)
            xbj.Range("I2:M2").Value = Array(!Time2, !Misses2, !Penelties2, !MSafety2, !Bonus2)
            xbj.Range("O2:S2").Value = Array(!Time3, !Misses3, !Penelties3, !MSafety3, !Bonus3)
            xbj.Range("U2:Y2").Value = Array(!Time4, !Misses4, !Penelties4, !MSafety4, !Bonus4)
            xbj.Range("AA2:AE2").Value = Array(!Time5, !Misses5, !Penelties5, !MSafety5, !Bonus5)
            xbj.Range("AG2:AJ2").Value = Array(!Time6, !Misses6, !Penelties6, !MSafety6, !Bonus6)
            Call CurMatch.MoveNext
        Loop
        End With

Now my problems are I want to increment the row by 1 for each row of the currentmatchexc el file, so the ("A2:G2') in row 16 will become ("A3:G3") also how do I move to the next row the Call CurMatch.movene xt 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.

**NeoPa** · Sep 9 '11, 01:43 AM

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.

**CD Tom** · Sep 9 '11, 02:10 AM

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:

Code:

        Dim Reportfolder As String, Fichier As String
        Dim xbj As Excel.Application
        Dim fso As Scripting.FileSystemObject
        Reportfolder = "C:\Tempfolder"
        Fichier = "\Scoring Template1.xlsx"
        Set xbj = CreateObject("Excel.Application")
        Set fso = CreateObject("Scripting.FileSystemObject")
        Call fso.CopyFile(Application.CurrentProject.path & "\scoring template.xlsx", Reportfolder & Fichier, True)
        xbj.Workbooks.Open (Reportfolder & Fichier)
        xbj.Visible = True
        ' script to fill in data
        With db.OpenRecordset("select * from [currentmatchexcel]")
            Do While Not .EOF
                xbj.Range("A2:G2").Value = Array(!Alias, !Class, !Time1, !Misses1, !Penelties1, !MSafety1, !Bonus1)
                xbj.Range("I2:M2").Value = Array(!Time2, !Misses2, !Penelties2, !MSafety2, !Bonus2)
                xbj.Range("O2:S2").Value = Array(!Time3, !Misses3, !Penelties3, !MSafety3, !Bonus3)
                xbj.Range("U2:Y2").Value = Array(!Time4, !Misses4, !Penelties4, !MSafety4, !Bonus4)
                xbj.Range("AA2:AE2").Value = Array(!Time5, !Misses5, !Penelties5, !MSafety5, !Bonus5)
                xbj.Range("AG2:AJ2").Value = Array(!Time6, !Misses6, !Penelties6, !MSafety6, !Bonus6)
                Call .MoveNext
            Loop
        End With

Now all I need is to manage the subsequent records and I'll be ready to go. Again thanks for all your help.

Export data from Access to Excel

Export data from Access to Excel

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment