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

Bashir,
Well, the "Workbooks.Open fileName:=rateSheet" line works fine. What error
number are you getting ?

But I do not understand how you are using the "GapName" file when you never
open it ?
You may find it easier and faster if instead of all the .Select s and
Active..., use object variables to refer to the various books/sheets.

This line also:
ActiveWorkbook.SaveAs fileName:= "Q:\systems admin\currency\" + fileName
whilest it does not error, it is probably not a good idea to use Excel words
as variables.

NickHK

"Intikhab Bashir" wrote in message
om...
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

This is a classic case of adopting someones code who left. The GapName
file is used below and holds the rates.

When the macro runs it just stops at the line with no error. It seems
to open the same file and then stop.

Thanks in advance,
Intikhab.

----------------
not a programmer
but trying!!!!!

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Bashir,
not your immediate problem, but your code shows:

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

Without anything to open GapName, unless I'm missing something ??

Again, what error number are you getting ? And what is the value of
"rateSheet" ? maybe if you declared it as a string, it would help ?

NickHK

"intikhab bashir" wrote in message
...
This is a classic case of adopting someones code who left. The GapName
file is used below and holds the rates.

When the macro runs it just stops at the line with no error. It seems
to open the same file and then stop.

Thanks in advance,
Intikhab.

----------------
not a programmer
but trying!!!!!

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!