Solved: Update existing Excel worksheet from Access

[DarkSprout]

:help
From an Access Database, how can I send the contents of a table/query to and already Existing Excel file, one table/query per worksheet.

The Excel file will only have code modules, which manipulates the newly inserted data when it is opened by the end user, who will receave it via email, which Access will send using Outlook.

With Thanks.

[CreganTur]

I'm not trying to hijack this thread, but I need to do something similar, but instead of updating an exisitng spreadsheet, I want to use data in access to create a brand new excel spreadsheet. (I thought I'd add this question here since it's so similar to DarkSprouts)

[Carl A]

Look for ExportAccessToExcellMultiSheet.mdb
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Tejpal,A.D

[CreganTur]

The example from Carl is impressive, but overly complicated for what I want to do; I did manage to find a simpler way to export data to Excel (you can use Tables or Queries).


'Export data to Excel
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,_
"TableOrQueryName", "FilepathWhereYouWantSpreadsheetSaved", True

The acSpreadsheetTypeExcel9 parameter declares which version of Excel you're running.

The True parameter at the end of the function is for whether or not the data you're exporting contains field names (column headings).

Now, this only writes new Excel spreadsheets- I'm still working on how to make them display after they've been written.

Also, after the Field Names parameter, there is space for a Range parameter- you might be able to use it to add new data to an existing spreadsheet, but that'll require some testing as I'm unsure of this.

NinjaEdit: When you set the Filepath, it must be valid. You cannot set the Filepath to something that does not exist yet.

For Example: lets say you want everything to go into the "Reports" folder at C:\Reports. This folder must exist; Access will not create the folder for you (using this Function)

[DarkSprout]

To open an Excel file use:

'// you may have to change the excel.exe path to match your config
Call Shell("C:\Program Files\Microsoft Office\OFFICE11\excel.exe " & Chr(34) & DirectoryAndFileName & Chr(34), 1) And use the following to find out if the File or Folder Exist:
Public Function FileExists(ByVal DirectoryAndFileName) As Boolean
FileExists = (Len(Dir(DirectoryAndFileName, vbDirectory + vbHidden + vbSystem)) > 0)
End Function

[CreganTur]

To open an Excel file use:

'// you may have to change the excel.exe path to match your config
Call Shell("C:\Program Files\Microsoft Office\OFFICE11\excel.exe " & Chr(34) & DirectoryAndFileName & Chr(34), 1) And use the following to find out if the File or Folder Exist:
Public Function FileExists(ByVal DirectoryAndFileName) As Boolean
FileExists = (Len(Dir(DirectoryAndFileName, vbDirectory + vbHidden + vbSystem)) > 0)
End Function

Excellent! Thanks Sprout! :clap:

[DarkSprout]

Cracked It!

Some Code, What I Wrote...

Dont you just love SQL, that was the trick, using an INSERT INTO query that places the data into Excel as a recordset
Carefull with the SheetName bit as IT WON'T insert into a existing worksheet
If you insert a Sheet1 - it will end up as Sheet11
Sub InsertIntoExcel(PathAndFileName As String, ObjectName As String, Optional NewSheetName As String = "Sheet1")
'// Exports Data Object Into and Existing Excel Workbook
'// Darryl S. Drury March08
Dim sql As String

'// Does File Exist?
If Len(Dir(PathAndFileName, vbDirectory + vbHidden + vbSystem)) = 0 Then
MsgBox StrBld("File:{0}\\nDoesn't Exist", PathAndFileName), 64, "File Error"
Exit Sub
End If

'// Does Object Exist?
If DCount("*", "MSysObjects", "[Name] = '" & ObjectName & "'") = 0 Then
MsgBox StrBld("Object:{0}\\nDoesn't Exist", ObjectName), 64, "Object Error"
Exit Sub
End If

sql = "SELECT * INTO [Excel 8.0;Database=" & PathAndFileName & "].[" & NewSheetName & "] FROM [" & ObjectName & "];"
DoCmd.RunSQL sql
End Sub

[Imdabaum]

'Export data to Excel
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,_
"TableOrQueryName", "FilepathWhereYouWantSpreadsheetSaved", True


I have a menu item labeled Reports with 2 reports; FSA and FSA w/Comments. The menu items open one form to filter the individual queries before the reports are loaded.

Form description:
I have 2 Toggle buttons
Button: [Filter by Initial] shows the a drop down box listing the initials of all users.
Button: [Filter by Area] shows a drop down box with the areas that our company works in.

Then the OK command button to print the report.
Select Case optReport
Case 1 ' By REPM
If Not IsNull(Me!cboRER.Column(0)) Then
sSQL = "RER = '" & Me!cboRER.Column(0) & "'"
End If
Case 2 ' By Area
If Not IsNull(Me!cboArea.Column(0)) Then
sSQL = "AreaID = '" & Me!cboArea.Column(0) & "'"
End If
End Select
DoCmd.OpenReport sReportName, acViewPreview, , sSQL

Now the users want the FSA w/Comments report to simply export and open into Excel because the Comments don't fit in the report very nicely. So is there a way of applying the filter on the call to TransferSpreadsheet?

[CreganTur]

I need to do something similar, but with a filter. Is there something similar to the DoCmd.OpenReport sReportName, acViewPreview, , sWhereclause; but with the TransferSpreadsheet command?

Simple answer is no- there's no Where parameter as a part of the TransferSpreadsheet method.

However, you can put in the name of a query for the TransferSpreadsheet Method, so you could simply whip up a query that serves as your filter.

[Imdabaum]

Thanks. That's what I was affraid of.

[rangudu_2008]

DarkSprout,
If you want to import/export data from an Access database using SQL commands or queries that are in-built into the db itself, then try out the codes in these links... They are very easy to understand as well as they can easily be modified as you need them in ur code appropriately.

http://www.excelguru.ca/node/23
http://www.erlandsendata.no/english/index.php?d=envbadacexportado
http://www.erlandsendata.no/english/index.php?d=envbadacexportdao
http://www.erlandsendata.no/english/index.php?d=envbadacimportado
http://www.erlandsendata.no/english/index.php?d=envbadacimportdao
http://www.erlandsendata.no/english/index.php?d=envbadacimportwbado
http://www.erlandsendata.no/english/index.php?d=envbawbreadfromclosedwb

The code in the link below could help u understand how to execute parameter queries of SQL in macros:
http://www.ozgrid.com/forum/showthread.php?t=85940

:thumb Always happy to help :thumb

[DarkSprout]

DarkSprout,
If you want to import/export data from an Access database using SQL commands or queries that are in-built into the db itself, then try out the codes in these links...
...
http://www.ozgrid.com/forum/showthread.php?t=85940

:thumb Always happy to help :thumb

The problem I had was that I couldn't Insert a data set into an already existing .xls file, which had code modules in and was being used as a template, to email to multible teams accross the county.

I Did solve the problem using the following Code:
Sub InsertIntoExcel(PathAndFileName As String, ObjectName As String, Optional NewSheetName As String = "Sheet1")
'// Exports Data Object Into and Existing Excel Workbook
'// Darryl S. Drury March08
'// Uses StrBld
Dim sql As String

'// Does File Exist?
If Len(Dir(PathAndFileName, vbDirectory + vbHidden + vbSystem)) = 0 Then
MsgBox StrBld("File:{0}\\nDoesn't Exist", PathAndFileName), 64, "File Error"
Exit Sub
End If

'// Does Object Exist?
If DCount("*", "MSysObjects", "[Name] = '" & ObjectName & "'") = 0 Then
MsgBox StrBld("Object:{0}\\nDoesn't Exist", ObjectName), 64, "Object Error"
Exit Sub
End If

'// Insert into Workbook
sql = StrBld("SELECT * INTO [Excel 8.0;Database={0}].[{1}] FROM [{2}];", PathAndFileName, NewSheetName, ObjectName)
DoCmd.RunSQL sql
End FunctionWhich works pretty damn well.

It does howether use my custom VBA String Builder: (you could strip these out for hard to follow[standard!] String Building)
Option Explicit
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~
' Function: StrBld - C# Style StringBuilder for VBA
' Darryl S. Drury [Feb08]
' ToUse: \\n (file://\\n) For 2 NewLines, \n For a single Newline, \tab for 4 Spaces
' \uNNNN (NNNN) being a 4Digit hexadecimal value of a sCharacter number
' {index} as insertion points for the Items, starting at {0}
' Format Case: \p (Proper Case) \> (Upper Case) \< (Lower Case)
' - must be first two Characters of string
' TEST: ?StrBld("\<\u0022{0}X{1}\u004D\u0050\u004C{2}\u0022\nTest", "E", "A", "E")
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~
Public Function StrBld(StringToFormat As String, ParamArray Items()) As String
On Error GoTo StringBuildFail
Dim InputString As String, i As Integer, sChar As String * 4
InputString = StringToFormat
Const DBLINE = vbNewLine & vbNewLine
'// Items
If Not IsMissing(Items) Then
For i = 0 To UBound(Items)
StringToFormat = Replace(StringToFormat, "{" & i & "}", Items(i))
Next i
End If

'// Unicode
Do While InStr(1, StringToFormat, "\u", vbBinaryCompare) > 0
i = InStr(1, StringToFormat, "\u", vbBinaryCompare)
sChar = Replace(Mid(StringToFormat, i, 6), "\u", "")
StringToFormat = Replace(StringToFormat, "\u" & sChar, Chr(Val("&H" & sChar)))
Loop

'// Tab & Newline(s)
StringToFormat = Replace(StringToFormat, "\tab", vbTab, , , vbBinaryCompare)
StringToFormat = Replace(StringToFormat, "\\n (file://\\n)", DBLINE, , , vbBinaryCompare)
StringToFormat = Replace(StringToFormat, "\n", vbNewLine, , , vbBinaryCompare)

'// Format Case
Select Case Left(StringToFormat, 2)
Case "\p": StringToFormat = StrConv(Replace(StringToFormat, "\p", ""), vbProperCase)
Case "\>": StringToFormat = StrConv(Replace(StringToFormat, "\>", ""), vbUpperCase)
Case "\<": StringToFormat = StrConv(Replace(StringToFormat, "\<", ""), vbLowerCase)
End Select
StrBld = StringToFormat
Exit Function

StringBuildFail:
MsgBox Err.Description & ", With StrBld() " & DBLINE & "Please Check String = '" & InputString & "'", 64, "Error#" & Err.Number
Err.Clear
Resume Next
End Function