Dot i've got an example set up to demonstrate how this works i've got a macro enabled workbook with some code that lets us select data from a separate.
Excel file called movies i've got a couple of column headings in here which i've got a full stop or a period character in them and we'll write some code to select.
Those columns i've got both these files stored in the same folder and i'll drop a link in the video description so you can download these so you've got the same data to.
Work with and just to show you the basic code we've got a simple subroutine setup which allows us to set the sql query text.
So at the moment i'm selecting the film id and the title columns from the film worksheet in that workbook all of the rest of the code just deals with connecting to the movies workbook.
Amount of detail so if you're interested excel vba working with databases excel vba writing sql for excel using adodb.
Those are the playlists you need anyway at the moment if i head back to the visual basic editor if i just run this subroutine i will find that i end up with a brand.
New worksheet in my dot in a field name workbook with a film id and title columns okay now let's try to include the release date column in the same select.
List if we look back at the movie's workbook you can see the release date column name has a full stop somewhere in the middle there.
And if we look back at the code i've written so far to refer to the film id and title columns you can see why that full stop might be a problem i have done a little more here than i.
Absolutely needed to i've
Qualified each column name in the select list with a reference to the sheet that it belongs to and to separate the sheet name from the.Column name we must use a full stop character this isn't entirely necessary for this example this code would work quite happily without the reference to.
The sheet name in front of each column name but it's helpful i think to understand why the full stop is a problem so i'm going to copy and paste my film.
Title column reference including the reference to the sheet name and the extra comma then we can replace title with release dot date now of course.
Excel is going to try to interpret this full stop as a separator between a sheet reference and a column reference but if i try to run this subroutine again that's not going to work of course.
So i get this error message so i click ok to clear that message and look at how we can solve this problem the solution to this problem is actually incredibly simple.
If not entirely obvious if you have a column name which includes a full stop character you can replace the full stop character with a hash symbol or a pound symbol.
And that will solve the problem if we run the subroutine again now we'll end up with a new worksheet and this one has a release hash date with values from the column.
We've referred to now while that solution wasn't exactly obvious there is one relatively straightforward way we could find that out rather than trying to refer to each.
Column by name we could just ask the
Query to return all of the columns from the worksheet so if we head back to the visual basic editor.We could just replace all of these individual column references here with an asterisk character to select every column so there is one extra column in the.
Movies workbook which has a full stop at the end of its name there run time with a full stop at the end if we run this subroutine now we'll end up.
With a worksheet which contains the a dome names for every column in that sheet just while we're on the subject there is one other character you may have a.
Problem with if you include it in a column name so i'm just going to head back to the visual basic editor and i'd like to explicitly select the title.
Release date and genre columns from my movies workbook so i'm not going to bother with the qualified column references this time i won't bother referencing the worksheet.
First let's just refer to title comma release hash date and comma genre okay so having done that i can happily run this subroutine and end up with a.
New worksheet including those three column names now i'm going to head back to the movies workbook and i'm going to modify the genre column.
Name by adding an exclamation mark character to the end having done that if i head back to the visual basic editor and i try to include that exclamation mark character at the.
End of the genre column name when i run the subroutine i'll end up with an error message again the exclamation mark character is.
Replaced with another character in the query if i click ok then change the exclamation mark character to an underscore character.
Instead when i run the subroutine this time have a look back at the results i'll see that i've now got the genre column.
Included so there you go i hope that was enough to answer the original question and if not as always feel free to carry on asking more questions otherwise i hope that helped thanks very.