Import Data

[josros60]

Hi,

I found this code to import data it's working good the only problem i found is that only importing upto line (row) 27 how to modify it that can import all the data in those columns (first was missing row one and the last one but fixed it for the first row, but still the problem to import after row 27, much appreciated your help.

here is the code:

Sub ImportRawData()

    Dim c           As Long
    Dim Col         As Variant
    Dim Filename    As String
    Dim Filepath    As Variant
    Dim rngBeg      As Range
    Dim rngEnd      As Range
    Dim rngDst      As Range
    Dim rngSrc      As Range
    Dim rowsize     As Long
    Dim wkbDst      As Workbook
    Dim wkbSrc      As Workbook
    
        Set wkbDst = ThisWorkbook
        Set rngDst = wkbDst.Worksheets("EFT").Range("A4:E4")
        
        Filepath = "C:\Users\jose.rossi\Desktop\NCL EFT_Summary.xlsm"
        Filename = "apcbtclz.csv"
        
        On Error Resume Next
            Set wkbSrc = Workbooks(Filename)
            If Err = 9 Then
                If Filepath <> "" Then ChDir Filepath Else ChDir ThisWorkbook.Path
                Filename = Application.GetOpenFilename("Excel Workbooks, *.xlsx")
                If Filename = "False" Then Exit Sub
                Set wkbSrc = Workbooks.Open(Filename)
            End If
        On Error GoTo 0
        
        ' Clear previous data.
        rngDst.Resize(rngDst.Parent.UsedRange.Rows.Count).ClearContents
        
        ' Import the data.
        With wkbSrc.Worksheets("apcbtclz").UsedRange
            ' Step through the source data columns.
            For Each Col In Array("AW", "BO", "BB", "AX", "X")
                ' Data starts on row 1.
                Set rngBeg = .Parent.Cells(1, Col)
                
                ' Find the row where the data ends in this column.
                Set rngEnd = .Parent.Cells(Rows.Count, Col).End(xlUp)
                
                ' Number of rows in this column.
                rowsize = rngEnd.Row - rngBeg.Row
                
                If rowsize > 0 Then
                    Set rngSrc = .Parent.Range(rngBeg, rngEnd)
                    rngDst.Offset(0, c).Resize(rowsize, 1).Value = rngSrc.Value
                End If
                
                ' Increment the column offset.
                c = c + 1
            Next Col
        End With
        
End Sub

 

[Fluff]

As you are copying from row 1 downwards, this line

rowsize = rngEnd.Row - rngBeg.Row

should be

rowsize = rngEnd.Row

 

[josros60]

Thank you so much, it worked perfectly.

just one question if possible in this line:

For Each Col In Array("AW", "BO", "BB", "AX", "X")

The

"AX"

column has the name of the director approving the invoice but if the row is blank how to modify the the code to add the word "Recurring" when blank.

Thanks again.

 

[Fluff]

As that's a completely different question, it needs a new thread. Thanks

 

[josros60]

Sorry.

I did now.

thank you again.

 

[Fluff]

You're welcome & thanks for the feedback.

 

[Fluff]

For future reference

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Import Data
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.