Error opening file in Macro

L

lidsurfer

New Member
Joined
Mar 29, 2004
Messages
31
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:

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
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
Sort by date Sort by votes
HI

What happens if you run a simple code that will open the file directly from the other machine. Something like

Code: 
Sub eee()
Workbooks.OpenText Filename:="c:\temp\VOIS0830.TXT", origin:=xlMSDOS, startrow:=1, DataType:=xlFixedWidth
End Sub

Tony
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Solved
For next loop with array and using a variable in range().select
Replies
5
Views
136
notmeiquit
N
L
  • Solved
VBA remove leading zeros
2
Replies
14
Views
947
Joe4
Joe4
P
  • Question
Help with Vba Code
Replies
3
Views
154
Coyotex3
C
L
  • Question
Variable Rows, Help Speeding Up
Replies
4
Views
314
kevin9999
K
C
  • Question
When deleting data (rows) macros prompts an error and cannot be used anymore
Replies
1
Views
117
bobaol
B

Forum statistics

Threads
1,214,780
Messages
6,121,525
Members
449,037
Latest member
tmmotairi

Share this page

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