Results 1 to 7 of 7
  1. #1
    JRodko is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Oct 2018
    Posts
    127

    Dlookup in excel spreadsheet export.

    Hello, I am using a macro and the ImportExportSpreadsheet functions to export the results of a query. I am trying to make the filename either manually entered via prompt, or from a table record using DLookup. This is what I have however access does not let me save because it tells me the File Name is invalid.

    ="C:\...Filename_" & "DLookup("[FieldName]", "tblTableName", "[ID] = 1" & ".xlsx"

    Any help would be appreciated. If it is easier to replace the Dlookup with a prompt to enter something custom, that would work perfect as well.

    Thank you

  2. #2
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,859
    You are not closing the Dlookup function?
    I would do it in two stages.
    Get your Dlookup value into a string variable.
    Use said variable in the path.

    That way you can also Debug.print it to see if it is correct.

    Can't even see that compiling?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  3. #3
    JRodko is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Oct 2018
    Posts
    127
    Hi Wlshgasman,

    This is not in VBA, but rather in the macro builder. I'm not sure that I know how to do what you are suggesting.

  4. #4
    JRodko is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Oct 2018
    Posts
    127
    Click image for larger version. 

Name:	Capture.JPG 
Views:	13 
Size:	38.2 KB 
ID:	43967Some additional context

  5. #5
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,859
    I have hardly ever used macroes for this very reason.
    I have just tried with a Tempvar and that works.

    I only have 2007 so have to do it in pictures.?

    Do yourself a favour, start learning VBA, it will benefit you so much more.

    The echo was just so I could see if it was the correct value.

    HTH anyway.
    Attached Thumbnails Attached Thumbnails Tempvar.PNG   Transfer.PNG  
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  6. #6
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I can't see how that macro knows what the cross out field reference is. Maybe it would work if you included the form reference - don't know, don't use macros.
    I also can't see how that is a representation of your DLookup in your first post, which by the way, is prefaced with a not needed ": & "DLookup
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  7. #7
    JRodko is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Oct 2018
    Posts
    127
    Welshgasman hooked me up. Thank you to both of you!

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

Similar Threads

  1. Export Form's Record Source To Excel Spreadsheet
    By Bcanfield83 in forum Forms
    Replies: 1
    Last Post: 04-18-2019, 03:18 PM
  2. Export Data to an Existing Excel Spreadsheet
    By wcrimi in forum Import/Export Data
    Replies: 16
    Last Post: 09-09-2018, 03:50 PM
  3. Export to spreadsheet with no Excel present
    By jabarlee in forum Import/Export Data
    Replies: 4
    Last Post: 08-14-2018, 02:37 PM
  4. Automate Export of a table to an Excel Spreadsheet
    By RayMilhon in forum Programming
    Replies: 3
    Last Post: 01-23-2018, 08:08 PM
  5. Replies: 1
    Last Post: 08-12-2010, 10:04 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