CODEWITHSUNDEEP
vbaexcel
Aqqqq
Aqqqq

Reputation: 848

With VBA, how to import a xlsx file into a xlsm without opening the xlsx file?

This is part of my current code:

Option Explicit

Sub ImportData()

    Dim wkbCrntWorkBook As Workbook
    Dim wkbSourceBook   As Workbook
    Dim fNameAndPath As Variant

    Set wkbCrntWorkBook = ActiveWorkbook
    fNameAndPath = Application.GetOpenFilename(FileFilter:="Excel 2007, *.xlsx; *.xlsm; *.xlsa", Title:="Select File To Import")
    If fNameAndPath = False Then Exit Sub
    Call ReadDataFromCloseFile(fNameAndPath)


    Set wkbCrntWorkBook = Nothing
    Set wkbSourceBook = Nothing

End Sub

Sub ReadDataFromCloseFile(filePath As Variant)
    On Error GoTo ErrHandler
    Application.ScreenUpdating = False

    Dim src As Workbook

    Set src = Workbooks.Open(filePath, True, True)

' COPY DATA FROM SOURCE (CLOSE WORKGROUP) TO THE DESTINATION WORKBOOK.
' GET THE TOTAL ROWS FROM THE SOURCE WORKBOOK.
Dim lastLine As Long   ' last line from source
lastLine = src.Worksheets(source_sheet_1_name).Range("A" & Rows.count).End(xlUp).Row
Worksheets("abc").Range("A3:A40")).Value= src.Worksheets("cde").Range("A4:A41").Value


' CLOSE THE SOURCE FILE.
src.Close False             ' FALSE - DON'T SAVE THE SOURCE FILE.
Set src = Nothing

ErrHandler:
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

According to this, the source workbook of the data will not be opened. However, as soon as I clicked the file name in the file browser to select the file to be imported, excel opened the file.

Does anyone know how I should achieve the objective (regardless of the link in the last paragraph)?

(My objective is to copy/import certain columns from an xlsx file to xlsm file according to certain criterien automatically after the user indicate the file to be imported (without opening the file to be imported). However, after I select the file to be imported by double click, the file to be imported is merely opened. The program does not carry forward to import the corresponding columns after the file to be imported is open.)

Upvotes: 0

Views: 2246

Answers (1)

Busse
Busse

Reputation: 863

There are a few things you can do here. The file needs to be opened, one way or another. But making it as locked down/hidden as possible will be the key.

You can try a few different things:

1) Before you open the workbook, set Application.ScreenUpdating = False, and then at the end before closing out of the sub, make sure you set it back to True.

2) After opening the workbook with src = workbooks.open... try setting Application.Visible = False. Once you are finished with the import process, and closing out of the source workbook, make sure you set it back to true: Application.Visible = True.

This should allow you to open the workbook, keep the updating off so no one can see what happens, and close out of the workbook with minimal resistance/visibility.

The reason it might be causing an error, or just outright not working is possibly from the workbook sheet name you are trying to call. I do not see the assignment of the variable, so lastLine = src.Worksheets(source_sheet_1_name) should actually say the sheetname in it, such as lastLine = src.Worksheets("Sheet1")

Give this a shot and let me know.

Upvotes: 2

Related Questions