Results 1 to 10 of 10
  1. #1
    diegomarino is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Feb 2018
    Posts
    408

    Make an excel from an ms edge web select

    hi,
    i'm making some excel files from ms edge web select, datas have a table structurle ike the one below

    Click image for larger version. 

Name:	web sel.png 
Views:	17 
Size:	6.3 KB 
ID:	48137

    obviously very often there are more rows

    i'm enough good in working with excel from ms access, but i'm wondering if there i a way to



    1) make a web selection with Edge
    2) click on a button in a form and create an excel with the data i selected.

    then, i'm able to save the file with the name i want and where i want

    thanks

  2. #2
    diegomarino is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Feb 2018
    Posts
    408
    got it!

    Code:
    Public Function CreaFileOCF(Nome As String, Cognome As String)   Dim xlx As Object, xlw As Object, xls As Object, xlc As Object
    
    
       On Error Resume Next
       Set xlx = GetObject(, "Excel.application")
       If Err.Number <> 0 Then
          Set xlx = CreateObject("Excel.application")
       End If
       Err.Clear
       On Error GoTo 0
       xlx.Visible = False ' se setto vero mi apre il file excel in questione
       Workbooks.Add
       
       ActiveWorkbook.SaveAs "C:\Users\diego\OneDrive - Indipendente\Lavoro\HH\Candidati\Z-Aggiornamenti ocf\" & Nome & "-" & Cognome
       Set xlw = xlx.Workbooks.Open("C:\Users\diego\OneDrive - Indipendente\Lavoro\HH\Candidati\Z-Aggiornamenti ocf\" & Nome & "-" & Cognome)
       Set xls = xlw.worksheets(1)
       Set xlc = xls.Range("a1")
       xlc.Value = Paste_from_Clipboard
    
    
       xlw.Save
       xlw.Close
       Set xlc = Nothing
       Set xls = Nothing
       Set xlw = Nothing
       xlx.Quit ' se esiste l'applicazione excel la chiude
       Set xlx = Nothing
    
    
    End Function
    Code:
    Public Function Paste_from_Clipboard() As String      
         Dim CObj As MSForms.DataObject
         Set CObj = New MSForms.DataObject
         CObj.GetFromClipboard
         Debug.Print (CObj.GetText(1))
         Paste_from_Clipboard = CObj.GetText(1)
         
    End Function
    the only problem is that the edge selection gives this output:
    Soggetto Data Inizio Data Fine

    ALLIANZ BANK FINANCIAL ADVISORS SPA 24/08/2015

    it is a single string, i need to have separated values table-like. Is it possible?

  3. #3
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    If you know the column delimiter you can use the split function to create an array then parse the array and paste each element to a cell

  4. #4
    diegomarino is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Feb 2018
    Posts
    408
    thanks, i was thinking the same. but the delimiter is this strange arrow

    Click image for larger version. 

Name:	delim.png 
Views:	15 
Size:	1.5 KB 
ID:	48141

    that makes in word a table
    Click image for larger version. 

Name:	delim1.png 
Views:	15 
Size:	5.9 KB 
ID:	48142

  5. #5
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    You can use this to determine the ascii codes of characters in a string.

    Code:
    Public Function Parseit(Arg As String)
        Dim i As Integer, s As String
        Dim LL As Integer
        LL = Len(Arg)
        For i = 1 To LL
            s = Mid(Arg, i, 1)
            'Debug.Print s & " " & Asc(Mid(Arg, i, 1))
            Debug.Print s & " " & Asc(s) & " " & Chr(Asc(s))
        Next
        
    End Function
    Let's say your funny arrow has ascii code of 123.
    Then specify chr(123) as your delimiter in the split function.

    If other unexpected characters show up, you could use the Replace function to change them to space or whatever before doing the split.
    Last edited by davegri; 06-28-2022 at 08:53 AM. Reason: added what to do with result

  6. #6
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    agree with dave.

    to find the delimiter have a temporary function to scan through the string to write out the ascii number

    for i=1 to len(str)
    debug.print asc(mid(str,i,1)
    next i

    in your example looks like it is the 10th char - but be aware some chars are made up of 2 ascii codes (such as '' used to produce a smiley face in html, so you could use for i=1 to 20 just to capture the chars either side


    The other possibility (try it, it may work) is to use the plaintext function - along the lines of

    stringArr=split(plaintext(CObj.GetText(1)),vbcrlf)

  7. #7
    diegomarino is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Feb 2018
    Posts
    408
    thanks, it is the right solution. however i have a problem

    thisi is my code
    Code:
       Arr = Split(Paste_from_Clipboard, Chr(10))   For Counter = LBound(Arr) To UBound(Arr)
          Debug.Print (Arr(Counter))
          Arr2 = Split(Arr(Counter), Chr(9))
          For Counter2 = LBound(Arr2) To UBound(Arr2)
                   Debug.Print (Arr(Counter2))
             xlc.Offset(Rw, Col).Value = Arr2(Counter)
             Col = Col + 1
    
    
            
          Next
          Col = 0
          
       Next
    i split first to the ascii 10 that's the line break, so i have:

    0) Soggetto Data Inizio Data Fine
    1)BANCA INTERMOBILIARE DI INVESTIMENTI E GESTIONI SPA 03/02/2015 31/12/2015
    2)BANCA INTERMOBILIARE DI INVESTIMENTI E GESTIONI SPA 01/10/2017 07/03/2022
    3)BANCA INVESTIS SPA GI? BANCA INTERMOBILIARE DI INVESTIMENTI E GESTIONI SPA 08/03/2022 04/05/2022
    4)BANCA INVESTIS SPA BANCA INTERMOBILIARE DI INVESTIMENTI E GESTIONI SPA 05/05/2022

    then i make another array splitted by ascii 9 that's the horizontal tab, but when i debug the first arr2(counter) i get:

    Soggetto Data Inizio Data Fine

    that should be splitted in
    0)soggetto
    1) data inizio
    2) data fine.


    i'm not able to understand where is the error

  8. #8
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    As mentioned, use the replace function to clean up your string before the split. You may need to use the replace twice or more if needed.

  9. #9
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    looks like the first line is just headings so you should just be able to ignore them and move on the the 2nd element

  10. #10
    diegomarino is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Feb 2018
    Posts
    408
    made it! there were some counter errors too i guess.

    Code:
    Public Function CreaFileOCF(Nome As String, Cognome As String)   Dim xlx As Object, xlw As Object, xls As Object, xlc As Object
       Dim Arr() As String
       Dim Arr2() As String
       Dim Counter As Long
       Dim Counter2 As Long
       Dim Col As Long
       Dim Rw As Long
       
       Counter = 0
       Counter2 = 0
       Col = 0
       Rw = 0
       On Error Resume Next
       Set xlx = GetObject(, "Excel.application")
       If Err.Number <> 0 Then
          Set xlx = CreateObject("Excel.application")
       End If
       Err.Clear
       On Error GoTo 0
       xlx.Visible = False ' se setto vero mi apre il file excel in questione
       Workbooks.Add
       
       ActiveWorkbook.SaveAs "C:\Users\diego\OneDrive - Indipendente\Lavoro\HH\Candidati\Z-Aggiornamenti ocf\" & Nome & "-" & Cognome
       Set xlw = xlx.Workbooks.Open("C:\Users\diego\OneDrive - Indipendente\Lavoro\HH\Candidati\Z-Aggiornamenti ocf\" & Nome & "-" & Cognome)
       Set xls = xlw.worksheets(1)
       Set xlc = xls.Range("a1")
       Arr = Split(Paste_from_Clipboard, Chr(10))
       For Counter = LBound(Arr) To UBound(Arr)
          Debug.Print (Replace(Arr(Counter), Chr(13), ""))
          Arr2 = Split(Replace(Arr(Counter), Chr(13), ""), Chr(9))
          For Counter2 = LBound(Arr2) To UBound(Arr2)
                   Debug.Print (Arr2(Counter2))
             xlc.Offset(Rw, Col).Value = Arr2(Counter2)
             Col = Col + 1
    
    
            
          Next
          Col = 0
          Rw = Rw + 1
          
       Next
    
    
       xlw.Save
       xlw.Close
       Set xlc = Nothing
       Set xls = Nothing
       Set xlw = Nothing
       xlx.Quit ' se esiste l'applicazione excel la chiude
       Set xlx = Nothing
    
    
    End Function

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 32
    Last Post: 04-20-2022, 09:44 AM
  2. Replies: 1
    Last Post: 04-06-2020, 02:52 AM
  3. Microsoft Edge and this forum
    By gem1204 in forum Access
    Replies: 3
    Last Post: 04-07-2016, 06:20 PM
  4. Replies: 2
    Last Post: 02-06-2016, 09:25 PM
  5. Replies: 31
    Last Post: 06-19-2012, 03:50 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums