+ Reply to Thread
Results 1 to 11 of 11

How to activate a workbook with the variable name

  1. #1
    Registered User
    Join Date
    09-22-2011
    Location
    Italy
    MS-Off Ver
    Office 2019 (Windows 11 Pro)
    Posts
    47

    How to activate a workbook with the variable name

    Hi,
    how to activate a workbook with the variable name?
    Workbooks("\Abc-" & Format(Now, "yyyy-mm-dd") & ".xlsx").Activate (error run-time 9)
    Thank you
    Last edited by alpha45; 09-18-2018 at 01:37 PM.

  2. #2
    Forum Expert JLGWhiz's Avatar
    Join Date
    02-20-2011
    Location
    Florida, USA
    MS-Off Ver
    Windows 10, Excel 2013
    Posts
    2,070

    Re: How to activate a workbook with the variable name

    Make sure the workbook is open. If not then
    Please Login or Register  to view this content.
    If that backward slash (\) is not actually part of the workbook, name then delete it from the statement. And it should not be part of the file name.
    Last edited by JLGWhiz; 09-18-2018 at 01:50 PM.
    Any code provided by me should be tested on a copy or a mock up of your original data before applying it to the original. Some events in VBA cannot be reversed with the undo facility in Excel. If your original post is satisfied, please mark the thread as "Solved". To upload a file, see the banner at top of this page.
    Just when I think I am smart, I learn something new!

  3. #3
    Registered User
    Join Date
    09-22-2011
    Location
    Italy
    MS-Off Ver
    Office 2019 (Windows 11 Pro)
    Posts
    47

    Re: How to activate a workbook with the variable name

    Thanks for the reply, I did not believe that opening the file remained active.
    thank you

  4. #4
    Forum Expert JLGWhiz's Avatar
    Join Date
    02-20-2011
    Location
    Florida, USA
    MS-Off Ver
    Windows 10, Excel 2013
    Posts
    2,070

    Re: How to activate a workbook with the variable name

    Quote Originally Posted by alpha45 View Post
    Thanks for the reply, I did not believe that opening the file remained active.
    thank you
    It will be the active workbook until the user changes its status or another workbook is opened or activated in some way.
    Regards, JLG

  5. #5
    Registered User
    Join Date
    09-22-2011
    Location
    Italy
    MS-Off Ver
    Office 2019 (Windows 11 Pro)
    Posts
    47

    Re: How to activate a workbook with the variable name

    Thanks for everything,
    could you kindly find me a solution for this in VBA?
    I'm a little 'rusty .....Sorry
    Thank you

    Cattura.PNG


    Everything should be on another folder.
    Last edited by alpha45; 09-19-2018 at 01:59 PM.

  6. #6
    Forum Expert JLGWhiz's Avatar
    Join Date
    02-20-2011
    Location
    Florida, USA
    MS-Off Ver
    Windows 10, Excel 2013
    Posts
    2,070

    Re: How to activate a workbook with the variable name

    You should be able to handle that with a formula. Try putting this in cell G3 then drag it down as far as needed. You will need to change the range size if you have more data than at row 29.
    Please Login or Register  to view this content.
    Everything should be on another folder.
    What does this mean?

  7. #7
    Registered User
    Join Date
    09-22-2011
    Location
    Italy
    MS-Off Ver
    Office 2019 (Windows 11 Pro)
    Posts
    47
    Quote Originally Posted by JLGWhiz View Post
    You should be able to handle that with a formula. Try putting this in cell G3 then drag it down as far as needed. You will need to change the range size if you have more data than at row 29.
    Please Login or Register  to view this content.

    What does this mean?

    Che ogni giorno si apre una nuova cartella,
    per questo ho bisogno che sia in via grazie

    That every day opens a new folder,
    *for this I need it to be thanks

  8. #8
    Forum Expert JLGWhiz's Avatar
    Join Date
    02-20-2011
    Location
    Florida, USA
    MS-Off Ver
    Windows 10, Excel 2013
    Posts
    2,070

    Re: How to activate a workbook with the variable name

    It is not clear what you mean by
    find me a solution for this in VBA?
    I thought you wanted a method to sum values. The Workbooks.Open method will open a workbook. If you want VBA code to open a folder, you need to provide information about the folder.
    1. How is it created?
    2. What are the naming conventions?
    3. What will be the file path for saving and retrieving the file?
    4. Any other characteristics of the file that need to be included in the code.

  9. #9
    Registered User
    Join Date
    09-22-2011
    Location
    Italy
    MS-Off Ver
    Office 2019 (Windows 11 Pro)
    Posts
    47

    Re: How to activate a workbook with the variable name

    Hi,
    the speech is a bit 'complex: the workbook opens regularly and you can access without problems with this code;

    Please Login or Register  to view this content.
    Private Declare PtrSafe Function URLDownloadToFile Lib "urlmon" Alias _
    "URLDownloadToFileA" (ByVal pCaller As Long, ByVal szURL As String, ByVal _
    szFileName As String, ByVal dwReserved As Long, ByVal lpfnCB As Long) As Long
    Sub Aggiornamento()
    Dim n, isin, isin1, nc
    Dim Book As Workbooks
    Dim Name As String
    Set WShell = CreateObject("WScript.Shell")
    mydPath = WShell.SpecialFolders("Desktop")
    'Resp = URLDownloadToFile(0, "http://www.consob.it/documents/46180/46181/PncPubbl.xlsx/ddc5b7dc-b8a5-4d6c-aff5-0ce15e4109a9?l=" & Int(Rnd() * 1000000), mydPath & "\Pnc-" & Format(Now, "yyyy-mm-dd") & ".xlsx", 0, 0) '<<< Vedi Testo
    Set WShell = Nothing
    'aggiornamento dati
    'z = MsgBox(" Devi aggiornare i dati?", vbYesNo, " Aggiornamento dati")
    'z = Val(z)
    'If z = 7 Then Exit Sub

    ' selezione ultimo file"""
    'If nomecartella = mydPath & "\Pnc-" & Format(Now, "yyyy-mm-dd") & ".xlsx" Then GoTo salta
    ' contare numero operazioni
    Name = mydPath & "\Pnc-" & Format(Now, "yyyy-mm-dd") & ".xlsx"
    'Workbooks.Open FileName:=Name
    n = 3
    conta:
    'If Cells(n, 3) <> "" Then n = n + 1: Cells(n - 1, 7) = Cells(n - 1, 3): GoTo conta
    If Cells(n, 3) <> "" Then n = n + 1: GoTo conta
    n = n - 1

    isin = 3
    isin1 = isin + 1
    For isin1 = isin + 1 To n
    If Cells(isin, 3) <> Cells(isin1, 3) Then Cells(isin1 - 1, 7) = Cells(isin1 - 1, 4)
    If Cells(isin, 3) = Cells(isin1, 3) Then Cells(isin1, 7) = Cells(isin1, 4) + Cells(isin, 4) + Cells(isin1, 7): isin1 = isin1 + 1: isin = isin + 1
    If Cells(isin, 3) = Cells(isin1, 3) Then Cells(isin1, 7) = Cells(isin1, 4) + Cells(isin1 - 1, 7): Cells(isin1 - 1, 7) = "": isin1 = isin1 + 1: isin = isin + 1
    If Cells(isin, 3) = Cells(isin1, 3) Then Cells(isin1, 7) = Cells(isin1, 4) + Cells(isin1 - 1, 7): Cells(isin1 - 1, 7) = "": isin1 = isin1 + 1: isin = isin + 1
    If Cells(isin, 3) = Cells(isin1, 3) Then Cells(isin1, 7) = Cells(isin1, 4) + Cells(isin1 - 1, 7): Cells(isin1 - 1, 7) = "": isin1 = isin1 + 1: isin = isin + 1
    If Cells(isin, 3) = Cells(isin1, 3) Then Cells(isin1, 7) = Cells(isin1, 4) + Cells(isin1 - 1, 7): Cells(isin1 - 1, 7) = "": isin1 = isin1 + 1: isin = isin + 1
    If Cells(isin, 3) = Cells(isin1, 3) Then Cells(isin1, 7) = Cells(isin1, 4) + Cells(isin1 - 1, 7): Cells(isin1 - 1, 7) = "": isin1 = isin1 + 1: isin = isin + 1
    If Cells(isin, 3) = Cells(isin1, 3) Then Cells(isin1, 7) = Cells(isin1, 4) + Cells(isin1 - 1, 7): Cells(isin1 - 1, 7) = "": isin1 = isin1 + 1: isin = isin + 1
    If Cells(isin, 3) = Cells(isin1, 3) Then Cells(isin1, 7) = Cells(isin1, 4) + Cells(isin1 - 1, 7): Cells(isin1 - 1, 7) = "": isin1 = isin1 + 1: isin = isin + 1
    If Cells(isin, 3) = Cells(isin1, 3) Then Cells(isin1, 7) = Cells(isin1, 4) + Cells(isin1 - 1, 7): Cells(isin1 - 1, 7) = "": isin1 = isin1 + 1: isin = isin + 1
    If Cells(isin, 3) = Cells(isin1, 3) Then Cells(isin1, 7) = Cells(isin1, 4) + Cells(isin1 - 1, 7): Cells(isin1 - 1, 7) = "": isin1 = isin1 + 1: isin = isin + 1
    If Cells(isin, 3) = Cells(isin1, 3) Then Cells(isin1, 7) = Cells(isin1, 4) + Cells(isin1 - 1, 7): Cells(isin1 - 1, 7) = "": isin1 = isin1 + 1: isin = isin + 1
    If Cells(isin, 3) = Cells(isin1, 3) Then Cells(isin1, 7) = Cells(isin1, 4) + Cells(isin1 - 1, 7): Cells(isin1 - 1, 7) = "": isin1 = isin1 + 1: isin = isin + 1
    If Cells(isin, 3) = Cells(isin1, 3) Then Cells(isin1, 7) = Cells(isin1, 4) + Cells(isin1 - 1, 7): Cells(isin1 - 1, 7) = "": isin1 = isin1 + 1: isin = isin + 1
    If Cells(isin, 3) = Cells(isin1, 3) Then Cells(isin1, 7) = Cells(isin1, 4) + Cells(isin1 - 1, 7): Cells(isin1 - 1, 7) = "": isin1 = isin1 + 1: isin = isin + 1
    If Cells(isin, 3) = Cells(isin1, 3) Then Cells(isin1, 7) = Cells(isin1, 4) + Cells(isin1 - 1, 7): Cells(isin1 - 1, 7) = "": isin1 = isin1 + 1: isin = isin + 1
    isin = isin + 1
    Next isin1

    'prosegui
    Stop
    [/CODE]

    The difficulty in which I find myself is: add the values of the single and multiple IDs of column C keeping them matched with the first names (as described on the first attachment), and update the page attached sheet (es).
    Also I would like to have the possibility to insert in the new sheet (es) any new id with relative values (sums) and name keeping in alphabetical order and using macro ...
    Keep in mind that every day the scanned folder (Pnc ....) changes, so you can not do operations on the same sheet and the sheet in question is only sheet 1, the other 2 are not needed.
    Thank you in advance for your collaboration ..
    Hello

    Cattura1.PNG
    Last edited by alpha45; 09-20-2018 at 08:15 AM. Reason: risolto parzialmente

  10. #10
    Forum Expert JLGWhiz's Avatar
    Join Date
    02-20-2011
    Location
    Florida, USA
    MS-Off Ver
    Windows 10, Excel 2013
    Posts
    2,070

    Re: How to activate a workbook with the variable name

    I am going to drop off this thread because that type of programming is above my expertise. Perhaps someone with more experience in web interface programming can be of help.
    Regards, JLG

  11. #11
    Registered User
    Join Date
    09-22-2011
    Location
    Italy
    MS-Off Ver
    Office 2019 (Windows 11 Pro)
    Posts
    47

    Re: How to activate a workbook with the variable name

    Tank You

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Using string variable within Windows().Activate
    By Bozo in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 06-23-2017, 01:31 PM
  2. Trying to activate an open Workbook with a variable
    By msf013 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-22-2014, 01:53 PM
  3. How do I activate a workbook using a global variable?
    By Erikje in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-05-2013, 04:51 PM
  4. [SOLVED] Workbook.Activate / Window.Activate problem
    By Tim in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-03-2006, 07:40 PM
  5. [SOLVED] Can a variable be used in a Worksheet().Activate method
    By Brad Sumner in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-12-2005, 03:05 PM
  6. Activate Variable Named Workbooks
    By HelpMe in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-20-2005, 11:17 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1