VBA to perform 'click' function

R2ah1ze1l

Board Regular
Joined
Nov 10, 2017
Messages
93
Good day all,

Struggling with this 'click' functionality. I have seen some instances where vba can be used to call a click function to occur. I am currently struggling with it. Mimicing lines of code is not working and I am curious if this is due to a naming issue, or that I have too much else taking place with the other vba routine that there is generated conflict.

I am recieving a Run-time error '424':
Object required

This occurs when the searched for information is not appearing in the requested file. It is hung up when trying to close the document. Here is the code:

Code:
  If ia > 16382 Then
   MsgBox "SN was not found in the document(s), ensure the SN and Issue# match output file(s)."
   Application.ScreenUpdating = True
   Range("A3").Select
   SRC_Workbook.Save 'need to store when import_click functionality is present
   SCR_Workbook.Close
    GoTo Leave
  End If

Here is the code that looks like it should perform the click function but clearly is not:

Code:
Worksheets("DATA").Select
Worksheets("DATA").Activate
ActiveWorkbook.Sheets("Data").Import.Value = True ' Import_Click from Tabulated Data sheets

I have tried everything I can think of to get this switch to function correctly. I can import data from the original file with other instances of Excel open. I can also have 1 instance and other workbooks open. So that doesn't seem to be my hardship, I'm hoping someone may have another idea. FYI the buttons name is 'Import' and in the same case as referenced. Is 'Import' and illegal term to use for a button name?
 
Solution FOUND!!!

Thank you all for your support. This was a foolish error on my behalf where I did not reset a pathline that ultimately determines where to pull the data from. (I have everything moved to my local for testing and the main info is all in a server)
I was able to learn a lot from the support you all gave and below is my final project!

I've left in the samples provided to help others see options should this be searched in future: (I removed information '###' that should not be shared for varying reasons)
Code:
Sub Button1_Click()
Dim Target_Workbook As Workbook
Dim SRC_Workbook As Workbook
Dim Target_Path As String, Main_Src As String
Dim SRC1_Iss As String, SRC2_Iss As String, SRC3_Iss As String
Dim SRC1_PATH As String, SRC2_PATH As String, SRC3_PATH As String
Dim i As Integer, ia As Integer, ib As Integer, endrow As Integer, ic As Integer, curr_set As Integer
Dim rngtocopy As Range, destpaste As Range
Dim rng_id As Variant
 

Worksheets("Info").Select
Worksheets("Info").Activate
SN = Range("B7").Value
SRC1_Iss = Format(Range("b3").Value, "#.00")
SRC2_Iss = Format(Range("b4").Value, "#.00")
SRC3_Iss = Format(Range("b5").Value, "#.00")
Test1_SRC = "C:\DMIS\Results\Tabulated Backup"
Main_Src = Test1_SRC 'Testing Location
'Main_Src = "M:\CMM Administration\Tabulated_CMM_Data\###" 'Live files
Bt_n = "import"

SRC1_FLD = "###_PROD1_115_PM7"
SRC2_FLD = "###_PROD2_115_PM7"
SRC3_FLD = "###_PROD1_75_PM7"
SRC1_File = "###_PROD1_115_PM7_" & SRC1_Iss & " " & "Tabulated" & " " & "Data.xlsm"
SRC2_File = "###_PROD2_115_PM7_" & SRC2_Iss & " " & "Tabulated" & " " & "Data.xlsm"
SRC3_File = "###_PROD1_75_PM7_" & SRC3_Iss & " " & "Tabulated" & " " & "Data.xlsm"
SRC1_PATH = Main_Src & "\" & SRC1_FLD & "\" & SRC1_File
SRC2_PATH = Main_Src & "\" & SRC2_FLD & "\" & SRC2_File
SRC3_PATH = Main_Src & "\" & SRC3_FLD & "\" & SRC3_File
 
 
'begins PROD1 data transfer
i = 0
Do While i < 5
i = i + 1
If i = 4 Then
 GoTo Leave
End If
   If i = 1 Then
    path_fld = SRC1_PATH
    mac_fld = SRC1_File
   End If
   If i = 2 Then
    path_fld = SRC2_PATH
    mac_fld = SRC2_File
   End If
   If i = 3 Then
    path_fld = SRC3_PATH
    mac_fld = SRC3_File
   End If
Set SRC_Workbook = Workbooks.Open(path_fld)
Application.ScreenUpdating = True 'False for production use, True for Testing
Worksheets("DATA").Select
Worksheets("DATA").Activate
ActiveWorkbook.Sheets("Data").Import.Value = True ' Import_Click from Tabulated Data sheets  <-original line works, it was within the "data" sheet a pathline was set incorrectly
'Call Import_Click
'Application.ActiveSheet
'Application.Run "'" & mac_fld & "'!(Import)"
'Application.ActiveWorkbook.ActiveSheet.Run (Import)
'ActiveWorkbook.Run (Import)
'activeWorkbook.RunMacros (Import_Click)
Worksheets("DATA").Select
Worksheets("DATA").Activate
Range("V3").Select
ia = 22
ib = 0
  Do While ia < 16383
  ActiveCell.Offset(0, 1).Select
   If ActiveCell.Value = SN Then
    ia = ia + 1
      Do While ib < 13
       ActiveCell.Offset(0, 1).Select
        If ActiveCell.Value = SN Then
         ib = ib + 1
         GoTo Jump1
        Else
         ib = ib + 1
          If ib > 10 Then
           ib = 0
           GoTo Jump1
          End If
        End If
      Loop
Jump1:
    curr_set = ia + ib
    GoTo Jump2
   Else
    ia = ia + 1
   End If
  Loop
    '^ Locating SN, checks if 2 matched SN's, uses last found
  
  If ia > 16382 Then
   MsgBox "SN was not found in the document(s), ensure the SN and Issue# match output file(s)."
   Application.ScreenUpdating = True
   Range("A3").Select
   SRC_Workbook.Save 'need to store when import_click functionality is present
   SCR_Workbook.Quit
    GoTo Leave
  End If
Jump2:
   If i = 1 Then
    ic = 2270
    rng_id = "E1:E2270"
   End If
   If i = 2 Then
    ic = 1237
    rng_id = "M1:M1237"
   End If
   If i = 3 Then
    ic = 851
    rng_id = "U1:U851"
   End If
   Application.ScreenUpdating = True 'False for production use
   Application.CutCopyMode = False
   
   Workbooks(1).Activate
   Set destpass = Workbooks(1).Sheets("Data").Range(rng_id)
   Workbooks(2).Activate
   Range("A3").Select
   Set rngtocopy = Workbooks(2).Sheets("Data").Range(Cells(1, curr_set), Cells(ic, curr_set))
   
   rngtocopy.Copy (destpass)
   Application.ScreenUpdating = True
   
   Workbooks(2).Activate
   Workbooks(2).Save
   Workbooks(2).Close
   
   Workbooks(1).Activate
Loop
Leave:
End Sub
 
Upvote 0

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
The data won't be retained in what file? Tabulated Data file

Also, why would the CSV be deleted? CSV is killed off within code to help limit used storage space.

Even if the original code did the above couldn't you copy it and adjust it so it doesn't.

I wanted to limit any additional modifications possible. If this *new* file needs to be updated, it now can without concern in relation to the master file *Tabulated Data*. Well, it's closer..I still need to figure out a couple small items to ensure ALL the data is copied and pasted properly. I have those area's hard coded currently. With a little time, I will learn the how to fix for that.
 
Upvote 0

Forum statistics

Threads
1,223,153
Messages
6,170,391
Members
452,324
Latest member
stuart1980

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