Activate windows of files stored in arrays, but getting subscript out of range error??

Jerryh91

New Member
Joined
Jul 19, 2011
Messages
2
I'm trying to write a copy and paste program that lets the user select which xml files to merge and then in sequence opens up the xml file selected second and paste it into the first selected file (top file in final completed file), opens the xml file selected third and paste it into the xml file selected first ... (until the last file selected had been pasted into the first file selected) and then save the final completed product as a brand new xlsm file with a file name constituting the first and last files pasted onto the very first file opened. But I keep running into either subscript out of range errors or type mismatch errors :confused: Any suggsestions??
Sub Merge()
Dim File As String
Dim AllFiles() As Variant 'Array containing file names to be opened and manipulated
Dim count, test, LastRow As Long 'Count equals number of files stored in Allfiles array, LastRow==equals last row of a sheet for purpose of finding selection area

'codes....
ReDim AllFiles(count)
test = 2
Do While (test <= count)
Windows(AllFiles(test)).Activate 'Subscript out of range error

'codes

Range("LastRow").Select 'subscript out of range error/type mismatch
'codes
ActiveWorkbook.SaveAs Filename:="C:\Allfiles(1)&Allfiles(count)\" & ".xlsm", FileFormat:=52 'save as new .xlsm file with file names of first file opened in allfiles concatenated with last file opened in allfiles. error :type mismatch/subscript out of range
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
Where/when/how do you populate the array AllFiles?
 
Upvote 0
ReDim AllFiles(0)
Do
Application.EnableCancelKey = xlDisabled
File = Application.GetOpenFilename("XML Files (*.xml),*.xml", 1, "Select File to be Merged") 'Needs to select in Order to merge files
Application.EnableCancelKey = xlErrorHandler
If (File = "False") Then Exit Do
ReDim Preserve AllFiles(count) 'Preserve ?
AllFiles(count) = File 'File== file name and directory
count = (count + 1)
If (MsgBox("Select Another File To be Merged With?", vbQuestion + vbOKCancel, "Merge Files") = vbCancel) Then Exit Do
Loop
this is at the very beginning of the codes
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,027
Members
448,543
Latest member
MartinLarkin

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
Back
Top