What's the best approach: Need multiple queries displayed on Excel on the same page

[bobdole22]

I'm brand new at my job and I'm trying to get hired on here, so it makes me extra nervous if I can't figure this out.

My boss has asked me to automate his reports he gets through a combination of access and excel. I say, "Sure, no problem." I get started on it (being fairly new to VBA and very new to Access) and I am starting quick. Right of the bat, learned a ton of important functions. He needs me to have a button to choose a db to show. Easy, just created a button and saved it to a variable.

Next, he needs me to run a bunch of his Microsoft Access Macros through Excel. Also, no problem. Just told it to open the Access File with a ".OpenCurrentDatabase" run the macros with ".DoCmd.RunMacro" and close it with ".CloseCurrentDatabase & .Quit."

Now he needs me to display some of his queries on Excel. I'm thinking sure, no problem. I tell it to connect to the database with some "ADODB.Connection" because it's a accdb file. Test out some SQL statements with:

VB:
Set cn = New ADODB.Connection
With cn
.Provider = "Microsoft.ACE.OLEDB.12.0"
.Open MyConn
Set rs = .Execute(sSQL)
End With

Formatting tags added by mark007
The simple statements work fine and show up nice and neatly. Although, I've hit a snag and I need some outside help. The SQL statements he has in his queries are insanely long and complex. They won't show up through this process. After spending over an hour trying to format the length in a string (because VB has a line limit, so you have to do a lot of &'s), I finally get it to run without a syntax error. But now I get an error saying It's returning 0 queries. I know for a fact it's not returning 0 queries, we run it all the time. It's just that I don't think excel can handle a statement 20 lines long without syntax errors.

Let me know if there is a way to do this please

Cross-Thread: What's the best approach: Need multiple queries displayed on Excel on the same page

[bobdole22]

Next I try to display the queries through ".DoCmd.RunMacro" back up top. I make a macro that exports to excel. The problem is, after tons of research. That I need to display 14 different queries and that way will only display 1 per Worksheet or tab. I need them all on the same page. I have no clue what to do now.


He has his Excel page originally linked to Access through the old-fashioned way. Just clicking the "link-button." So it works fine for displaying at first glance. But he says it needs to be able to switch computers and not get a filepath error. Now, that is impossible to my knowledge this way. Is their any way to code and change the file path of an already linked Excel sheet? Or even to re-link one through VBA only? From my research I haven't found anything, that's why I'm asking y'all. Any help would be great. My boss is waiting impatiently and I have already wasted the week finding out those errors.