+ Reply to Thread
Results 1 to 22 of 22

VBA OneDrive issues

Hybrid View

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

    Re: VBA OneDrive issues

    I've attached the entire code that I have, when I run it and it hits (Do While Dir(ThisWorkbook.Path & "" & wbsf1) <> "") I get the error, I only assume this is because of ThisWorkbook.Path gives back a URL for OneDrive. I've tried a bunch of different options all giving me the same results. I can't hardcode a path because it will be used by a host of users all using different OneDirve or SharePoint per facility.


    Option Explicit
    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
    
     On Error Resume Next
        With OutMail
            .To = ""
            .CC = ""
            .BCC = ""
            .Subject = wbsf1
            .Body = " Attached is the file - " & wbsf1
            .Attachments.Add Wbsf.FullName
            .Display '.send
        End With
        On Error GoTo 0
    
    
    With Wbsf
              .Close SaveChanges:=False
    End With
    
                     
                       Set OutApp = Nothing
                       Set OutMail = Nothing
                       
                       
    With wb.Sheets("Inventory")
        .Activate
        .Select
        .Range("a1").Select
    End With
     
    'Set DefaultSaveFormat back to the users setting
    Application.DefaultSaveFormat = saveformat
    
    With Application
            .ScreenUpdating = True
            .DisplayAlerts = True
    End With
    
    Exit Sub
    Errorhandler1:
    
    With Application
            .ScreenUpdating = True
            .DisplayAlerts = True
    End With
    
    End Sub
    Tampa inventory 1.7.24 sales date. Folio 5.xlsm
    Last edited by jayettar; 01-08-2024 at 05:52 PM.

+ 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. 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