• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA Code to Select File to Import Data - Issue Returning to Original Workbook

D

Dernst

New Member
Hello! I am working on creating an excel workbook (tool) for my sales support team that would allow users to open the file explorer, select a file, copy data and then paste it into the original workbook (tool). However, when I try to go to my original workbook (Delphi Event Fee Report) to paste the data I have copied, I get a run time error 424 object required message. I have tried to define the original workbook various ways but always get the object error message. Any thoughts? The Delphi Event Fee Report Temp file is the tool I am creating; the import button tab has a command button I am trying to assign the macro to. The Event Fee Modified file is the file I am copying the data from. The Delphi Data tab on the Delphi Event Fee Report Temp file is where I want the data pasted (cell A2, paste special values). Thank you!


Code:
Code: 
Sub Import()
    Dim FileToOpen As Variant
    Dim OpenBook As Workbook
  
   
    Application.ScreenUpdating = False
    FileToOpen = Application.GetOpenFilename(Title:="Browse for Event Fee Report", FileFilter:="Excel Files(*.xls*),**xls*")
    If FileToOpen <> False Then
     Set OpenBook = Application.Workbooks.Open(FileToOpen)
     ActiveSheet.Range("A2:p500").Select.Copy
        Workbooks("Delphi Event Fee Report Template 2.xlsm").Activate
        Range("A2").Select
        ActiveSheet.PasteSpecial xlPasteValues
        OpenworBook.Close False
   
    
    End If
    Application.ScreenUpdating = True
   
    End Sub
 

Attachments

  • Delphi Event Fee Report Temp.xlsm
    319.7 KB · Views: 6
  • EventFee_Modified_2-10-20 (1).xlsx
    37.2 KB · Views: 3
Last edited by a moderator:
Dernst
I didn't check Your files, but ...
If You use
Dim OpenBook As WorkBook
then why soon something like?
OpenworBook.Close False
 
Isnt that supposed to close the other workbook I opened with the file explorer? Regardless, this is not a line causing issues.
 
the file that the user selects becomes the the openworkbook (typo), which is the event fee modified file. The code selects the range on the only sheet in the file and copies the data.

Code: 
Set OpenBook = Application.Workbooks.Open(FileToOpen) - (selected by user but is the event fee modified file)
     ActiveSheet.Range("A2:p500").Select.Copy - (selects the data from file selected and copies)
Workbooks("Delphi Event Fee Report Template 2.xlsm").Activate - (file I want to go back to)
        Range("A2").Select - (range I want to select in the original file (Delphi Event Fee report temp)
        ActiveSheet.PasteSpecial xlPasteValues - (pastes data from event fee modified file)
        OpenworkBook.Close False - (closes the event fee modified file)
 
Dernst
My the last try ... to get an answer
What is Your OpenworBook?
Have Your even tried to run ONLY Openworkbook.close false ? No!
You could write ALL your code with small letters and after end of line ... You'll see - if there are 'typo's.
Your the newest 'code' --- really cannot work --- maybe You tried to give some comments for it.
 
I was just trying to explain my logic through each line so that you might understand why I wrote the code the way I did. It is not a new code, it is just notes on my code.
 
Dernst
It's good to learn.
One step of learning would be try to read others questions and after that ... answer to questions.
If You try to explain something, then You should learn to explain it as with Excel.
One step would be that You would write code a-little-by-little and test it.
... if it works as You've had an idea then continue
otherwise ... take few deep breaths and fix the code -- test again.
One more step ... check syntaxes ... how those should write?
... Excel works smoother if the code has written as it should write.

> You didn't answer to my questions,
... What is Your OpenworBook?
... Have Your even tried to run ONLY Openworkbook.close false ?
> For my eyes, those would make some challenges with Your code.
Maybe You didn't want to learn?
 
I did answer the question what is your openworkbook? the workbook file Event Fee Modified is the workbook I am opening with openworkbook code. I attached in the original post.

I have not run the openworkbook.close line by itself because that is not where I am having an issue/getting an error which I tried to explain. I get the error message before I even get to this line in the code, so how could this be causing the issue if the system never even gets to this line? From my understanding, codes are read in the order they are written, but hey I guess I could be wrong.

I have done lots of research over the past few days trying to figure out why my code is not working, specifically research on the object error message I have received due to the Workbooks("Delphi Event Fee Report Template 2.xlsm").Activate line in my code. I have tried various attempts to define this workbook as an object with no success. I have read forum after forum and watch video after video and can not find a reason why when I define my workbook as an object I still get this error message.

Please do not say I do not want to learn just because I learn differently than you may learn. I have spent hours doing research and trying to teach myself and am proud of what I have learned completely on my own.

Thanks for your input.
 
Dernst
What is Your OpenworBook? What is Your OpenworkBook?
Where have You written - any of those in Your code? What would be its value?
Have You opened somewhere Your Workbooks("Delphi Event Fee Report Template 2.xlsm") before activate?
How something could close ... if that variable have never before use/open? ... sometimes Excel closes, but normally something unwanted.
Excel do not guess - if Excel finds even 'minor' typo - it will give a note.
Hint 1: If something gives 'note' then try to solve only that part - hide everything else while searching ( = step-by-step ).
HInt 2: If something really do not work then You are in 'loop' >>>then go outside, take a 30minute walk, forget that moment everything about Excel. It would help/solve many cases - even typos.
 
Yes I realized I had the typo when we first started our correspondence, that is why when you asked What your openworbook, I responded with openworKbook (typo) indicating I saw this typo. I will review again today.

The Workbooks("Delphi Event Fee Report Template 2.xlsm") workbook is already opened, so I should try indicating that it needs to be opened again, maybe before activating the sheet I wish to use?

thanks you this is a good starting point for today, I appreciate your input.
 
Hi !​
​
As a smart enough code does not need Activate, Select, …​
Code: 
Sub Import()
   Dim V
       V = Application.GetOpenFilename("Excel Files(*.xls*),**xls*", , "Browse for Event Fee Report")
    If V <> False Then
             Application.ScreenUpdating = False
        With Workbooks.Open(V)
             ThisWorkbook.ActiveSheet.[A2:P500].Value = .ActiveSheet.[A2:P500].Value
            .Close False
        End With
             Application.ScreenUpdating = True
    End If
End Sub
Do you like it ? So thanks to click on bottom right Like !​
​
 
Thank you very much for providing this!

Couple of questions, I am new to coding, and would like to know what each line does. Can you explain each line (trying to learn, if you have time)?
And, if I want to select a different sheet to paste the data, can I replace "ActiveSheet" with "Sheets(2)"?

Code: 
Sub Import()
   Dim V
       V = Application.GetOpenFilename("Excel Files(*.xls*),**xls*", , "Browse for Event Fee Report") - opens file explorer to select file?
    If V <> False Then
             Application.ScreenUpdating = False
        With Workbooks.Open(V)
             ThisWorkbook.ActiveSheet.[A2:p500].Value = .ActiveSheet.[A2:p500].Value - pastes values in template file
            .Close False
        End With
             Application.ScreenUpdating = True
    End If
End Sub
 
Last edited by a moderator:
Edit first your post and use the code tags (via the insert icon) as per forum rules, thanks …​
​
My 'code' is just your code revamped with the useless removed, nothing new …​
For specific statement, place the text cursor on it then hit the F1 key and read the VBA inner help, voilà !​
​
As a reminder : DestinationRange.Value = SourceRange.Value …​
​
 
You must log in or register to reply here.
Back
Top