nsus
03-20-2009, 09:38 AM
I have written a piece of code that prompts the user for a data file location; then asks if the user wants to add another data file but the user does not need to select another file. Then the code opens the data file and copies over some information to the current worksheet.
My problems:
1) If the user does not want to add a second data sheet, the code still tried to open the file and gets hung up. I tried to deal with the statement: If DataSheet2<> "NoDataSet" Then. But it tried to open DataSheet 2 even if the user selected "no"
2)How can I close DataSheet1 and DataSheet2 when I have transfered the information?
My current problem code:
ThisSpreadsheet = "Lih_Data_import.xls"
Dim DataSet1 As Variant
Dim DataSet2 As Variant
Dim DataSet3 As Variant
DataSet1 = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls), *xls", Title:="Choose Import Data File", MultiSelect:=False)
myMsg = "Do you want to select another file?"
Response = MsgBox(myMsg, vbYesNo, myTitle)
Select Case Response
Case Is = vbYes
DataSet2 = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls), *xls", Title:="Choose Import Data File", MultiSelect:=False)
Case Is = vbNo
DataSet2 = "NoDataSet"
End Select
'Transfers REM5
'datasheet1
Workbooks.Open DataSet1
Sheets("REM5").Select
Range("A2:G2500").Copy
Windows(ThisSpreadsheet).Activate
Sheets("REM5").Select
Range("A5").PasteSpecial Paste:=xlPasteValues
FinalRow = Cells(65536, 1).End(xlUp).Row
'Datasheet2
If DataSheet2 <> "NoDataSet" Then
Workbooks.Open DataSet2
Sheets("REM5").Select
Range("A2:G2500").Copy
Windows(ThisSpreadsheet).Activate
Sheets("REM5").Select
FinalRow = Cells(65536, 1).End(xlUp).Row
Cells(FinalRow + 1, 1).PasteSpecial Paste:=xlPasteValues
End If
My problems:
1) If the user does not want to add a second data sheet, the code still tried to open the file and gets hung up. I tried to deal with the statement: If DataSheet2<> "NoDataSet" Then. But it tried to open DataSheet 2 even if the user selected "no"
2)How can I close DataSheet1 and DataSheet2 when I have transfered the information?
My current problem code:
ThisSpreadsheet = "Lih_Data_import.xls"
Dim DataSet1 As Variant
Dim DataSet2 As Variant
Dim DataSet3 As Variant
DataSet1 = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls), *xls", Title:="Choose Import Data File", MultiSelect:=False)
myMsg = "Do you want to select another file?"
Response = MsgBox(myMsg, vbYesNo, myTitle)
Select Case Response
Case Is = vbYes
DataSet2 = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls), *xls", Title:="Choose Import Data File", MultiSelect:=False)
Case Is = vbNo
DataSet2 = "NoDataSet"
End Select
'Transfers REM5
'datasheet1
Workbooks.Open DataSet1
Sheets("REM5").Select
Range("A2:G2500").Copy
Windows(ThisSpreadsheet).Activate
Sheets("REM5").Select
Range("A5").PasteSpecial Paste:=xlPasteValues
FinalRow = Cells(65536, 1).End(xlUp).Row
'Datasheet2
If DataSheet2 <> "NoDataSet" Then
Workbooks.Open DataSet2
Sheets("REM5").Select
Range("A2:G2500").Copy
Windows(ThisSpreadsheet).Activate
Sheets("REM5").Select
FinalRow = Cells(65536, 1).End(xlUp).Row
Cells(FinalRow + 1, 1).PasteSpecial Paste:=xlPasteValues
End If