Transferspreadsheet Action

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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.
 
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
 
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
 
test

Klatuu said:
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
Click to expand...
 
Back
Top