Results 1 to 3 of 3
  1. #1
    Bcanfield83 is offline Advanced Beginner
    Windows 7 32bit Access 2016
    Join Date
    May 2018
    Posts
    81

    Question Strange behavior in Access VBA Procedure (DoCmd.TransferSpreadsheet)

    In my DB, I have a Module called "modImportFiles" which consists of several sub-procedures that utilize the "DoCmd.TransferSpreadsheet acImport" function to import a .XLSX spreadsheet into a specified table in the DB.
    The .xlsx files are located in various folders on a network drive - though 95% of the folder path is identical for the files.
    Thus, I just recently updated the module to declare a bunch of variables at the Module Level instead of declaring them in each Sub-Procedure since they are used across all Sub-Procedures in this Module.



    What's strange is that when I step through a couple of the sub-procedures, I've noticed that the specified ".xlsx" spreadsheet is now opening up (as Read Only) in the database after the DoCmd.TransferSpreadsheet line is finished.
    The spreadsheet is obviously supposed to be imported into the database, but I'm not sure why Access is now opening the spreadsheet as well. I didn't change anything other than declaring the variables at the module level.
    In theory it should just be importing the file into the specified table, correct? Not also opening the file up

    Example - these are the module level variables that I'm declaring
    Code:
    Option Compare Database
    Dim strPriorMonth, strPriorMonthName, strYear, strMonthYear, strMontYear15 As String
    Dim strFilePath As String
    Dim strFileLoc As String
    Dim strFileName As String
    Dim strImportTable As String
    Dim strDataOwnerEDS As String
    Code:
    strPriorMonth = (DateSerial(Year(Date), Month(Date), 1)) - 1
    strPriorMonthName = Format(strPriorMonth, "MMMM")
    strYear = Year(strPriorMonth)
    strMonthYear = strPriorMonthName & Space(1) & strYear
    strMontYear15 = strPriorMonthName & Space(1) & "15" & Space(1) & strYear
    
    
    strDataOwnerEDS = "bsmith"
    strImportTable = "tblbsmith_Import"
    
    
    strFilePath = "\\corpnt01\global_sh\PROJECTS\Security Program\Access Reviews\" & strMonthYear & "\Reports\" & strDataOwnerEDS & "\"
    strFileName = strDataOwnerEDS & "_" & strMonthYear & ".xlsx"
    
    
    strFileLoc = strFilePathEDS & strFileName
    strFileLocAlt = strFilePathEDSAlt & strFileName
    
    
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "qryDelete_Current_Records"
    
    
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, strImportTable, strFileLoc, True, "Owner_Users!"

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Right under your

    Option Compare Database ALWAYS include
    Option Explicit

    Also, this
    Code:
    Dim strPriorMonth, strPriorMonthName, strYear, strMonthYear, strMontYear15 As String
    does Not do what you think. With Access/vba you must explicitly dim each and every variable, otherwise
    they are variant datatype.

    Some examples:
    Dim a as string
    Dim b as string, c as string, d as long, Dte as Date
    Dim i as Integer

  3. #3
    Bcanfield83 is offline Advanced Beginner
    Windows 7 32bit Access 2016
    Join Date
    May 2018
    Posts
    81
    Ah, good to know - thank you.

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

Similar Threads

  1. Strange behavior with Conditional Formatting
    By GraeagleBill in forum Forms
    Replies: 15
    Last Post: 07-13-2018, 04:33 PM
  2. Replies: 6
    Last Post: 10-19-2016, 04:33 PM
  3. Strange TransferSpreadsheet Behavior
    By JoeM in forum Programming
    Replies: 6
    Last Post: 08-05-2015, 07:47 AM
  4. Strange Behavior on Startup
    By RonL in forum Programming
    Replies: 3
    Last Post: 02-14-2013, 03:31 PM
  5. Strange Behavior when Sorting
    By geniass in forum Queries
    Replies: 5
    Last Post: 09-02-2010, 03:53 PM

Tags for this Thread

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