Transferspreadsheet Action

[Guest]

Is there any way, either using the TransferSpreadsheet action or VBA, to
transfer a table to an Excel spreadsheet WITHOUT the field names transferring
to the spreadsheet as well?

Your help is greatly appreciated.

Thanks,
Manuel

 

[Guest]

No, you can specify whether to import column names, but the export always
includes field names.
If it is absolutely necessary to do this, there is a way. If you are
exporting to an existing spreadsheet, you can open it as an Excel Object, and
use the CopyFromRecorset method. If it is a new spreadsheet, then you can
create an Excel Object, create a spreadsheet, use the CopyFromRecordset, save
and close the spreadsheet, and Quit the Excel application.

 

[Guest]

I'm exporting to an existing spreadsheet which already has field names. You
wrote:

"If you are exporting to an existing spreadsheet, you can open it as an
Excel Object, and use the CopyFromRecorset method."

Could you elaborate? How would I "open it as an Excel Object"? Would this
be a Macro or VBA command. And how would I envoke the "CopyFromRecorset
method"? I don't see this as an option in my Macro.

Thanks again,
Manuel

 

[Guest]

Here is how you open an Excel sreadsheet for automation:

'Open Excel
On Error Resume Next ' Defer error trapping.
Set xlApp = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
blnExcelWasNotRunning = True
Set xlApp = CreateObject("excel.application")
Else
DetectExcel
End If
Err.Clear ' Clear Err object in case error occurred.
On Error GoTo LoadAdjustedActuals_Err
DoEvents
xlApp.DisplayAlerts = False
xlApp.Interactive = False
xlApp.ScreenUpdating = False
Set xlBook = xlApp.Workbooks.Open(varGetFileName, 0, True)
xlBook.Worksheets("Actuals_res_export").Activate
*****************
Here is what you need to do to save and close it:

'Close files and delete link to spreadsheet
On Error Resume Next
xlBook.Save
xlBook.Close
Set xlBook = Nothing
'If we createed a new instance of Excel
If blnExcelWasNotRunning = True Then
xlApp.Application.Quit
End If
Set xlApp = Nothing
*************************
Here is some code required to support the code above:
Option Compare Database
Option Explicit

' Declare necessary API routines:
Declare Function FindWindow Lib "user32" Alias _
"FindWindowA" (ByVal lpClassName As String, _
ByVal lpWindowName As Long) As Long

Declare Function SendMessage Lib "user32" Alias _
"SendMessageA" (ByVal Hwnd As Long, ByVal wMsg As Long, _
ByVal wParam As Long, _
ByVal lParam As Long) As Long

Sub DetectExcel()
' Procedure dectects a running Excel and registers it.
Const WM_USER = 1024
Dim Hwnd As Long
' If Excel is running this API call returns its handle.
Hwnd = FindWindow("XLMAIN", 0)
If Hwnd = 0 Then ' 0 means Excel not running.
Exit Sub
Else
' Excel is running so use the SendMessage API
' function to enter it in the Running Object Table.
SendMessage Hwnd, WM_USER + 18, 0, 0
End If
End Sub
********************
And here is how you put the data in:

xlSheet.Cells(2, 1).CopyFromRecordset rstItms

 

[fangliang]

test

Here is how you open an Excel sreadsheet for automation:

'Open Excel
On Error Resume Next ' Defer error trapping.
Set xlApp = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
blnExcelWasNotRunning = True
Set xlApp = CreateObject("excel.application")
Else
DetectExcel
End If
Err.Clear ' Clear Err object in case error occurred.
On Error GoTo LoadAdjustedActuals_Err
DoEvents
xlApp.DisplayAlerts = False
xlApp.Interactive = False
xlApp.ScreenUpdating = False
Set xlBook = xlApp.Workbooks.Open(varGetFileName, 0, True)
xlBook.Worksheets("Actuals_res_export").Activate
*****************
Here is what you need to do to save and close it:

'Close files and delete link to spreadsheet
On Error Resume Next
xlBook.Save
xlBook.Close
Set xlBook = Nothing
'If we createed a new instance of Excel
If blnExcelWasNotRunning = True Then
xlApp.Application.Quit
End If
Set xlApp = Nothing
*************************
Here is some code required to support the code above:
Option Compare Database
Option Explicit

' Declare necessary API routines:
Declare Function FindWindow Lib "user32" Alias _
"FindWindowA" (ByVal lpClassName As String, _
ByVal lpWindowName As Long) As Long

Declare Function SendMessage Lib "user32" Alias _
"SendMessageA" (ByVal Hwnd As Long, ByVal wMsg As Long, _
ByVal wParam As Long, _
ByVal lParam As Long) As Long

Sub DetectExcel()
' Procedure dectects a running Excel and registers it.
Const WM_USER = 1024
Dim Hwnd As Long
' If Excel is running this API call returns its handle.
Hwnd = FindWindow("XLMAIN", 0)
If Hwnd = 0 Then ' 0 means Excel not running.
Exit Sub
Else
' Excel is running so use the SendMessage API
' function to enter it in the Running Object Table.
SendMessage Hwnd, WM_USER + 18, 0, 0
End If
End Sub
********************
And here is how you put the data in:

xlSheet.Cells(2, 1).CopyFromRecordset rstItms