Results 1 to 9 of 9

Thread: Error 1004 - Application-defined or object-defined error VBA Excel

  1. #1

    Thread Starter
    Fanatic Member
    Join Date
    Mar 2013
    Location
    Minneapolis, MN
    Posts
    531

    Error 1004 - Application-defined or object-defined error VBA Excel

    This is killing me! Error 1004 - Application-defined or object-defined error.

    I have looked up several references, none seem to work. I would like to not use Range.

    Code:
        Dim wb As Workbook
        Set wb = ActiveWorkbook
        
        Dim i As Integer
        i = 1
            
        Do While Not rs.EOF
            Sheet1.Cells(i, 0).Value = (rs.Fields("JobNo"))
            Sheet1.Cells(i, 1).Value = (rs.Fields("ShipTo"))
            Sheet1.Cells(i, 2).Value = (rs.Fields("Product"))
            Sheet1.Cells(i, 3).Value = (rs.Fields("Detail"))
            Sheet1.Cells(i, 4).Value = (rs.Fields("Feet"))
            
            rs.MoveNext
            i = i + 1
    
        Loop
    Thanks!
    - A 'Hyperactive Member' trying to make a difference in a hyperactive world! And recently, I've been promoted to a 'Finatic Member,' whatever that means!

  2. #2

    Thread Starter
    Fanatic Member
    Join Date
    Mar 2013
    Location
    Minneapolis, MN
    Posts
    531

    Re: Error 1004 - Application-defined or object-defined error VBA Excel

    This must be clearly rocket science. How to put cells from a database (verified reading correctly) into Excel fields???

    What am I missing?

    Code:
    Public cn As ADODB.Connection
    Public rs As ADODB.Recordset
    
    Sub GetJobs_Click()
        Dim connectionString As String
        ' connectionString = "Provider=sqloledb;Data Source=w2012;Initial Catalog=M1_SU;User Id=m1view;Password=connect2m1;"
        connectionString = "Provider=sqloledb;Data Source=sage\SQLEXPRESS;Initial Catalog=JobSheet;Integrated Security=SSPI;Persist Security Info=True;"
    
        Set cn = New ADODB.Connection
        Set rs = New ADODB.Recordset
            
        Dim sql As String
        sql = "SELECT * From [JobSheet].[dbo].[JobInfo] ORDER BY JobNo "
            
        cn.connectionString = connectionString
        
        cn.Open (connectionString)
            
        rs.Open sql, cn, adOpenKeyset, adLockReadOnly, adCmdText
        rs.MoveFirst
        
        Dim i As Integer
        i = 1
            
        Do While Not rs.EOF
            ThisWorkbook.ActiveSheet.Cells(i, 0).Value = (rs.Fields("JobNo"))
            ThisWorkbook.ActiveSheet.Cells(i, 1).Value = (rs.Fields("ShipTo"))
            ThisWorkbook.ActiveSheet.Cells(i, 2).Value = (rs.Fields("Product"))
            ThisWorkbook.ActiveSheet.Cells(i, 3).Value = (rs.Fields("Detail"))
            ThisWorkbook.ActiveSheet.Cells(i, 4).Value = (rs.Fields("Feet"))
            
            rs.MoveNext
            i = i + 1
    
        Loop
    
        rs.Close
        Set rs = Nothing
            
        cn.Close
        Set cn = Nothing
    
    End Sub
    - A 'Hyperactive Member' trying to make a difference in a hyperactive world! And recently, I've been promoted to a 'Finatic Member,' whatever that means!

  3. #3
    PowerPoster
    Join Date
    Nov 2017
    Posts
    3,116

    Re: Error 1004 - Application-defined or object-defined error VBA Excel

    There is no row 0 or column 0. It's not like an array in that sense. Rows start with row 1, columns start with column 1.

    Good luck.

  4. #4

    Thread Starter
    Fanatic Member
    Join Date
    Mar 2013
    Location
    Minneapolis, MN
    Posts
    531

    Re: Error 1004 - Application-defined or object-defined error VBA Excel

    Bingo! Thank you so much!!!
    - A 'Hyperactive Member' trying to make a difference in a hyperactive world! And recently, I've been promoted to a 'Finatic Member,' whatever that means!

  5. #5
    New Member Doc.AElstein's Avatar
    Join Date
    Sep 2020
    Location
    Hof, Bavaria, Germany
    Posts
    5

    Re: Error 1004 - Application-defined or object-defined error VBA Excel

    Hi.
    The problem is solved here, but I am just adding some stuff, out of passing interest…

    I am not 100% sure about this, but I think Excel finds that problem giving Cells(i, 0) syntactically OK, ref!!.
    But it can’t find a range object in row 1 that is one column to the left of column A in a worksheet ref!!
    I think it is that problem that chucks up the error.

    A bit more info, just out of interest… _
    _... I am considering two different things which it is very easy to get mixed up
    _... Excel referencing spreadsheet cells, which is mostly a “relative referencing thing” of one sort or another, even if it might not appear to be at first glance, ref!!
    _ … How Excel takes arrays from ranges and pastes arrays into ranges


    First, a quick review of Excel spreadsheets and its conventional doings with arrays…
    Excel, when doing interactions with spreadsheet ranges and arrays, has a general convention of considering spreadsheet range values in a 2 D array, the first dimension refers typically to the horizontal row, and the second dimension refers typically to the vertical column. ( Conventionally/ usually the first row and first column is taken as indicia 1, (it does not necessarily always have to be when talking about the arrays associated with a 2 D range Area ) ).
    So pseudo like
    Arr(row,column) = {1 To n1, 1 To n2}
    As OptionBase1 said, conventionally the first row and first column is taken as indicia 1. Note this is when referencing cells in an entire spreadsheet- There is a small subtlety there ref!!. We are talking there about referencing spreadsheet cells, which is actually a relative thing as explained in ref!!

    **Also you need to be a bit careful when taking in or pasting out an array into a spreadsheet since its handled a bit differently as regard indicia:
    Excel will conventionally make an array filled from a spreadsheet as starting at first and second dimension ( pseudo “row” and “column” ) indicia of 1 , regardless of where the range is.
    The other way around is similar , but a bit different. You can, if you want, have indicia starting at other than 1, but it doesn’t matter where your indicia start, as regards pasting out: If you paste your 2D array out into a spreadsheet, it starts always at top left of the range where you paste it to: Top left is top left regardless of what you have chosen to be the start indicia of the 2D array that you are pasting out.

    Now, .. How a spreadsheet handles 1 D arrays / relevance to this Thread
    _..It seems that a lot of computer things in general that give out arrays of 1 dimension, often have their first integer indicia thing set at 0, at least by default, ref##
    I suppose technically speaking a 1 D array has no orientation, but some of us often think conveniently in our human minds of it as looking “horizontal” or like in a row. Excel goes along with that way of thinking sometimes, and in many things to do with a spreadsheet , if you pass a 1 D array, (even one with a first indicia thing set at 0 or any other value ) , then it does the same as it would if you gave it a 2 D array where the first dimension had a dimension of 1, ( Note: I said dimension of 1 there, not indicia ) . Saying the same thing in more simple, (not necessarily perfectly technically correct ) terms: In spreadsheet interactions, Excel will often take a 1 D array as if it was a 2 D , 1 “row” array, or 2D 1 “horizontal” array,
    Pseudo {0 to n2} taken as if it were {1 To 1, 1 To n2+1},
    or
    pseudo {0 to n2} taken as if it were {7 To 7, 1 To n2+1}
    or
    pseudo {0 to n2} taken as if it were {9 To 9, 65 To n2+65}
    or
    pseudo {0 to n2} taken as if it were {8 To 8, 0 To n2 }
    etc.. etc..
    ( using n2 rather than n is just a personal convention of mine. It helps me keep my brain orientated in the perception that its referring to something to do with a column number in a row, and it either is associated with , or is pseudo equivalent to, a second dimension in a 2D array. That thinking is all technically dodgy, I know, but thinking that way usually gets you by, when dealing with the things I am rambling on about )
    ( Unfortunately Excel VBA has some String functions that came from VB which insist on a 1 D array: That can be a Bummer – since those functions wont take any of those 2 D , 1 “row” arrays instead )

    Coming back to the relevance to the OPs issue, or similar issues…

    In simple terms, if you had a 1 D array with values in it, ( even one starting at indicia 0 ), which you wanted to paste as a row in consecutive cells into a spreadsheet range, then you can do it directly in a single code line, pseudo like

    ___MyRnge = My1DArray()

    Instinct might tell you that the last code line sounds a bit dodgy. Maybe it should be, but VBA has seemingly just been written such that it conveniently usually does what you want without any problems, in such a case!
    The code snippet below perhaps explains it a bit better, if you run it from the VB Editor in step ( key F8 ) mode .
    Code:
    '   https://www.vbforums.com/showthread.php?889021-Error-1004-Application-defined-or-object-defined-error-VBA-Excel
    Sub OneD_TwoD_Arrays()
    Rem 0 worksheets info, use arbritrary worksheet for demos..
    Dim Ws As Worksheet: Set Ws = ActiveSheet  '   You don't have to do this. You can forget it and miss out all the  Ws.  bits   But that can catch you out sometimes, when you might end up referrencing a range in a different worksheet to that which you thought you were
    Rem 1 make arbritrary  1 D array
    '1_a) long way
    Dim arr1D_a(0 To 4) As String
     Let arr1D_a(0) = "BigJob": arr1D_a(1) = "Baveria": arr1D_a(2) = "Computer": arr1D_a(3) = "OldOne": arr1D_a(4) = "42"
    '1_b) quick way using  VBA Array function ,    https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/array-function
    Dim arr1D_b() As Variant      ' The array needs to be dynamic and needs to have  Variant  type, because....._   _.. the next code line function  returns  a field of elements   which are housed in  elements of Variant type ,  so the type must be  Variant  regardles of what types are withing the elements or we will get type mismatch errors , and  _.. the working of the function is such that it sends infomation to size the thing taking it: If we have a fixed size array as in  _b  then Excel will error becuse Excel will insist on wanting to do this sizing of any recieving array, which it can't do if the array is fixed size.
     Let arr1D_b() = Array("BigJob", "Baveria", "Computer", "OldOne", "42") '   This returns us a 5 element array starting at indicia  0     So we have now  arr1D1_b(0) = "BigJob": arr1D_b(1) = "Baveria": arr1D_b(2) = "Computer": arr1D_b(3) = "OldOne": arr1D_b(4) = "42"
    Rem 2 paste arrays into spreadsheet
     Ws.Cells.ClearContents
     
     Let Ws.Range("C1:G1").Value = arr1D_a()      ' Top left of where the 5 column, single row is pasted is cell  C1
     Let Ws.Range("D2:H2").Value = arr1D_b()      ' Top left of where the 5 column, single row is pasted is cell  D2
     
    Rem 3 Excel Spreadsheet array interaction:
    '3a) usually Excel makes a 2D array from a range with indicia starting at (1, 1)
    Dim arr2D1Row_a() As Variant   '  In the next code line, I am uisng the  .Value  property applied to a multi cell rectangular single area range. When i do that,  .Value  is programmed to return me an array of values which as far as it knows might be number values or text values,  or formulas or blanks or vb errors.  To acccomadte the possibility of us distinguisching later what type we have, all elelments in the returned array are  Variant  because only  Variant  can  "hold"  all the different types in it. (a number can go in a number type variable, (Single  Double  Long  Integer) or a  Variant  type variable; a text can go in a  String  type or a  Variant  type etc. etc).    So, because Excel is programmed to return an array of Variant type Elements, I must decalre my array, arr2D1Row_a() , as variant types, or else i will get a type mismatch error at the next code line
     Let arr2D1Row_a() = Ws.Range("D2:H2").Value  '  Excel decides the array type in this situation. It will always be a 2D 1 "row" array  {1 To 1, 1 To 5}  Nothing you can do will influence this
    '3b) When pasting out, top left is always top left of the range you specify, Excel doesn't care what indicia you've got in the array being pasted out
     Let Ws.Range("E3:I3").Value = arr2D1Row_a()  ' Top left of where the 5 column, single row is pasted is cell  E3
    Dim arr2D1Row_b(7 To 7, 42 To 47) As String   '  A 2D 1 "row" array with arbritrary inidia: I can decide this but ...._ Excel doesn't care what indicia you have chosen when pasting out
     Let arr2D1Row_b(7, 42) = "BigJob": arr2D1Row_b(7, 43) = "Baveria": arr2D1Row_b(7, 44) = "Computer": arr2D1Row_b(7, 45) = "OldOne": arr2D1Row_b(7, 46) = "42"
     Let Ws.Range("F4:J4").Value = arr2D1Row_b()  '  Top left is always top left, cell F4 in this case.  ...._ Excel doesn't care what indicia you have chosen when pasting out
    End Sub


    I suppose the bottom lines are
    It seems generally that cell referencing in Excel and Excel VBA is based on relative references, even when it might not appear to be at first glance.
    Arrays on the other hand have a fixed indicia to refer specifically to specific elements in the array, but as far as Excel is concerned they are “floating around”, orientation-less, and what I tried to do here is explain a bit about how Excel lets you put them into or take out of a spreadsheet in one go without looping, which can be very useful in making coding more efficient
    ( Couple that knowledge with a good understanding of how the Excel and Excel VBA Index function can work in manipulating arrays in its extended form of having row and column arguments as arrays, …_
    __Index( Arr() , Rws(), Clms() )
    _... then you can write some coding that knocks the socks off some other array/ spreadsheet manipulating coding in VBA
    )

    Alan





    ref## In VBA, you can change the typical start indicia of 1 dimensional producing things from the default 0 to 1
    One way to do that is to write the Username of a certain VB forums member at the top of your code module like this
    Code:
     Option Base 1
    In the case of the macro example above, that will make no change what so ever to the results.
    There is a small change to the workings: That change is in the code line that uses the VBA Array function,
    arr1D_b() = Array("BigJob", "Baveria", "Computer", "OldOne", "42")
    Previously it gave me this:
    arr1D1_b(0) = "BigJob": arr1D_b(1) = "Baveria": arr1D_b(2) = "Computer": arr1D_b(3) = "OldOne": arr1D_b(4) = "42"
    https://i.imgur.com/5e151Tf.jpg
    Name:  arr1D_b()OptionBase0.jpg
Views: 1048
Size:  53.5 KB

    Now it gives me this:
    arr1D1_b(1) = "BigJob": arr1D_b(2) = "Baveria": arr1D_b(3) = "Computer": arr1D_b(4) = "OldOne": arr1D_b(5) = "42"
    https://i.imgur.com/SnM2jm2.jpg
    Name:  arr1D_b()OptionBase1.jpg
Views: 1107
Size:  44.5 KB

    ( I get that Watch Window if I highlight any of the arr1D_b() bits of thee coding and then hit keys Shift+F9.
    Having done that, then after I have stepped past the code line that fills that array, the contents will be shown in that Watch Window after hitting that + symbol
    )

    **There is no effect on any of the pasting out done in the macro, because top left is always top left of the range you specify, and Excel doesn’t care what indicia you’ve got in the array being pasted out, ( be it a 1 D array , or even a wildly indicied 2 D array, as in the last example in the macro )





    ref !! It is a bit more complicated if you are referencing a range somewhere in a spreadsheet other than at the origin, cell A1, since then you can “go backwards from the origin” of the range you are at. Like
    Range("B10").Cells(0, 0) is like Range("A9")
    or
    Range("B10").Cells(-1, 0) is like Range("A8").
    Something like,
    Range("B10").Cells(0, -1)
    will , in this example, give you your Error 1004 - Application-defined or object-defined error. In this example its because you are trying to go 2 columns back from column B
    ( You cant really do something like Cells(4, 5) in VBA. Well you can , I know, but that is just because VBA is kind and puts in the bit you missed. Effectively its doing something pseudo like
    Worksheets(“MySheet”).Cells(4, 5)
    So its giving you a cell that is at (4, 5) relative to the first cell in a worksheet, in such a case. Cells is a property that must be applied to a range object. If you forget to give it one, Excel takes a guess at what you wanted to apply it to
    And if you want to get really picky, we usually miss something else out. Cells has a few properties and other stuff. We are using the Item Property. We should do this:
    Worksheets(“MySheet”).Cells.Item(4, 5)
    Once again, VBA is kind, in this case, and assumes that’s what we meant
    )


    It seems generally that cell referencing in Excel and Excel VBA is based on relative references, even when it might not appear to be at first glance.
    Arrays on the other hand have a fixed indicia to refer specifically to specific elements in the array, but as far as Excel is concerned they are “floating around”, and what I tried to do here is explain a bit about how Excel lets you put them into or take out of a spreadsheet in one go without looping, which can be very useful in making coding more efficient
    Attached Files Attached Files
    Last edited by Doc.AElstein; Oct 24th, 2020 at 07:27 AM.

  6. #6
    New Member Doc.AElstein's Avatar
    Join Date
    Sep 2020
    Location
    Hof, Bavaria, Germany
    Posts
    5

    Re: Error 1004 - Application-defined or object-defined error VBA Excel

    The last post was a bit general. Maybe I could add a short more specific application of the ideas to the OPs macro..
    This is the bit to concentrate on
    Code:
        Do While Not rs.EOF
            ThisWorkbook.ActiveSheet.Cells(i, 0).Value = (rs.Fields("JobNo"))
            ThisWorkbook.ActiveSheet.Cells(i, 1).Value = (rs.Fields("ShipTo"))
            ThisWorkbook.ActiveSheet.Cells(i, 2).Value = (rs.Fields("Product"))
            ThisWorkbook.ActiveSheet.Cells(i, 3).Value = (rs.Fields("Detail"))
            ThisWorkbook.ActiveSheet.Cells(i, 4).Value = (rs.Fields("Feet"))
            
            rs.MoveNext
            i = i + 1
    
        Loop
    
    We could do this
    Code:
    Dim arrTemp() As Variant
        Do While Not rs.EOF
         Let arrTemp() = Array(rs.Fields("JobNo"), rs.Fields("ShipTo"), rs.Fields("Product"), rs.Fields("Detail"), rs.Fields("Feet"))
         Let ThisWorkbook.ActiveSheet.Range("A" & i & ":E" & i & "").Value = arrTemp()
          rs.MoveNext
         Let i = i + 1
    
        Loop
    Or this,
    Code:
    Dim arrTemp() As Variant
        Do While Not rs.EOF
         Let arrTemp() = Array(rs.Fields("JobNo"), rs.Fields("ShipTo"), rs.Fields("Product"), rs.Fields("Detail"), rs.Fields("Feet"))
            With ThisWorkbook.ActiveSheet
             .Range("A" & i & ":E" & i & "").Value = arrTemp()
            End With
            rs.MoveNext
            i = i + 1
    
        Loop
    Often in VBA we “cut out the middle man”
    In this case that would mean directly assigning the array after its made to the worksheet row. So finally we would have this
    Code:
        Do While Not rs.EOF
         Let ThisWorkbook.ActiveSheet.Range("A" & i & ":E" & i & "").Value = Array(rs.Fields("JobNo"), rs.Fields("ShipTo"), rs.Fields("Product"), rs.Fields("Detail"), rs.Fields("Feet"))
          rs.MoveNext
         Let i = i + 1
    
        Loop
    ( I do note that the OP said he did not want to use Range, but I am not quite sure what he meant by that.. )

    I am not too clued up on ADODB stuff, but possibly there might be a way to get out a row array, in which case the thing would be a bit simplified further


    Alan
    Last edited by Doc.AElstein; Oct 24th, 2020 at 03:04 PM.

  7. #7
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Error 1004 - Application-defined or object-defined error VBA Excel

    what about excels copyfromrecordset method?
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  8. #8
    New Member Doc.AElstein's Avatar
    Join Date
    Sep 2020
    Location
    Hof, Bavaria, Germany
    Posts
    5

    Re: Error 1004 - Application-defined or object-defined error VBA Excel

    I have never used that copyfromrecordset thing, Pete. I really don’t know too much about anything ADO, ADODB
    If I understand a quick look at the documentation, ( https://docs.microsoft.com/en-us/off...yfromrecordset ) , that copyfromrecordset thing seems to let you paste out all your “data” in one go to a spreadsheet.
    I assume the “data” is something that crudely to the uneducated mind like mine is something that very approximately could be considered as
    Record = approximately rows
    Field = approximately columns
    Recordset = approximately a set of rows = approx like 2D array of data = approx like spreadsheet range Area of data

    So I will take a guess that what that copyfromrecordset thing does, is
    _a) somehow convert that “data” ( using ADO or god knows what ) , into something it and/ or VBA recognises as a 2D VBA array, say, arrFromRecordset()
    _b) it works out the range size for you, so that you only have to pass it top left of where you want it.

    I guess then it effectively does something like this: ( This is all pseudo like )

    Ws.Range( Ws.Cells(1, 1) , Ws.Cells(rs.RecordsRows.Count, rs.Fields.Count) ) = arrFromRecordset ()

    ( in this example, I assume you gave the copyfromrecordset thing a top left of A1. So like you gave
    Ws.Range("A1").CopyFromRecordset rs __ )

    In other words
    Ws.Range("A1").CopyFromRecordset rs
    is approximately pseudo like
    Ws.Range( Ws.Cells(1, 1) , Ws.Cells(rs.RecordsRows.Count, rs.Fields.Count) ) = arrFromRecordset()
    where arrFromRecordset() is something we might recognise as a simple VBA 2D array which the copyfromrecordset thing made for us from our ADO “data”

    That makes some sense if you consider the two optional arguments it has of
    maximum rows/records
    maximum columns/fields

    What I mean is, the method is making the range to paste out into. So then it is very simple to allow those options, since you just make the bottom right pseudo
    Ws.Cells(maximum rows/records, maximum columns/fields)
    Instead of
    Ws.Cells(rs.RecordsRows.Count, rs.Fields.Count)

    So if I have that all approximately correct, then that is probably a very efficient way of getting all your data into a worksheet range, ( assuming Microsoft did a good development job on it… )


    I note again that the OP said he didn’t want to use range. I wonder if he meant he did not want to use the copyfromrecordset thing since it is the Range.CopyFromRecordset method
    I expect only a sub set of the data was wanted, possibly just some “column” data

    What would be useful would be to able to get direct access to that arrFromRecordset()
    I expect we can’t. That is internally made and used internally only
    We can do that indirectly: We could paste it all out using the copyfromrecordset thing.
    Then take that data into an array, arrIn()
    Then Clear that data
    Then paste the subset of rows and columns you want in one go using the Index code line like
    ____Index( arrIn(), Rws(), Columns() )

    I suppose the obvious comment to that is “why not paste it all out and then delete what you don’t want
    In a lot of cases you probably might as well do that.
    But deleting rows and columns in a spreadsheet can sometimes take a while, even with coding, as interactions with a spreadsheet are often very slow.
    On the other hand the alternative I suggest can be very efficient in comparison.
    Just to clarify what that alternative to deleting stuff would be: It would be this:
    _ take all info in, from the spreadsheet , in one go,
    _ then using coding to get your wanted arrOut() , via
    _arrout()=Index( arrIn(), Rws(), Columns() )
    _ Then pasting out that array in one go

    ( arrrIn() is effectively arrFromRecordset() - What I am doing here is getting it indirectly, since the copyfromrecordset thing used that, but doesn’t give me direct access to it )

    ( You can “cut out the middle man”, in other words I don’t need to make a arrOut(), like pseudo, I can directly do this instead
    MyWantedDataRange= Index( arrIn(), Rws(), Columns() ) __ )



    At the end of the day it could be either
    _ all splitting hairs
    _ or not
    Just depends I suppose how much data you are playing with and how often you do it.


    I am just mentioning a few possibilities, discussing alternative, that’s all.
    I am pretty ignorant about data analysis, data bases and stuff.
    ( I had a massive amount of data to get at and organise for an important personal project a few years ago.
    I got some great help in forums with using advanced data bases , including some ADO, SQL stuff, that I couldn’t begin to understand
    A few years later I re did a lot of it using more simple things including some of the Index( arrIn(), Rws(), Columns() ) ideas and a few other unconventional things , often using VBA in unusual ways. .
    In some cases it worked a lot better, and most people told me it wouldn’t
    …. )

    Alan
    Last edited by Doc.AElstein; Oct 26th, 2020 at 08:47 AM.

  9. #9
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Error 1004 - Application-defined or object-defined error VBA Excel

    assuming you use a SQL query to select the data in the recordset then you most likely want all the data, or you can do further recordset manipulation to further reduce or add to the records selected then inserted into excel
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width