Results 1 to 12 of 12
  1. #1
    lonesoac0 is offline Advanced Beginner
    Windows 7 32bit Access 2013
    Join Date
    Aug 2014
    Posts
    36

    Keeps asking for an equals sign....

    Hello all,



    I am trying to run the function of:
    Code:
    DoCmd.TransferSpreadsheet(acImport,acSpreadsheetTypeExcel12Xml, "ServiceNow", fileName, True, "A1:F12")
    but I keep getting the error code of: Compile error: Expected: =. Um, last time I checked I was trying to import data with this thing and it should not be asking for an = sign. Below is the basic of my code and results of my code. The selectFile is a function that opens a file dialog so that I can point the code to the correct file every time. The rangecount seems to work as well.

    Click image for larger version. 

Name:	Capture.PNG 
Views:	24 
Size:	31.8 KB 
ID:	39493

    Code:
    Sub SelectFileToImport()fileName = selectFile
    Debug.Print fileName
    'open excel and read record
    Dim myRec As DAO.Recordset
    Dim xlapp As Excel.Application
    Dim xlwrksht As Excel.worksheet
    Set myRec = CurrentDb.OpenRecordset("ServiceNow")
    Set xlapp = CreateObject("Excel.application")
    Set xlWrkBk = GetObject(fileName)
    Set xlwrksht = xlWrkBk.Worksheets(1)
    rangecount = xlwrksht.Cells(xlwrksht.Rows.Count, 1).End(xlUp).Row
    
    
    DoCmd.TransferSpreadsheet(acImport,acSpreadsheetTypeExcel12Xml, "ServiceNow", fileName, True, "A1:F11")

  2. #2
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    2 possibilities- filename is not a string variable (I can't see where it's declared; must be at module level)
    More likely is the spaces in your filename. In that case, I believe the entire string must be quoted so you will have to concatenate the variable or do so in the transfer line of code. I never have spaces in file or folder names unless maybe if they will have anything to do with programming so I'm not 100% certain of #2.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Don't use parentheses. VBA expects a function when used. Function must be set equal to something.

    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "ServiceNow", fileName, True, "A1:F11"
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Likely you solved the issue - good catch, but I disagree with this statement
    Function must be set equal to something.
    Function DoStuff
    msgbox "hello"
    End Function

    and to call it

    DoStuff

    This will work, yes? But there is no = to anything?

  5. #5
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    @micron - that's a function, not a command

  6. #6
    accesstos's Avatar
    accesstos is offline Expert
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    551
    The correct is:
    Function must be set equal to something, when parentheses are are used.

  7. #7
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    @Ajax - I re-read posts 3 and 4 but seem to be missing your point. If you're referring to "DoStuff" it is a function call without the word call AFAIK. I'm missing the part where anyone mentioned a command?
    P.S. I agree that if parentheses are used around the arguments in a function call, it must be set to be equal to something.
    Also, if the Call keyword is used, the arguments must be enclosed - if there are any. Oddly enough, if there are none, parentheses are not required if using Call.

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Okay, "often" ?

    A function that returns a value must be set equal to something.

    Like Date() - it must go somewhere.

    MsgBox("hello", vbYesNo) must be set equal to something because a value is returned.

    Point is, parens are not appropriate for TransferSpreadsheet command.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I'm missing the part where anyone mentioned a command?
    it's what the OP is using

    actually

    myfunction(x)

    is perfectly valid code - just a waste of time because the returned value is not assigned to anything. You can just as easily write

    5


    as valid code, but it doesn't get you anywhere

    "mystring" on the other hand will generate a compilation error

  10. #10
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Well, simply

    MsgBox("hello", vbYesNo)

    errors therefore it is not valid code.

    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  11. #11
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I don't think anyone said otherwise, least of all me.

  12. #12
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Oh, I misunderstood. By MyFunction, Ajax meant a UDF, not an intrinsic function. Got it.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. Replies: 2
    Last Post: 06-30-2019, 01:47 PM
  2. First Row Equals Header Problem
    By wcrimi in forum Access
    Replies: 4
    Last Post: 11-11-2014, 01:25 PM
  3. Sign Out and Sign In Student Database
    By jamiers in forum Forms
    Replies: 6
    Last Post: 08-29-2012, 02:03 PM
  4. Return rows where value equals drop down
    By AccessLiability in forum Forms
    Replies: 1
    Last Post: 07-02-2012, 07:15 AM
  5. Replies: 35
    Last Post: 09-19-2011, 10:13 AM

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