Hi,

The code below worked fine last month and when tried again it has
given some problems. Basically the code runs but when it gets to the
workbook.open line (highlighted below), it asks to open a new
workbook or not and then just stops.

This is very perplexing as nothing has changed from when i was working
last month.

Thanks in advance.



================================================== =========================

Sub newGAPrates()


Dim rateSheetName As String
Dim rateSheet
Dim GapName As String
Dim Message, Title, Default
Dim currencyCode
Dim currCodeComp
Dim currencyRate
Dim i, j, a
Dim newRates(500, 1)
Dim rate

Message = "Which currency rate this is? insert 01 or 02"
Title = "Rate type"
Default = "01"
rate = InputBox(Message, Title, Default)

Message = "Insert the file name of the currency rates."
Title = "Currency Rates Sheet"
Default = "input file name with .xls"
rateSheetName = InputBox(Message, Title, Default)

Message = "Insert the file name of the GAP rate sheet."
Title = "GAP02"
Default = "input file name with .xls"
GapName = InputBox(Message, Title, Default)

rateSheet = "Q:\Systems Admin\Currency\" + rateSheetName

ChDir "Q:\Systems Admin\Currency"

Workbooks.Open fileName:=rateSheet ***********STOPS
HERE*************

i = 0
For Each rngcell In Range("C12", Range("C13").End(xlDown))
newRates(i, 0) = Range("C12").Offset(i, 0)
newRates(i, 1) = Range("D12").Offset(i, 0)
i = i + 1
Next

ActiveWorkbook.Close

Windows(GapName).Activate

a = 0

For Each rngcell In Range("A3", Range("A4").End(xlDown))
currencyCode = Range("A3").Offset(a, 0)
For i = 0 To 500
If newRates(i, 0) = currencyCode Then
Range("B3").Offset(a, 0) = newRates(i, 1)
End If
Next
a = a + 1
Next

'upload file

Dim fileName
Dim effectDate
Dim dateNow
Dim formatteddate


If rate = "01" Then
Range("A78:D1027").Select
ElseIf rate = "02" Then
Range("A72:D761").Select
End If

Selection.Copy
Workbooks.Add
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False

dateNow = Date
formatteddate = Format(dateNow, "ddmmyy")


If rate = "01" Then
fileName = "GAP01 currency upload " + formatteddate + ".xls"
ActiveWorkbook.SaveAs fileName:= _
"Q:\systems admin\currency\" + fileName, FileFormat:= _
xlNormal, Password:="", WriteResPassword:="",
ReadOnlyRecommended:=False _
, CreateBackup:=False
ElseIf rate = "02" Then
fileName = "GAP02 currency upload " + formatteddate + ".xls"
ActiveWorkbook.SaveAs fileName:= _
"Q:\systems admin\currency\" + fileName, FileFormat:= _
xlNormal, Password:="", WriteResPassword:="",
ReadOnlyRecommended:=False _
, CreateBackup:=False
End If

'Sheets("Sheet3").Select
'ActiveWindow.SelectedSheets.Delete
'Sheets("Sheet2").Select
'ActiveWindow.SelectedSheets.Delete
'Sheets("Sheet1").Select
'Sheets("Sheet1").Name = "upload"

Workbooks(GapName).Activate

If rate = "01" Then
Range("F78:F1027").Select
ElseIf rate = "02" Then
Range("F72:F761").Select
End If

Selection.Copy
Workbooks(fileName).Activate
Range("F1").Activate
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False

Message = "When are these rates affective from?"
Title = "Currency date"
Default = "Effective date"
effectDate = InputBox(Message, Title, Default)

If rate = "01" Then
Range("E1:E950") = effectDate
ElseIf rate = "02" Then
Range("E1:E690") = effectDate
End If

Columns("E:E").Select
Selection.NumberFormat = "mm/dd/yy"

Range("A:F").Select

Selection.Sort Key1:=Range("F1"), Order1:=xlDescending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Columns("F:F").Select
Selection.NumberFormat = "0.00000000"

For i = 0 To 1000
If Range("F1").Offset(i, 0) = 1 Then
Rows(i + 1).Select
Selection.Delete Shift:=xlUp
i = i - 1
End If
Next

Range("A1").Select

ActiveWorkbook.Save
If rate = "01" Then
ActiveWorkbook.SaveAs fileName:= _
"Q:\Systems Admin\Currency\GAP01 currency upload.prn",
FileFormat:= _
xlTextPrinter, CreateBackup:=False
ElseIf rate = "02" Then
ActiveWorkbook.SaveAs fileName:= _
"Q:\Systems Admin\Currency\GAP02 currency upload.prn",
FileFormat:= _
xlTextPrinter, CreateBackup:=False
End If


End Sub