New to VBA and need help with a macro

Kahlan1177

New Member
Joined
Oct 4, 2018
Messages
6
Hi,
So i am working on a macro that will allow me to open a window in workbook 1 to find an excel report file and then format that report. Once the format is complete it then copies and pastes to workbook 1 to the specified tab so that the original file is not touched or modified. I have 3 reports to do in this fashion.

This is what i have so far, but it keeps bugging when it goes to format, i only attached the main routine and one of the sub routines. Can anyone help me see my mistake?


Code:
Public Sub RiskOpen_Workbook_FileDialog()
'
' RiskOpen_Workbook_FileDialog Marco
' open file window to choose file
'
 
'
Dim fd As FileDialog
Dim lngPathLen As Long
Dim strPath As String, strfile As String, strTemp As String
strTemp = ""
Dim strfilepath as String
Set fd = Application.FileDialog(msoFileDialogFilePicker)
With fd
    .AllowMultiSelect = False
    .ButtonName = "Select"
    .InitialView = msoFileDialogViewDetails
    .Title = "Select File To Update"
   
    .InitialFileName = "\\Reports\Privacy & Reputational Risk Audit"
        With .Filters
  
        .Clear
        .Add "Excel Workbooks", "*.xls; *.xlsx"
     
    End With
   
    .FilterIndex = 1
   
    If .Show = -1 Then
        strTemp = .SelectedItems(1)
    End If
   
    Set fd = Nothing
  
End With
 
If strTemp <> "" Then
 
    lngPathLen = InStrRev(strTemp, "")
 
    strPath = Left(strTemp, lngPathLen)
    strfile = Right(strTemp, Len(strTemp) - lngPathLen)
   
    Debug.Print strPath
    strfilepath = strPath & strfile
   
    If Left(strfile, 6) = "MonAdj" Then
        Call MonAdj
    End If
   
     If Left(strfile, 5) = "Award" Then
        Call Award
    End If
   
    If Left(strfile, 5) = "Other" Then
        Call Other
    End If
   
End If
 
End Sub
 
 
 
 
 
 
 
Public strfilepath As String
 
Sub MonAdj()
'
' MonAdj Macro
 
'
    Workbooks.Open Filename:=strfilepath
    Application.Width = 1011
    Application.Height = 757.5
    Cells.Select
    Cells.EntireColumn.AutoFit
    Selection.ColumnWidth = 8.13
    Rows("1:4").Select
    Selection.Delete Shift:=xlUp
    ActiveWindow.SmallScroll Down:=-18
    Columns("B:B").ColumnWidth = 8
    Columns("B:B").EntireColumn.AutoFit
    Columns("C:C").EntireColumn.AutoFit
    Columns("B:B").Select
    Selection.Delete Shift:=xlToLeft
    Columns("E:E").Select
    Selection.Delete Shift:=xlToLeft
    Columns("H:H").Select
    Selection.Delete Shift:=xlToLeft
   Selection.Delete Shift:=xlToLeft
    Selection.Delete Shift:=xlToLeft
    Columns("G:G").EntireColumn.AutoFit
    Cells.Select
    Selection.Copy
    Windows("MACRO Sampler .xlsm").Activate
    Sheets("Mon Adj").Select
    Cells.Select
    ActiveSheet.Paste
End Sub
 
Last edited by a moderator:

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
what is the bug message ?
 
Upvote 0
You are declaring strfilepath as public but then also declaring it again in the first first code.
Remove
Code:
Dim strfilepath as String
from any code & just leave the Public declaration
 
Upvote 0
what is the bug message ?


Hi,
I get:

Run-time error '1004':
" could not be found. Check the spelling of the file name, and verify that the file location is correct.

If you are trying ti open the file from your list of most recently used files, make sure that the file has not been renamed, moved, or deleted.

and the file is there. i have not deleted it or renamed it.
 
Upvote 0
Hi,
If i remove that line of code the macro stops at strfilepath = strPath & strfile and gives me a "ambiguous name detected" message.
 
Upvote 0
That sounds as though you still have more than one declaration of strfilepath
 
Upvote 0
That sounds as though you still have more than one declaration of strfilepath

Hi Fluff,
I'm suspecting i have something wrong in either these lines:

If strTemp <> "" Then

lngPathLen = InStrRev(strTemp, "")

strPath = Left(strTemp, lngPathLen)
strfile = Right(strTemp, Len(strTemp) - lngPathLen)

Debug.Print strPath
strfilepath = strPath & strfile

If Left(strfile, 6) = "MonAdj" Then
Call MonAdj
End If

If Left(strfile, 5) = "Award" Then
Call Award
End If

If Left(strfile, 5) = "Other" Then
Call Other
End If

End If

End Sub



or in this the sub routine that is being Called:


Public strfilepath As String

Sub MonAdj()
'
' MonAdj Macro

'
Workbooks.Open Filename:=strfilepath
Application.Width = 1011
Application.Height = 757.5
Cells.Select
Cells.EntireColumn.AutoFit
Selection.ColumnWidth = 8.13
Rows("1:4").Select
Selection.Delete Shift:=xlUp
ActiveWindow.SmallScroll Down:=-18
Columns("B:B").ColumnWidth = 8
Columns("B:B").EntireColumn.AutoFit
Columns("C:C").EntireColumn.AutoFit
Columns("B:B").Select
Selection.Delete Shift:=xlToLeft
Columns("E:E").Select
Selection.Delete Shift:=xlToLeft
Columns("H:H").Select
Selection.Delete Shift:=xlToLeft
Selection.Delete Shift:=xlToLeft
Selection.Delete Shift:=xlToLeft
Columns("G:G").EntireColumn.AutoFit
Cells.Select
Selection.Copy
Windows("MACRO Sampler .xlsm").Activate
Sheets("Mon Adj").Select
Cells.Select
ActiveSheet.Paste
End Sub
 
Upvote 0
Is this line
Code:
Public strfilepath As String
at the very top of a module (before any code) and does it appear in any other modules?
 
Upvote 0
Is this line
Code:
Public strfilepath As String
at the very top of a module (before any code) and does it appear in any other modules?

I do. :eek:
I have that line in another Module. I have 3 reports that i need to format, i was told by a friend to have 3 different modules. That's why i have the 3 IF statements calling different sub routines. so would the marco run if i merged all my 3 sub routines into one module??
 
Upvote 0
That's fine, but you should only have one declaration for the variable.
If you delete all but one of lines, does your code work?
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,025
Members
448,543
Latest member
MartinLarkin

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