Variable Filename Help

JBerr

New Member
Joined
Dec 10, 2020
Messages
7
Office Version
  1. 365
Platform
  1. Windows
I am new to VBA coding and working on automating a process that I perform every month. I recorded the macro below which works as intended if I am testing using the specific specified filename. I need both the Capital One Allocation & transaction download filenames to be variable. I have searched the boards and was unable to find something similar enough for me to try and use it. This is called from a user form:

VBA Code:
Private Sub Yes_Click()

    Windows("Capital One Allocation - Rev IW.xlsm").Activate
    Windows("2020-11-29_transaction_download.xlsx").Activate
    
'Copy range
    
    Range("A2:G100").Select
    Selection.Copy
    Windows("Capital One Allocation - Rev IW.xlsm").Activate
    Range("B3").Select
    ActiveSheet.Paste
    Range("B2:H53").Select
    Application.CutCopyMode = False
    ActiveWorkbook.Worksheets("Download").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Download").Sort.SortFields.Add2 Key:=Range( _
        "D3:D53"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    ActiveWorkbook.Worksheets("Download").Sort.SortFields.Add2 Key:=Range( _
        "B3:B53"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Download").Sort
        .SetRange Range("B2:H53")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("B3").Select

Unload DownloadImport

End Sub

Any help you can give would be appreciated.
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
1,493
Office Version
  1. 2013
Platform
  1. Windows
I need both the Capital One Allocation & transaction download filenames to be variable.
In what way? Could you be more specific?
 

JBerr

New Member
Joined
Dec 10, 2020
Messages
7
Office Version
  1. 365
Platform
  1. Windows
In what way? Could you be more specific?
Each month I use a blank template to import the transactions then rename it to that year/month/day. The transaction download from Capital One is also a different filename each month. I am using this code to get the import filename and paste it in the Download worksheet:

VBA Code:
Private Sub Worksheet_Activate()

'Picking the download filename

    If Sheet2.Range("C1").Value <> "" Then
        GoTo FileNameInUse:
    End If

    Dim fileTxt As Variant

    fileTxt = Application.GetOpenFilename("ALL XLSX files (*.xlsx*), *.xlsx", , "Please Choose File")

    On Error GoTo CancelFileSelection:
    
    If fileTxt <> False Then
        Sheet2.Range("C1").Value = fileTxt
    End If

'Opening the download file

    Dim DLWorkBook As Workbook
    
    Set DLWorkBook = Workbooks.Open(Filename:=Sheet2.Range("C1").Value)

'Importing via dialog box

    DownloadImport.Show
    
    Exit Sub
 

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
1,493
Office Version
  1. 2013
Platform
  1. Windows
So what you want is that your userform DownoadImport gets awareness of the picked filename?
 

JBerr

New Member
Joined
Dec 10, 2020
Messages
7
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

So what you want is that your userform DownoadImport gets awareness of the picked filename?
Yes. The Code I used in the first post is the "Yes" click on my userform.
 

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
1,493
Office Version
  1. 2013
Platform
  1. Windows
From your post #1 code it's not clear to me which workbook is the source to be copied from and which one is the target workbook. So difficult for me to adjust that part of your code.
To pass the file to your userform copy the code below to the userform's module:
VBA Code:
Option Explicit

Private oWbPicked As Workbook

Property Set PickedWorkbook(ByVal argWb As Workbook)
    Set oWbPicked = argWb
End Property

Change this part of your post #1 code DownloadImport.Show to:
VBA Code:
With DownloadImport
    Set .PickedWorkbook = DLWorkBook
    .Show
End With

Your userform now has awareness of the picked file, the variable oWbPicked contains its reference.
 

JBerr

New Member
Joined
Dec 10, 2020
Messages
7
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Here is how it looks:

VBA Code:
Option Explicit

Private Sub No_Click()

    Unload DownloadImport

End Sub

Private oWbPicked As Workbook

Property Set PickedWorkbook(ByVal argWb As Workbook)
    Set oWbPicked = argWb

End Property

Private Sub Yes_Click()

    With DownloadImport
        Set .PickedWorkbook = DLWorkbook
        .Show
    End With

When it gets to
VBA Code:
DLWorkbook
it breaks with the Variable not set error.
 

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
1,493
Office Version
  1. 2013
Platform
  1. Windows
Apologies since I misinformed you, my mistake. When I said:

Change this part of your post #1 code DownloadImport.Show to:
I meant your post #3 code, the Worksheet_Activate event procedure in which the userform is launched.

If I understand correctly, your intention at first is to copy from workbook [2020-11-29_transaction_download.xlsx] to workbook [Capital One Allocation - Rev IW.xlsm].
If your macros are also running in the latter workbook, you can refer to this workbook with the keyword ThisWorkbook.

The code of your userform could then look like this:

VBA Code:
Option Explicit

Private oWbPicked As Workbook

Property Set PickedWorkbook(ByVal argWb As Workbook)
    Set oWbPicked = argWb
End Property


Private Sub Yes_Click()

    ' Windows("Capital One Allocation - Rev IW.xlsm").Activate
    ' Windows("2020-11-29_transaction_download.xlsx").Activate

'Copy range

    oWbPicked.ActiveSheet.Range("A2:G100").Copy Destination:=ThisWorkbook.ActiveSheet.Range("B3")

'    Windows("Capital One Allocation - Rev IW.xlsm").Activate
'    Range("B3").Select
'    ActiveSheet.Paste

    Range("B2:H53").Select
    Application.CutCopyMode = False
    ActiveWorkbook.Worksheets("Download").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Download").Sort.SortFields.Add2 Key:=Range( _
        "D3:D53"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    ActiveWorkbook.Worksheets("Download").Sort.SortFields.Add2 Key:=Range( _
        "B3:B53"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Download").Sort
        .SetRange Range("B2:H53")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("B3").Select

Unload DownloadImport

End Sub

The code to launch your userform (you have choosen for the Worksheet_Activate event) could look like this:
VBA Code:
Private Sub Worksheet_Activate()

    Dim sFilePathAndName    As String
    Dim DLWorkBook          As Workbook

    sFilePathAndName = Application.GetOpenFilename("ALL XLSX files (*.xlsx), *.xlsx", , "Please Choose File")
    
    If sFilePathAndName <> "False" Then
        
        Sheet2.Range("C1").Value = sFilePathAndName

        If Not isWorkbookOpen(sFilePathAndName) Then
            
            Set DLWorkBook = Workbooks.Open(FileName:=sFilePathAndName)
            ' launch userform
            With DownloadImport
                Set .PickedWorkbook = DLWorkBook
                .Show
            End With

        Else    ' selected filename belongs to an already opened workbook
                ' so what should we do different  ....
        End If

    Else
        MsgBox "No file selected, cancel was pressed."

    End If
End Sub

Note the use of a separate function to check whether a workbook is already open or not. Paste the code of this function in a standard module.
VBA Code:
Public Function isWorkbookOpen(argFileName As String) As Boolean

    Dim fileID As Long
    Dim errNum As Long

    ' retrieve file handle from Windows OS
    fileID = FreeFile()
    
    ' don't act on errors for now
    On Error Resume Next
    
    ' open file for reading en try to LOCK (!!) this file
    ' to prevent other processes / programs accessing this file
    Open argFileName For Input Lock Read As #fileID
    
    ' if LOCKING fails, an error occurs
    ' assign error number to variabele
    errNum = Err.Number
    
    ' If lock succeeded, file has to be closed, using its file handle
    Close fileID
    
    ' if error was 0, there where no errors, ....
    ' ... so opening and locking file succeeded, so it was not open before
    ' in VBA the number 0 equals FALSE, any other number equals TRUE
    ' convert error number to boolean and return result
    isWorkbookOpen = CBool(errNum)

End Function
 
Solution

JBerr

New Member
Joined
Dec 10, 2020
Messages
7
Office Version
  1. 365
Platform
  1. Windows
I copied your code into the userform and am getting a "Runtime Error 91 Object Variable or With Block Variable not set" at the
VBA Code:
oWbPicked.ActiveSheet.Range
portion of the code.

VBA Code:
Option Explicit

Private oWbPicked As Workbook

Property Set PickedWorkbook(ByVal argWb As Workbook)
    Set oWbPicked = argWb

End Property

Private Sub Yes_Click()

'Copy range
      
     oWbPicked.ActiveSheet.Range("A2:G100").Copy Destination:=ThisWorkbook.ActiveSheet.Range("B3")

I did not change the file picker code I am using because it works unless it is the reason for the Runtime Error 91 break. I have not figured out how to add in your check for open filename code yet.
 

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
1,493
Office Version
  1. 2013
Platform
  1. Windows
This part
VBA Code:
With DownloadImport
    Set .PickedWorkbook = DLWorkBook
    .Show
End With
in the code you are using to launch the userform is essential!

It loads the userform into memory, it is assigning your picked workbook to the custom PickedWorkbook property of your userform, causing this code in your userform to run:
VBA Code:
Property Set PickedWorkbook(ByVal argWb As Workbook)
    Set oWbPicked = argWb
End Property
... and finally puts your userform on your screen.
 

Forum statistics

Threads
1,136,288
Messages
5,674,870
Members
419,531
Latest member
Mjsherwin

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
Top