Microsoft Office Forums - View Single Post

gmayor · 10-16-2016, 04:03 AM

You need a function like the folllowing to import the CSV into the worksheet. I have no idea what the mysterious Macro1 refers to as your Master.xlsx does not support macros. The macro below when called from the earlier macro adds the CSV data to the end of Sheet1 in the Master.xlsx workbook, provided there is data in column A (or the worksheet is empty)

Code:

Sub CopyData(strCSV As String)
Const strWB As String = "T:\Accounting\Master.xlsx"
Dim xlApp As Object
Dim xlWb As Object
Dim xlSheet As Object
Dim NextRow As Long
Const xlDelimited As Long = 1

    On Error Resume Next
    Set xlApp = GetObject(, "Excel.Application")
    If Err <> 0 Then
        Set xlApp = CreateObject("Excel.Application")
    End If
    On Error GoTo 0
    'Open the workbook to receive the data

    Set xlWb = xlApp.workbooks.Open(strWB)
    Set xlSheet = xlWb.sheets(1)
    If xlSheet.Range("A1") = "" Then
        NextRow = 1
    Else
        NextRow = xlSheet.Range("A" & xlSheet.Rows.Count).End(-4162).Row + 1
    End If
    'Import the data from the CSV
    With xlSheet.QueryTables.Add(Connection:="TEXT;" & strCSV, _
                                 Destination:=xlSheet.Range("A" & NextRow))
        .TextFileParseType = xlDelimited
        .TextFileCommaDelimiter = True
        .Refresh
    End With
    'Save the workbook
    xlWb.Close SaveChanges:=True
lbl_Exit:
    Set xlApp = Nothing
    Set xlWb = Nothing
    Set xlSheet = Nothing
    Exit Sub
End Sub

Replace
'Do stuff with the csv file here
with

Code:

CopyData strFilename