There has to be a dozen ways to send data to Excel from Access. Today, I'd like to show you one of my favorite methods.
This method uses a simple Access SQL command that leverages the IN clause to point to an external destination database – in this case Excel.
Here is the SQL String and the breakdown:
"SELECT * INTO [Buffalo] IN '' [Excel 8.0;Database=" & CurrentProject.Path & "\BuffaloMarket.xls] FROM PvTblFeed "
Select * Into [Buffalo]
(This specifies the worksheet in which the data will be output)
IN '' [Excel 8.0;Database=" & CurrentProject.Path & "\BuffaloMarket.xls]
(This identifies the destination workbook. Note that CurrentProject.Path returns the path to the current database – similar to 'ThisWorkbook.Path'.)
FROM PvTblFeed "
(This identifies the source for the data. You can also add a where clause and other standard stuff)
You can implement this in a simple function like so:
-
Function Transfer_to_Excel()
-
Dim strsql As String
-
-
strsql = "SELECT * INTO [Buffalo] IN '' [Excel 8.0;Database=" & _
-
CurrentProject.Path & _
-
"\BuffaloMarket.xls] FROM PvTblFeed WHERE Market = 'Buffalo'"
-
-
CurrentDb.Execute strsql, dbFailOnError
-
-
End Function
There are a couple of caveats here.
First, the destination Workbook must be closed. That is to say, it can't be open while trying to run this. Second, the destination sheet cannot already exist.
Here is another example using the RunSQL action to create an entire workbook with Market data – one tab for each market.
-
Function TransferAll_to_Excel()
-
-
DoCmd.SetWarnings False
-
DoCmd.RunSQL "SELECT * INTO [BUFFALO] IN '' [Excel 8.0;Database=" & CurrentProject.Path & "\MarketOutputs.xls] FROM PvTblFeed WHERE Market = 'Buffalo'"
-
DoCmd.RunSQL "SELECT * INTO [CALIFORNIA] IN '' [Excel 8.0;Database=" & CurrentProject.Path & "\MarketOutputs.xls] FROM PvTblFeed WHERE Market = 'CALIFORNIA'"
-
DoCmd.RunSQL "SELECT * INTO [CANADA] IN '' [Excel 8.0;Database=" & CurrentProject.Path & "\MarketOutputs.xls] FROM PvTblFeed WHERE Market = 'CANADA'"
-
DoCmd.RunSQL "SELECT * INTO [CHARLOTTE] IN '' [Excel 8.0;Database=" & CurrentProject.Path & "\MarketOutputs.xls] FROM PvTblFeed WHERE Market = 'CHARLOTTE'"
-
DoCmd.RunSQL "SELECT * INTO [DALLAS] IN '' [Excel 8.0;Database=" & CurrentProject.Path & "\MarketOutputs.xls] FROM PvTblFeed WHERE Market = 'DALLAS'"
-
DoCmd.RunSQL "SELECT * INTO [DENVER] IN '' [Excel 8.0;Database=" & CurrentProject.Path & "\MarketOutputs.xls] FROM PvTblFeed WHERE Market = 'DENVER'"
-
DoCmd.RunSQL "SELECT * INTO [FLORIDA] IN '' [Excel 8.0;Database=" & CurrentProject.Path & "\MarketOutputs.xls] FROM PvTblFeed WHERE Market = 'FLORIDA'"
-
DoCmd.RunSQL "SELECT * INTO [KANSASCITY] IN '' [Excel 8.0;Database=" & CurrentProject.Path & "\MarketOutputs.xls] FROM PvTblFeed WHERE Market = 'KANSASCITY'"
-
DoCmd.SetWarnings True
-
-
End Function
Granted, this may not be the silver bullet for all your integration problems, but you've got to admire the ease and simplicity of this method. By the way, this is just scratching the surface of all the cool things you can do with the IN clause.
Check out Rob Cooper's post at Access Team Blog describing how you can leverage the IN clause to hit external data sources.
RELATED STUFF
Hey Mike,
Really like this piece of code. Looking at the example above, you could actually do a Select Query before hand to loop through all the "Market" unique data and then create a a single DoCmd.RunSql to create each sheet by looping through this.
what do you think about that? Sorry I am too lazy code to do this, but looks quite do able?
OJ
OJ: You're right. I could open up a recordset and simply loop through the market names.
Can you pull Data form a Excel File with multiple sheets by specifying the Sheet name
Sam: This will work.
Mike thanks a million for your solution...
Good Stuff.
Has the advantage over Excel.Range.CopyFromRecordset that you don't need to (indeed must not) open the Excel workbook first.
A summary of your 'dozen ways' would be interesting.
/Roy