This is a weird one - may not be a great solution to it - wondering if anyone has any insight into this..
I have a database that uses an Excel file as a linked table. I have to use the Excel file as a data source as this information is sent from a vendor to us. The Excel file has column A that has nothing in it, ever. I'm probably not going to be able to get the vendor to do anything about that.
I have queries built upon the linked table. The practice has been to receive the email with the Excel file and to place the Excel file in a specific location and name the file the same so Access can utilize the link. For some reason (that I don't quite understand) sometimes the link table includes the blank column A and other times it does not. This, of course, is playing havoc with the queries as the data is shifting around based on if column A is there or not there. It also seems to be the case that if one person opens the database then the linked table might include column A but if another person opens it then the linked table might not have column A.
As a workaround I am asking the user that receives the Excel file to open it and put something (anything) into any cell in column A and then save the file to the location where it lives.
Maybe I just need to change it from a link to an import and use macros or VBA to import the data when clicking a button, etc.
Wondering if anyone has dealt with this before.
I have a database that uses an Excel file as a linked table. I have to use the Excel file as a data source as this information is sent from a vendor to us. The Excel file has column A that has nothing in it, ever. I'm probably not going to be able to get the vendor to do anything about that.
I have queries built upon the linked table. The practice has been to receive the email with the Excel file and to place the Excel file in a specific location and name the file the same so Access can utilize the link. For some reason (that I don't quite understand) sometimes the link table includes the blank column A and other times it does not. This, of course, is playing havoc with the queries as the data is shifting around based on if column A is there or not there. It also seems to be the case that if one person opens the database then the linked table might include column A but if another person opens it then the linked table might not have column A.
As a workaround I am asking the user that receives the Excel file to open it and put something (anything) into any cell in column A and then save the file to the location where it lives.
Maybe I just need to change it from a link to an import and use macros or VBA to import the data when clicking a button, etc.
Wondering if anyone has dealt with this before.