Opening an Excel Workbook through VBA

Vote count: 0

I'm looking for a way to open an existing workbook and then having my code be able to run on that same workbook.

I don't have much knowledge on VBA and was hoping someone could help. Through my research, I know you can open a workbook and have your code run in the same workbook if the mapping to the file is pasted into the macro code. I want to be able to use an open dialogue box to select it though. This code:

Option Explicit
Private Sub CommandButton1_Click()

Dim directory As String, fileName As String, sheet As Worksheet, total As Integer, fd As Office.FileDialog

Set fd = Application.FileDialog(msoFileDialogFilePicker)

With fd

.AllowMultiSelect = False

.Title = "Please select the file."

.Filters.Clear
.Filters.Add "Excel 2003", "*.xls?"

If .Show = True Then
fileName = Dir(.SelectedItems(1))

End If
End With

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Workbooks.Open (fileName)

For Each sheet In Workbooks(fileName).Worksheets
total = Workbooks("import-sheets.xlsm").Worksheets.Count
Workbooks(fileName).Worksheets(sheet.Name).Copy _
after:=Workbooks("import-sheets.xlsm").Worksheets(total)
Next sheet

Workbooks(fileName).Close

Application.ScreenUpdating = True
Application.DisplayAlerts = True

allows me to select the file through an open dialogue box but then the workbook opens in a new window. Is there a way to alter it so that it just copies the contents of the selected workbook to a blank one that will allow me to run the rest of my code on it?

I didn't include the rest of my code since this question is long enough as is and that wouldn't relate to the problem at hand. Please let me know if it'll be helpful to include it though and I will edit the question.

asked 32 secs ago

JjKaf

Let's block ads! (Why?)

Opening an Excel Workbook through VBA