Hi all
I have a VBA issue that I would love some help with.
Ok.
I have an excel sheet which allows a user to input a path to a specific file which is then used to build a pivot table. There are three different sections. First the user selects where the input file is, then they select where they would like to save the output file and then the click 'GO' which runs the below code:
This file/macro runs fine when executed on my machine. When i upload the file to a portal on a different server, it throws the following error:
Error opening file. Method 'OpenText' of object 'Workbooks' failed
This error is from the error handler in the above code.
It appears as though it fails opening the file in the folowing sub:
When I hover above strOpenFilePath it has the correct value i.e. file location "C:\Temp\VOIS0830.TXT"
This is the path that I selected in step 1: Selecting the input file.
Another thing to note is that I have tried putting a copy of the same file in the same folder on the server just in case it was having difficulties finding the file...it still produced the same error.
This problem is driving me crazy, so any assistance would be very appreciated.
Thank you.
I have included the other two macro code in case it is required:
Find File:
Save File:
I have a VBA issue that I would love some help with.
Ok.
I have an excel sheet which allows a user to input a path to a specific file which is then used to build a pivot table. There are three different sections. First the user selects where the input file is, then they select where they would like to save the output file and then the click 'GO' which runs the below code:
Code:
' Method name: OpenFile_Click()
' Description: Executes when the user clicks the Go button.
' Checks if an input file has been specifed and if so opens it to a sheet.
' Calls other methods to create a summary pivot table and save the file.
' Parameters: None
' Returns: Nothing
' Author: Rosalyn Ng
' Date: 17 May 2005
' Changes: Andrew Foster - 15 March 2006.
' Added Sheet Protection
' ToDo:
Sub OpenFile_Click()
Sheets("Process Sheet").Unprotect
'Get file name of file to open
Dim strOpenFilePath As String
strOpenFilePath = Range("InputFile").Value
'Get file name of output file
Dim strOutputFilePath As String
strOutputFilePath = Range("OutputFile").Value
'Check output file name valid
If (strOpenFilePath = "") Then
MsgBox ("No file specified")
Else
On Error GoTo ErrorHandler
'Get just the file name
Dim lngPos As Long
Dim strOpenFile As String
lngPos = InStrRev(strOpenFilePath, "\")
strOpenFile = Right$(strOpenFilePath, Len(strOpenFilePath) - lngPos)
'Opens the .txt file and appends the correct column header.
Call openAndFormatTxt(strOpenFilePath)
'Delete the last row
Set rgLast = Range("A1").SpecialCells(xlCellTypeLastCell)
lLastRow = rgLast.Row
Rows(lLastRow).Select
Selection.ClearContents
Range("A1").Select
'Modify amounts for reversals
Call ModifyAmountsForReversals
'Format Time column
Call FormatTime
'Sort results by Date then time
Call SortResults
'Create summary pivot table
Call PivotTable(strOpenFilePath)
'Save output file
If (strOutputFilePath = "") Then
MsgBox ("Unable to Save output, invalid output file name.")
Else
Call SaveOutput(strOutputFilePath)
Workbooks("ANZEftposMacro.xls").Activate
Sheets("Process Sheet").Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Exit Sub
End If
End If
Workbooks("ANZEftposMacro").Activate
Sheets("Process Sheet").Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Exit Sub
ErrorHandler:
MsgBox ("Error opening file. " + Err.Description)
End Sub
' Method name: ModifyAmountsForReversals()
' Description: Adds an extra column and recalucates the Amounts in cents.
' Amount will become negative if the transaction ID is zero (0).
' This recalculated value is copied to the original column
' Parameters: None
' Author: Rosalyn Ng
' Date: 17 May 2005
' Changes: None
' ToDo: None
Private Sub ModifyAmountsForReversals()
'Move to the temporary column
Range("F1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 12).Select
'Create formula for recalculation and apply to temporary column
ActiveCell.FormulaR1C1 = "=IF(RC[-2]=0, (RC[-12]*-1)/100, RC[-12]/100)"
ActiveCell.Copy
Range("R2", ActiveCell).Select
ActiveSheet.Paste
'Copy over new values to previous Amount column
Selection.Copy
Range("F1").Select
Selection.End(xlDown).Select
Range("F2", ActiveCell).PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
'Remove Temporary column contents
Columns("R:R").Select
Selection.ClearContents
Range("A1").Select
'Format Amount Column
Columns("F:F").Select
Selection.NumberFormat = "$#,##0.00"
End Sub
' Method name: FormatTime()
' Description: Formats the Time field by appending ":" between the time components.
'
' Parameters: None
' Author: Rosalyn Ng
' Date: 17 May 2005
' Changes: None
' ToDo: None
Private Sub FormatTime()
'Change format of temporary column
Range("R:R").Select
Selection.NumberFormat = "h:mm:ss AM/PM"
'Move to the temporary column
Range("F1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 12).Select
'Create formula for reformat and apply to temporary column
ActiveCell.FormulaR1C1 = _
"=if(LEN(RC[-14])=6 , Time(MID(RC[-14], 1, 2), MID(RC[-14], 3, 2), MID(RC[-14], 5, 2)), Time(MID(RC[-14], 1, 1), MID(RC[-14], 2, 2), MID(RC[-14], 4, 2)))"
'"=IF(LEN(RC[-14])=6, CONCATENATE(MID(RC[-14], 1, 2), "":"", MID(RC[-14], 3, 2), "":"", MID(RC[-14], 5, 2)), CONCATENATE(CONCATENATE(""0"",MID(RC[-14], 1, 1)), "":"", MID(RC[-14], 2, 2), "":"", MID(RC[-14], 4, 2)))"
ActiveCell.Select
ActiveCell.Copy
Range("R2", ActiveCell).Select
ActiveSheet.Paste
'Copy over new values to previous Time column
Selection.Copy
Range("D1").Select
Selection.End(xlDown).Select
Range("D2", ActiveCell).PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
'Remove Temporary column contents
Columns("R:R").Select
Selection.ClearContents
'Change format of Time column
Range("D:D").Select
Selection.NumberFormat = "h:mm:ss AM/PM"
Range("C1").Select
End Sub
' Method name: SortResults()
' Description: Sorts the results by date then time
' Parameters: None
' Returns: Nothing
' Author: Rosalyn Ng
' Date: 17 May 2005
' Changes: None
' ToDo: None
Private Sub SortResults()
Cells.Select
Range("C1").Activate
Selection.CurrentRegion.Select
Selection.Sort Key1:=Range("C2"), Order1:=xlAscending, Key2:=Range("D2") _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
:=xlSortNormal
Range("C1").Select
End Sub
' Method name: PivotTable()
' Description: A pivot table is created using the given file source on another sheet
' summing the total cents against the date.
' Parameters: file the input file name
' Author: Rosalyn Ng
' Date: 17 May 2005
' Changes: None
' ToDo: Double check column headers
Private Sub PivotTable(file As String)
'Displays a pivot table on a new sheet using the data from the text file.
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
file + "!R1C1:R1000C16").CreatePivotTable TableDestination:="", TableName _
:="PivotTable2", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
ActiveWorkbook.ShowPivotTableFieldList = True
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Tran Date")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable2").AddDataField ActiveSheet.PivotTables( _
"PivotTable2").PivotFields("Amount"), "Sum of Amount", xlSum
Application.CommandBars("PivotTable").Visible = False
ActiveWorkbook.ShowPivotTableFieldList = False
'Format cells
Range("B5:B8").Select
Selection.NumberFormat = "$#,##0.00"
Columns("B:B").EntireColumn.AutoFit
Range("B9").Select
End Sub
' Method name: SaveOutput()
' Description: Saves the workbook to the specified file name.
' Parameters: file the filepath to save the workbook to.
' Returns: Nothing
' Author: Rosalyn Ng
' Date: 17 May 2005
' Changes: None
' ToDo: Double check column headers
Private Sub SaveOutput(file As String)
On Error GoTo ErrorHandler
ActiveWorkbook.SaveAs Filename:=file, _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
Exit Sub
ErrorHandler:
If (Err.Number <> 1004) Then
MsgBox ("Error saving file. " + CStr(Err.Number) + CStr(Err.Source) + Err.Description)
End If
End Sub
' Method name: openAndFormatTxt()
' Description: Opens the input text file and puts data into columns.
' Formats the sheet by adding relevant column headers,
' resizing some columns and hiding unnecessary columns.
' Parameters: strOpenFile the input file to open
' Author: Rosalyn Ng
' Date: 17 May 2005
' Changes: None
' ToDo: None
Private Sub openAndFormatTxt(strOpenFile As String)
'Open input file
Workbooks.OpenText Filename:=strOpenFile, Origin:=xlMSDOS, _
StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), Array(1, _
1), Array(5, 5), Array(13, 1), Array(19, 1), Array(25, 1), Array(37, 1), Array(43, 2), Array _
(53, 9), Array(56, 1), Array(62, 1), Array(67, 1), Array(74, 9), Array(80, 1), Array(92, 9), _
Array(96, 1), Array(97, 9), Array(98, 1), Array(101, 2), Array(117, 9), Array(120, 1), _
Array(128, 1)), TrailingMinusNumbers:=True
'Append column headers
ActiveCell.FormulaR1C1 = "Rec ID"
Range("B1").Select
ActiveCell.FormulaR1C1 = "Message Type"
Range("C1").Select
ActiveCell.FormulaR1C1 = "Tran Date"
Range("D1").Select
ActiveCell.FormulaR1C1 = "Tran Time"
Range("E1").Select
ActiveCell.FormulaR1C1 = "Tran Code"
Range("F1").Select
ActiveCell.FormulaR1C1 = "Amount"
Range("G1").Select
ActiveCell.FormulaR1C1 = "PAN"
Range("H1").Select
ActiveCell.FormulaR1C1 = "Mobile Number"
Range("I1").Select
ActiveCell.FormulaR1C1 = "Sequence Number"
Range("J1").Select
ActiveCell.FormulaR1C1 = "Network"
Range("K1").Select
ActiveCell.FormulaR1C1 = "Retailer ID"
Range("L1").Select
ActiveCell.FormulaR1C1 = "Terminal ID"
Range("M1").Select
ActiveCell.FormulaR1C1 = "Responder"
Range("N1").Select
ActiveCell.FormulaR1C1 = "Response Code"
Range("O1").Select
ActiveCell.FormulaR1C1 = "Card No"
Range("P1").Select
ActiveCell.FormulaR1C1 = "Vodafone Trans ID"
'Bold font the column header
Rows("1:1").Select
Range("C1").Activate
Selection.Font.Bold = True
'Resize columns
Columns("C:C").Select
Columns("C:C").EntireColumn.AutoFit
Columns("F:F").EntireColumn.AutoFit
Columns("K:K").EntireColumn.AutoFit
Columns("P:P").EntireColumn.AutoFit
Columns("O:O").EntireColumn.AutoFit
Columns("L:L").EntireColumn.AutoFit
Columns("H:H").Select
Columns("H:H").EntireColumn.AutoFit
'Hide unnecessary columns
Range("A1").Select
Range("A:A,B:B,E:E,G:G,I:I,J:J,L:L,M:M,N:N").Select
Range("N1").Activate
Selection.EntireColumn.Hidden = True
ActiveWindow.ScrollColumn = 3
'Format Date column
Columns("C:C").Select
Selection.NumberFormat = "d/mm/yyyy;@"
End Sub
This file/macro runs fine when executed on my machine. When i upload the file to a portal on a different server, it throws the following error:
Error opening file. Method 'OpenText' of object 'Workbooks' failed
This error is from the error handler in the above code.
It appears as though it fails opening the file in the folowing sub:
Code:
' Method name: openAndFormatTxt()
' Description: Opens the input text file and puts data into columns.
' Formats the sheet by adding relevant column headers,
' resizing some columns and hiding unnecessary columns.
' Parameters: strOpenFile the input file to open
' Author: Rosalyn Ng
' Date: 17 May 2005
' Changes: None
' ToDo: None
Private Sub openAndFormatTxt(strOpenFile As String)
'Open input file
Workbooks.OpenText Filename:=strOpenFile, Origin:=xlMSDOS, _
StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), Array(1, _
1), Array(5, 5), Array(13, 1), Array(19, 1), Array(25, 1), Array(37, 1), Array(43, 2), Array _
(53, 9), Array(56, 1), Array(62, 1), Array(67, 1), Array(74, 9), Array(80, 1), Array(92, 9), _
Array(96, 1), Array(97, 9), Array(98, 1), Array(101, 2), Array(117, 9), Array(120, 1), _
Array(128, 1)), TrailingMinusNumbers:=True
When I hover above strOpenFilePath it has the correct value i.e. file location "C:\Temp\VOIS0830.TXT"
This is the path that I selected in step 1: Selecting the input file.
Another thing to note is that I have tried putting a copy of the same file in the same folder on the server just in case it was having difficulties finding the file...it still produced the same error.
This problem is driving me crazy, so any assistance would be very appreciated.
Thank you.
I have included the other two macro code in case it is required:
Find File:
Code:
' Method name: FindFile_Click()
' Description: Executes when the user clicks the Find File button.
' Dialogue opens and user selects the input file for the Macro
' Selected file is populated to cell E5. User is able to cancel the operation
' Using the same file path, a default output file location is shown in E9
' Author: Rosalyn Ng
' Date: 17 May 2005
' Changes: Andrew Foster - 15 March 2006.
' Added Sheet Protection
' ToDo: None
Sub FindFile_Click()
Dim sFileToLoad As Variant
On Error GoTo ErrorHandler
Sheets("Process Sheet").Unprotect
sFileToLoad = Application.GetOpenFilename(FileFilter:= _
"Text Files (*.txt), *.txt,Microsoft Office Excel Workbook (*.xls),*.xls,All Files (*.*),*.*.")
If sFileToLoad <> False Then
Range("InputFile").Value = (CStr(sFileToLoad))
'Add a default filename for the output
Dim lngPos As Long
Dim strDefaultOutputFile As String
lngPos = InStrRev(CStr(sFileToLoad), ".")
strDefaultOutputFile = Left$(CStr(sFileToLoad), lngPos - 1) + "Output.xls"
Range("OutputFile").Value = CStr(strDefaultOutputFile)
End If
Sheets("Process Sheet").Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Exit Sub
ErrorHandler:
MsgBox ("Error locating file. " + Err.Description)
End Sub
Save File:
Code:
' Method name: btnSave_Click()
' Description: Executes when the user clicks the Save Location button.
' Dialogue opens and user selects where to save the output of the Macro
' Selected file is populated to cell E9. User is able to cancel the operation
' Author: Rosalyn Ng
' Date: 17 May 2005
' Changes: Andrew Foster - 15 March 2006.
' Added Sheet Protection
' ToDo: None
Sub btnSave_Click()
Dim sFileToSave As Variant
On Error GoTo ErrorHandler
Sheets("Process Sheet").Unprotect
sFileToSave = Application.GetSaveAsFilename(InitialFileName:="", _
FileFilter:="Microsoft Office Excel Workbook (*.xls), *.xls,Text Files (*.txt), *.txt,All Files (*.*),*.*.")
If sFileToSave <> False Then
Range("OutputFile").Value = (CStr(sFileToSave))
End If
Sheets("Process Sheet").Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Exit Sub
ErrorHandler:
MsgBox ("Error locating save destination. " + Err.Description)
End Sub