Results 1 to 4 of 4
  1. #1
    Join Date
    May 2005
    Posts
    129
    Provided Answers: 1

    Answered: VBA Help - Read a column of data and turn into a string for later use

    Hi I need your assistance once more

    I want to read a column of data in worksheet the number of records (rows) will change on a daily basis, I can do it within Access for I'm referencing a table see code below, just cant get my head around reading a column of data in Ahhhh

    Column A
    11111
    22222
    33333
    444

    And turn it into this And SALES.Account IN ('111111', '22222', '33333', '444')

    This output will be called upon by another function


    Public Function AccList() As String


    Dim rsDAO As DAO.Recordset
    Dim TmpString As String

    Set rsDAO = CurrentDb.OpenRecordset("SiteDetails")

    TmpString = "And SALES.Account IN ("

    Do Until rsDAO.EOF

    TmpString = TmpString & "'" & rsDAO(4) & "', "

    rsDAO.MoveNext

    Loop

    AccList = Left(TmpString, Len(TmpString) - 2) & ") "

    End Function

  2. Best Answer
    Posted by MikeTheBike

    "Hi

    Without knowing anything about your spread sheet/layout (or indeed you knowledge os VBA Excel) etc. something like this
    Code:
    Option Explicit
    
    Public Function AccList() As String
        Dim i As Long
        Dim TmpString As String
        Dim iColNo As Integer
    
        iColNo = 5
    
        TmpString = "And SALES.Account IN ("
    
        Do Until Cells(i, iColNo) = ""
    
            TmpString = TmpString & "'" & Cells(i, iColNo) & "', "
    
           i = i + 1
        Loop
    
        AccList = Left(TmpString, Len(TmpString) - 2) & ") "
    
    End Function
    Or perhaps this
    Code:
    Public Function AccList() As String
        Dim i As Long
        Dim iBotRow As Long
        Dim iTopRow As Long
        Dim TmpString As String
        Dim iColNo As Integer
    
        iColNo = 5
        iTopRow = 2
        iBotRow = ActiveSheet.Cells.SpecialCells(xlLastCell).Row
    
        TmpString = "And SALES.Account IN ("
    
        For i = iTopRow To iBotRow
            TmpString = TmpString & "'" & Cells(i, iColNo) & "', "
        Next i
    
        AccList = Left(TmpString, Len(TmpString) - 2) & ") "
    
    End Function
    Hope this is of some help/indication of what is possible!

    MTB"


  3. #2
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    805
    Provided Answers: 2
    Hi

    Without knowing anything about your spread sheet/layout (or indeed you knowledge os VBA Excel) etc. something like this
    Code:
    Option Explicit
    
    Public Function AccList() As String
        Dim i As Long
        Dim TmpString As String
        Dim iColNo As Integer
    
        iColNo = 5
    
        TmpString = "And SALES.Account IN ("
    
        Do Until Cells(i, iColNo) = ""
    
            TmpString = TmpString & "'" & Cells(i, iColNo) & "', "
    
           i = i + 1
        Loop
    
        AccList = Left(TmpString, Len(TmpString) - 2) & ") "
    
    End Function
    Or perhaps this
    Code:
    Public Function AccList() As String
        Dim i As Long
        Dim iBotRow As Long
        Dim iTopRow As Long
        Dim TmpString As String
        Dim iColNo As Integer
    
        iColNo = 5
        iTopRow = 2
        iBotRow = ActiveSheet.Cells.SpecialCells(xlLastCell).Row
    
        TmpString = "And SALES.Account IN ("
    
        For i = iTopRow To iBotRow
            TmpString = TmpString & "'" & Cells(i, iColNo) & "', "
        Next i
    
        AccList = Left(TmpString, Len(TmpString) - 2) & ") "
    
    End Function
    Hope this is of some help/indication of what is possible!

    MTB

  4. #3
    Join Date
    May 2005
    Posts
    129
    Provided Answers: 1
    Thanks MTB

    Working like a dream

  5. #4
    Join Date
    Jun 2017
    Location
    Ukraine
    Posts
    11

    VBA Help Read a column of data and turn into a string for later use

    Can I use Microsoft Access with Microsoft SQL Server database software and what are the advantages?

Posting Permissions

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