How to alter files once you've opened a folder, using VBA?

Mr2017

Well-known Member
Joined
Nov 28, 2016
Messages
634
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hi

I've got some VBA code which opens the downloads folder.

But once I've done that, I'd like to select that file and import data from column A - C in it into my active workbook.

Does anyone know how I can modify the code below, please, to transfer data between the workbooks?

I haven't done this in a while - hence the reason I'm asking!

TIA

VBA Code:
Sub OpenDownloadsFolder()




Dim fd As FileDialog
Dim filewaschosen As Boolean
Dim Report As Workbook
Dim iWB As Workbook



Set Report = ActiveWorkbook
Set fd = Application.FileDialog(msoFileDialogOpen)
fd.Filters.Clear
fd.Filters.Add "xlsx files", "*.xlsx"
fd.AllowMultiSelect = False
fd.InitialFileName = Environ("UserProfile") & "\Downloads"
filewaschosen = fd.Show


fd.Execute

End Sub
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
1,820
Office Version
  1. 2010
Platform
  1. Windows
try this:
VBA Code:
Sub OpenDownloadsFolder()
Dim fd As FileDialog
Dim filewaschosen As Boolean
Dim Report As Workbook
Dim iWB As Workbook
Set Report = ActiveWorkbook
Set fd = Application.FileDialog(msoFileDialogOpen)
fd.Filters.Clear
fd.Filters.Add "xlsx files", "*.xlsx"
fd.AllowMultiSelect = False
fd.InitialFileName = Environ("UserProfile") & "\Downloads"
filewaschosen = fd.Show
fd.Execute

lastrow = Cells(Rows.Count, "A").End(xlUp).Row
inarr = Range(Cells(1, 1), Cells(lastrow, 3))
Report.Activate
Range(Cells(1, 1), Cells(lastrow, 3)) = inarr

End Sub
 

Mr2017

Well-known Member
Joined
Nov 28, 2016
Messages
634
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Awesome!!

Thanks @offthelip - that worked!!

Do you know, however, how to amend the code below so that it does what yours does above?

I'm not used to using the numerical references and haven't used the inarr function, before, so it will be easier to complete the rest of the code needed, if I can write it in this format.....

TIA


VBA Code:
Sub OpenDownloadsFolder()


Dim fd As FileDialog
Dim filewaschosen As Boolean
Dim Report As Workbook
Dim iWB As Workbook



Set Report = ActiveWorkbook
Set fd = Application.FileDialog(msoFileDialogOpen)
fd.Filters.Clear
fd.Filters.Add "xlsx files", "*.xlsx"
fd.AllowMultiSelect = False
fd.InitialFileName = Environ("UserProfile") & "\Downloads"
filewaschosen = fd.Show

iWB.Activate
Range("A1", Range("A1").End(xlDown)).Select
Selection.Copy
Report.Activate
Range("A1").PasteSpecial xlPasteAll


fd.Execute

End Sub
 

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
1,820
Office Version
  1. 2010
Platform
  1. Windows
try this:
VBA Code:
Sub OpenDownloadsFolder()


Dim fd As FileDialog
Dim filewaschosen As Boolean
Dim Report As Workbook
Dim iWB As Workbook



Set Report = ActiveWorkbook
Set fd = Application.FileDialog(msoFileDialogOpen)
fd.Filters.Clear
fd.Filters.Add "xlsx files", "*.xlsx"
fd.AllowMultiSelect = False
fd.InitialFileName = Environ("UserProfile") & "\Downloads"
filewaschosen = fd.Show
fd.Execute

lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Range("A1:C" & lastrow).Copy
Report.Activate
Range("A1:C" & lastrow).PasteSpecial



End Sub
Note using the numerical references when using vba is worth learning because it means you can use a index loop to loop through all the columns as well as the rows.
The critical thing to remember is that for some reason MS decided to reverse the order of rows and columns, so to refer to Cell A3 you need to refer to it as cell ( 3,1) i.e row then column!!
 

Mr2017

Well-known Member
Joined
Nov 28, 2016
Messages
634
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
That's great - thank you!! That worked!

I have 3 simple questions, if I may, please:

1) How I can 'enable the content' (using VBA) of the file that's been opened if it asks me to do so?

2) How do I add .xlsm files to the file path? I tried this, but it didn’t work.

VBA Code:
Sub GetData()



Dim fd As FileDialog

Dim filewaschosen As Boolean

Dim Report As Workbook

Dim iWB As Workbook

Set Report = ActiveWorkbook

Set fd = Application.FileDialog(msoFileDialogOpen)

fd.Filters.Clear

‘tried this to add macro files, but it didn’t work?

fd.Filters.Add "xlsx files", "*.xlsx", "xlsm files", "*.xlsm"

fd.AllowMultiSelect = False

fd.InitialFileName = Environ("UserProfile") & "\Downloads"

3) How can I activate sheet 6 (using the code name ie Sheet6) in the file where data is being imported from? Itried this, but it didn’t work? Thanks in advance:

VBA Code:
Sub GetData2()





Dim fd As FileDialog

Dim filewaschosen As Boolean

Dim Report As Workbook

Dim iWB As Workbook

Set Report = ActiveWorkbook

Set fd = Application.FileDialog(msoFileDialogOpen)

fd.Filters.Clear

fd.Filters.Add "xlsx files", "*.xlsx"

'"xlsm files", "*.xlsm"

fd.AllowMultiSelect = False

fd.InitialFileName = Environ("UserProfile") & "\Downloads"



filewaschosen = fd.Show

fd.Execute



Sheet6.Visible = True

Sheet6.Activate

'unhide columns N - AD

Columns("N:AD").Select

Selection.EntireColumn.Hidden = False





'go to N39 and copy the data from that cell until the last row in that column

Range("N39", Range("N39").End(xlDown)).Select

Selection.Copy

'go to your workbook and paste the data into Sheet 4 cell A2



Report.Activate

Sheet4.Visible = True

Sheet4.Activate

Range("A2").PasteSpecial xlPasteValues

End Sub
 

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
1,820
Office Version
  1. 2010
Platform
  1. Windows
This code shows you how to select sheet 6 on the newly opened workbook , and also how to get xlsm files displayed as well. The problem of how to overcome any EXCEL security issues with enabling macros on the file you are opening is not easy and I don't know how you want to get around that. The possible ways are digitally signing the files, or putting it in a trusted location. There may be other ways.
VBA Code:
Sub OpenDownloadsFolder()


Dim fd As FileDialog
Dim filewaschosen As Boolean
Dim Report As Workbook
Dim iWB As Workbook



Set Report = ActiveWorkbook
Set fd = Application.FileDialog(msoFileDialogOpen)
fd.Filters.Clear
fd.Filters.Add "Custom Excel Files", "*.xlsx, *.xlsm, *.xls"
fd.AllowMultiSelect = False
fd.InitialFileName = Environ("UserProfile") & "\Downloads"
filewaschosen = fd.Show
fd.Execute
Worksheets("Sheet6").Select
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Range("A1:C" & lastrow).Copy
Report.Activate
Range("A1:C" & lastrow).PasteSpecial



End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,130,169
Messages
5,640,550
Members
417,151
Latest member
ChickenTenderer

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top