Results 1 to 22 of 22

VBA OneDrive issues

Threaded View

  1. #1
    Registered User
    Join Date
    01-02-2024
    Location
    Mass
    MS-Off Ver
    2021
    Posts
    12

    VBA OneDrive issues

    Hello all,

    My company just migrated over to One Drive from a corporate shared drive, our inventory spreadsheet would use ThisWorkbook.path to save a copy each time someone would email it, now with One Drive using an HTTP protocol I get the run time error. Would someone please help me with what I need to change up or insert in my code?
    Thanks ahead of time.

    Administrator's note: Please take the time to review our rules. There aren't many, and they are all important. Our guidelines recommend code tags. I have added them for you this time because you are a new member. --6StringJazzer
    Sub Send_Outlook_Albany()
    '>>>>>>>>>>>>>>>
    
    'setup for Outlook 3/5/16
    
    '<<<<<<<<<<<<<<
    Dim wb, Wbsf As Workbook
    Dim ans, FileVer As Integer
    Dim wbsf1, suggname, salesdate, salesdate1, saveformat As String
    Dim ExisFile, VerStr As String
    Dim FileExtStr As String
    Dim FileFormatNum As Long
    Dim Terminal As String
    Dim I As Long
                        
        Dim OutApp As Object
        Dim OutMail As Object
    
        Set OutApp = CreateObject("Outlook.Application")
        Set OutMail = OutApp.CreateItem(0)
                        
     
    Terminal = "Tampa"
    
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationAutomatic
    saveformat = Application.DefaultSaveFormat
     
                         ans = MsgBox("This will Email a copy of this report via OUTLOOK" & vbCrLf & vbCrLf & _
                        "OK?", vbYesNoCancel + vbQuestion)
                        If ans = 2 Then Exit Sub
                        If ans = 7 Then MsgBox "Try again": Exit Sub
                        
                        
    FileExtStr = ".xlsx": FileFormatNum = 51
    
    FileVer = 1
    salesdate = Sheets("Tank Report").Range("h4").Text
    salesdate1 = Application.Text(salesdate, "mm-dd-yyyy")
    VerStr = " (" & FileVer & ")"
    suggname = Terminal & " Inventory Report " & salesdate1 & VerStr & FileExtStr
            
    '>>>>>>>>>>
    'Copies File over to new workbook
    'use for Emailing via Notes
    Set wb = ActiveWorkbook
    Set Wbsf = Workbooks.Add
    Set Wbsf = ActiveWorkbook
    wbsf1 = suggname
    
    
    ' Checks to see if file exists ...
    'Add Digit to version number
    Do While Dir(ThisWorkbook.Path & "" & wbsf1) <> ""
    
    ExisFile = Dir(ThisWorkbook.Path & "" & wbsf1)
    FileVer = Mid(ExisFile, InStr(ExisFile, "(") + 1, 1)
    FileVer = FileVer + 1
    VerStr = " (" & FileVer & ")"
    suggname = Terminal & " Inventory Report " & salesdate1 & VerStr & FileExtStr
    wbsf1 = suggname
    'MsgBox FileVer
    'MsgBox "OOPS File Exists", vbOKCancel + vbQuestion
    
    Loop
    
    On Error GoTo Errorhandler1
    
    ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "" & wbsf1, FileFormat:=FileFormatNum
    
    wbsf1 = Wbsf.Name
                        If Val(Application.Version) > 14 Then
                                'You use Excel 2013 or higher
                            With Wbsf
                            Sheets.Add After:=ActiveSheet
                            Sheets.Add After:=ActiveSheet
                            End With
                        Else
                                'You use Excel 2010 or lower
                        End If
    With wb
        .Activate
        .Sheets("Tank Report").Activate
        Range("a1:r57").Select
       .Sheets(Array("Tank Report", "Inventory", "Status")).Select
        Selection.Copy
    End With
    
    With Wbsf.Sheets("Sheet1").Range("a1")
        .PasteSpecial xlPasteAllUsingSourceTheme
        '.PasteSpecial xlPasteAll
        .PasteSpecial xlPasteColumnWidths
        .PasteSpecial xlPasteValues
    End With
     
     
     'Get a Global logo. Check for name
        wb.Sheets("vessel report").Activate
        ActiveSheet.Shapes("Picture 199").Select
        Selection.Copy
        
        
      
        Wbsf.Sheets("sheet3").Activate
        ActiveWindow.DisplayGridlines = False
        Wbsf.Sheets("sheet2").Activate
        ActiveWindow.DisplayGridlines = False
        Wbsf.Sheets("sheet1").Activate
        ActiveWindow.DisplayGridlines = False
       
        'Paste Global Logo
        Wbsf.Sheets("Sheet1").Range("a1").Select
        ActiveSheet.Paste
        Wbsf.Sheets("sheet3").Activate
        Wbsf.Sheets("Sheet3").Range("a1").Select
        ActiveSheet.Paste
        Wbsf.Sheets("sheet2").Activate
        Wbsf.Sheets("Sheet2").Range("a1").Select
        ActiveSheet.Paste
    
    
    'Resets Original Workbook
    With wb
        .Sheets("Status").Activate
        .Sheets("Status").Select
        Range("a1").Select
        .Sheets("Tank Report").Activate
        .Sheets("Tank Report").Select
        Range("c10").Select
        .Sheets("Rate Report").Activate
        .Sheets("Rate Report").Select
        Range("D3").Select
        .Sheets("Vessel Report").Activate
        .Sheets("Vessel Report").Select
        Range("C5").Select
        .Sheets("Inventory").Select
    End With
    Application.CutCopyMode = False
    
    
    ' sets up the send file
    With Wbsf
        .Sheets("sheet1").Name = "Tank Report"
        .Sheets("sheet2").Name = "Inventory"
        .Sheets("sheet3").Name = "Status"
    
        
       ' activate and set print area
        .Sheets("Tank Report").Activate
        .Sheets("Tank Report").Select
           With ActiveSheet.PageSetup
            .PrintArea = "A1:R57"
            .LeftMargin = Application.InchesToPoints(0.5)
            .RightMargin = Application.InchesToPoints(0.5)
            .TopMargin = Application.InchesToPoints(0.5)
            .BottomMargin = Application.InchesToPoints(0.5)
            .HeaderMargin = Application.InchesToPoints(0.3)
            .FooterMargin = Application.InchesToPoints(0.3)
            .CenterHorizontally = True
            .CenterVertically = False
            .Orientation = xlLandscape
            .PaperSize = xlPaperLetter
            .FirstPageNumber = xlAutomatic
            .Zoom = False
            .FitToPagesWide = 1
            .FitToPagesTall = 1
            .PrintGridlines = False
           End With
           .Sheets("Tank Report").Range("a1").Select
        
        'activate and set print area
        .Sheets("Inventory").Activate
        .Sheets("Inventory").Select
           With ActiveSheet.PageSetup
            .PrintArea = "A1:p35"
            .LeftMargin = Application.InchesToPoints(0.5)
            .RightMargin = Application.InchesToPoints(0.5)
            .TopMargin = Application.InchesToPoints(0.5)
            .BottomMargin = Application.InchesToPoints(0.5)
            .HeaderMargin = Application.InchesToPoints(0.3)
            .FooterMargin = Application.InchesToPoints(0.3)
            .CenterHorizontally = True
            .CenterVertically = False
            .Orientation = xlLandscape
            .PaperSize = xlPaperLetter
            .FirstPageNumber = xlAutomatic
            .Zoom = False
            .FitToPagesWide = 1
            .FitToPagesTall = 1
            .PrintGridlines = False
           End With
           .Sheets("Inventory").Range("a1").Select
        
        'activate and set print area
        .Sheets("Status").Activate
        .Sheets("Status").Select
           With ActiveSheet.PageSetup
            .PrintArea = "A1:i42"
            .LeftMargin = Application.InchesToPoints(0.5)
            .RightMargin = Application.InchesToPoints(0.5)
            .TopMargin = Application.InchesToPoints(0.5)
            .BottomMargin = Application.InchesToPoints(0.5)
            .HeaderMargin = Application.InchesToPoints(0.3)
            .FooterMargin = Application.InchesToPoints(0.3)
            .CenterHorizontally = True
            .CenterVertically = False
            .Orientation = xlLandscape
            .PaperSize = xlPaperLetter
            .FirstPageNumber = xlAutomatic
            .Zoom = False
            .FitToPagesWide = 1
            .FitToPagesTall = 1
            .PrintGridlines = False
           End With
           .Sheets("Status").Range("a1").Select
      
              
        .Sheets("Inventory").Activate
        .Sheets("Inventory").Range("a1").Select
    
    End With
        
    ActiveWorkbook.Save
    
    
    '<<<<<<<<<<<MAIL it
    
    
    Application.CutCopyMode = False
    Last edited by 6StringJazzer; 01-02-2024 at 07:42 PM.

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Issues Enabling Macros in a Shared Excel Workbook on OneDrive
    By fdo.andrade1 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-04-2023, 11:08 PM
  2. [SOLVED] How do I copy over data to OneDrive but not copy specific data if not in OneDrive
    By tweacle in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-10-2022, 01:23 PM
  3. Replies: 1
    Last Post: 05-10-2021, 11:21 AM
  4. [SOLVED] Saving a file to onedrive shared network and access via onedrive online
    By Sintek in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 03-09-2021, 11:56 AM
  5. Save to OneDrive and Upload from OneDrive Link
    By GOrtega in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-07-2020, 08:35 PM
  6. [SOLVED] Excel to Outlook Calendar date duplication issues and blank cells causing issues
    By singerbatfink in forum Outlook Formatting & Functions
    Replies: 0
    Last Post: 02-11-2016, 07:57 AM
  7. Replies: 3
    Last Post: 07-16-2014, 01:50 AM

Tags for this Thread

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