PasteSpecial Format CSV From Recordset

Jul 28, 2008

Currently, my code involves making a connection to a db then run queries. Results from the queries goes to recordset & from the recordset copy to specified range in excel worksheet. I am trying to do an automation process.

The problem is that once the record is pasted in excel worksheet, the date column is not being recognised as date therefore excel function (vlookup) is not giving me the results in my report.

When i do a manual PasteSpecail as CSV into the worksheet from the query result, my report gets populated with data which is correct.

i've tried doing a pastespecial format:="CSV" but it doesnt work.

Is there any codes that i can use to copy from the recordset as a CSV format pastespecial??

Or any other ways that can be done?

View 9 Replies


VBA PasteSpecial Format & Values

Jan 14, 2007

I am trying to copy a range from one sheet and paste in another sheet via VBA.

ActiveSheet.Paste Destination:=Range("IV1").End(xlToLeft).Offset(0, 1)
Column E has formulas (=SumB3:D3) nothing more then that. I get a #REF after the paste into the FORCAST sheet.

I would like to Paste Values and Formats.

I tried changing the code to this, with different variations:

ActiveSheet.PasteSpecial Paste:=xlPasteFormats, xlPasteValues _
Destination:=Range("IV1").End(xlToLeft).Offset(0, 1)

But keep comming up with errors.

View 9 Replies View Related

How To Use Offset With Recordset

Feb 25, 2013

i have a created a connection with Access table named Test, the table has 3 fields, ID,Firstname and LastName. The values in field firstName is filled in a Combox which is in Sheet1 of excel sheet.

so far i have done with the coding which create a connection with access table and fille all the firstname from the table to the Combobox using a recordset.

the code for filling combobox value from records set is like

Do Until RecordSet.EOF
shp.ControlFormat.AddItem RecordSet.Fields("Name").Value

Now what I need is whenever I select a name in a combobox the Cell A1 in excel should show me the LastName for that name.

I am not very sure if we can use offset in this.

View 3 Replies View Related

Open The Recordset

Dec 29, 2008

i have a select statement, that pulls one row from access and i need to get the data and assign it to different fields in a userform. however, because it only pulls one row, its at .eof and closes, so any following statements cause errors because the recordset is closed.

qc.Open "SELECT * FROM [Equipment_Table] WHERE [Equipment_QC] =" & combobox12.text, cn, adOpenStatic
Equipment_Title.text = qc.fields.item("Equipment_Title")
Equip_File = qc.fields.item("Equipment_File")
Equip_ID = qc.fields.item("Equipment_ID")

View 9 Replies View Related

Delete All Entries In Recordset ADO

Jan 25, 2012

I got a recordset which I get from a database (I use ADO).

I want to delete every entry in that recordset from the database.

View 4 Replies View Related

VBA - Unable To Get Data For Recordset

Feb 28, 2014

I've Created Connectionstring for excel which is in database format and trying to get the data by comparing 2 sheets using he below code.. But not working..

Set ws = Sheet1
sSQL = ""
sSQL = "SELECT [Company Code], "


View 1 Replies View Related

Insert Ado Recordset Into Table

Sep 23, 2009

I'm getting extremely frustrated with trying to find some code that allows me to paste an ADO recordset into a database table!

What I am doing is on a weekly basis is creating a plan of which containers my company needs to devan. We have a system that stores shipping information about these containers and I get shipping information delivered to me via e-mail from one of our major shipping companies. Their data is more accurate so what I do is paste the data I get from them into my container planning sheet and import it into out Teradata Warehouse to query against using ADO and I import this one record at a time. This is extremely slow!

In order to get around this I want to create a recordset from this data and then paste the recordset into a table in the database. So far this is my ....

View 9 Replies View Related

VBA: Create PivotTable From A DAO Recordset

Sep 24, 2009

I have been creating pivot tables (from the active workbook) with ADO for a while and its working great.

I just tried to switch to DAO and adapted my code accordingly but the new code fail at:

Code: ....

View 9 Replies View Related

Pivot Table From Vba Ado Recordset

Nov 15, 2006

Is there an example outthere that will demonstrate how to create a pivot table using an ADO Recordset? I'll be experimenting tommorow but really don't relish the thought of spending 12 hours trying to figure it out.

View 2 Replies View Related

Sporadic Headers When Reading CSV Into Recordset

Nov 8, 2011

When I read a CSV file into a recordset, the headers are not all being read. I get gaps in the headers for whatever reason. Attached below is the Function to load the recordset from the file. I have hard coded Hdr to "No" in the connection string.


Function QueryByID(tableName As String, fieldToQuery As String, Target As Long, Hdr As String) As Recordset
Dim strFilePath, strFileName, strQuery As String
Dim oFSObj, oConn As Object
Dim oRs As Recordset
Dim f, lastRow As Integer

[Code] .......

The below is how I output the resultset:


Sub OutputResultSet(aTable As String)
Dim row, col, i, j As Integer
row = 1
col = 1

[Code] .....

I have triple checked the CSV file and the first row in each file has definitely got headers. Some headers are loaded and output fine, others are just blank.

View 5 Replies View Related

Filling Listbox With Recordset From Access

Jan 17, 2012

I want to fill a listbox with values from a database.

The query et al. works just fine, but I cant get the listbox filled.

Is there a command to fill the listbox with all the entries in the recordset at once?

View 5 Replies View Related

Current Recordset Converts In An Array

Nov 30, 2009

I have a sub-routine I call on that takes a current recordset and converts it to an Array.

I would like to make this sub-routine tool more flexable by being able to specify THE NAMES for the recordset and an array involved.

For example

Sub My_Code()

....blal bla bla.... current code...

Call Recordset_To_Array( Recordset_name, Array_name )

....bla bla bla... continued

end sub
And the subroutine tool:

Sub Recordset_To_Array( Recordset_name as String, Array_name as string )

'Code that generates RS
'Need to take Recordset_name string value and use it in the line below at "XXXXX"
Dim XXXXX as Recordset

'Code that generates array
'Need to take Array_name string value and use it in the line below at "XXXXX"
Dim XXXXX as Variant
End Sub

View 9 Replies View Related

Working With Recordset In A Range Of Cells

Oct 14, 2006

how i can display different values using the Selection.FormulaArray, ok look at my
Dim mySheet As Worksheet

' On Error GoTo errors
Dim x As Integer
x = TextBox1.Text
Dim y As Integer
y = RefEdit2.value

Set mySheet = Excel.ActiveSheet
mySheet. Range(RefEdit1).Select

Selection.FormulaArray = "=multiplie_value(""" & x & """, """ & y & """)"
MsgBox Selection.FormulaArray

Now the function i call:

Public Function multiplie_value(valor As Integer, multiplo As Integer) As Integer
multiplie_value = Val(valor) * multiplo
End Function

thats an example. so the real question is i create a form, the user enter some values to search to a database, the user select an area(a range) where they want the data to be display, but i dont how to display that info that is coming from the database in the range that the user selected. The info displayed in the range should look like a tabular report, column headers, etc.

View 9 Replies View Related

VBA Recordset Not Returning Updated Results From Query?

May 9, 2012

I have a function that loops through sheets and opens a query and copys the information to a sheet. I have gone in the access db and updated the query. Yet everytime i call it in excel it returns the old data. I have gone into access and verified what data I should get and I just don't get it.

I pass in the query name and the sheet name into qryName and sheetName name.

Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sQRY As String
Dim strFilePath As String


View 1 Replies View Related

Return PreStored Query Result Into Recordset Through Excel

Sep 12, 2013

I am trying to run a prestored query in a Access database and popuate the result into a Recordset --- through Excel. So far, I have managed to accomplish that using the code below. However, now I need to update some records in the recordset. The code below does not allow me to do so.

The following message prompted when the code tried to update the record: Run-time error '3251' Current Recordset does not support updating. This may be a limitation of the provider, or of the selected locktype.

This is written for other users who only knows how to create a query in Access. And the users prefer not to import the query results to spreadsheet.

*Someone suggested use Recordset.Open however, that seems to work only with SQL statement but not prestored Queries.

Sub RunExistingQuery()
'Execute prestored queries in Access
Dim Con As ADODB.Connection


View 2 Replies View Related

Create Pivot Table Based On Access Recordset

Nov 1, 2008

I'm looking for an example of creating a pivot table based on an ADO Access Recordset.

I understand I can use MS Query - which I am, but looking to see if an alternative exists. A table I am inetrested in currently has 400,000 records I would like to retrieve and pivot.

View 9 Replies View Related

Problem Displaying Single Record In Userform Listbox From Recordset

Jul 20, 2009

I am trying to populate a 3 column listbox in a userform from SQL Server via ADO. When the result set consists of more than one record, there is no problem and the data is displayed properly (ie each piece of data is in its appropriate column) eg...

test1...................1................. 01/01/1900

however when the recordset returns only a single record, the data does not transpose and views as below (ie each piece of data in the record is on a different line in the first column


View 12 Replies View Related

Excel 2010 :: ADO Recordset Query Quandary Locating Duplicates

Mar 13, 2013

I am having trouble setting up a query for a recordset that will list duplicates for an automated process to clean up. I am using Excel 2010 with Microsoft ActiveX Data Objects Recordset 6.0 Library and Microsoft ActiveX Data Objects 6.1 Library as references.

So far, I have chopped the query down to it's most basic elements and reassembled until it broke. Necessary pieces work well, but when I combine them, I get nowhere.

Here is the working simple query to retrieve a single row:

dupeSQL = "SELECT PSID FROM [Obstructed$] "

Here is the working single query to retrieve a count:

dupeSQL = "SELECT COUNT(PSID) As '" & "CountPSID" & "' " _
& "FROM [Obstructed$] "

When combined, I cannot figure out why it won't work:

dupeSQL = "SELECT [PSID], COUNT(PSID) As '" & "CountPSID" & "' " _
& "FROM [Obstructed$] "

Here is what I am trying to get to:

dupeSQL = "SELECT [PSID], COUNT(PSID) As '" & "CountPSID" & "' " _
& "FROM [Obstructed$] " _
& "WHERE COUNT(PSID) > 1 " _

I can even work without the GROUP BY. I have also played with the HAVING clause to try and accommodate the [PSID] field, but have gotten nowhere.

View 1 Replies View Related

VBA For PasteSpecial Values ONLY?

Nov 25, 2008

I was wondering if there is some VBA code that I can put in my spreadsheet that would only allow Paste Special Values?

I want to make sure that the user can not simply cut and paste. If possible it would be nice if the user doesn't know what is going on all they need to know is that they can copy and paste.

Is there a seamless way to accomplish this?

View 9 Replies View Related

PasteSpecial Method Failed

Jun 26, 2009

I have a working macro that duplicates the active worksheet with values only. It basically cleans out formulas and data validation from the data. The code is:

View 2 Replies View Related

Copy/PasteSpecial Not Working

Sep 5, 2009

well heres my code ....

View 14 Replies View Related

Copy Pastespecial Loop

Sep 16, 2009

The following macros will:

I'm desperate for a copy paste macros that will:

1) Copy H3:H143 (141 rows) from "Sheet 1" and place this on "Sheet 2" on D4:D144 (141 rows)

2) Copy I:3:I143 (141 rows) from "Sheet 1" and place this on "Sheet 2" on I4:I144 (141 rows)

So, in essence, it will copy every single column across on "Sheet 1", but will paste every 5 columns on "Sheet 2" .

It will then loop 33 times to continually copy and paste across columns.

View 14 Replies View Related

Copy PasteSpecial Not Working

Aug 10, 2006

I've just written a new Excel application which works fine when run on my local PC, and also when I save a copy on a central network server, and run it over the network.

However when another user who's testing it, runs it from the same network server, or on his local PC, the code trips out at the following line of ....

View 9 Replies View Related

Add PasteSpecial And ClearFormats To VBA Code

Nov 7, 2011

What I am looking for is once I select the worksheet I want copied, I only want the cell values pasted in the new worksheet. In addition, the worksheet should have no cell formats of any kind. I highlighted a part of the code in red where I think the new code should go, but I am not sure.

Option Explicit
Option Compare Text

Sub CopySheet()
Dim sh As String, nm As String
sh = InputBox("Enter the name of the worksheet you want to copy.")
If sh = "" Then Exit Sub

[Code] .........

View 4 Replies View Related

Macro PasteSpecial But Should Not See Moving?

Jun 6, 2014

Not sure how to say that, but I'm trying to write a simple macro to copy some data and pastespecial (Transpose) it somewhere else on the same sheet. But I don't want to see the data "moving".

Here is an example:

Sub Macro1()
Set SCOPE = ActiveSheet.Range("D2:G6")
Set THERE = ActiveSheet.Range("M240")


View 2 Replies View Related

Copy And PasteSpecial Based On If Statements?

Jun 9, 2013

I have a Workbook which I'm trying to apply the following VBA to (the moment Sheet11 is opened):

If Sheets("Sheet11").Range("B8:B372")=Sheets("Sheet8").Range("F1") - Dates
Sheets("Sheet8").Range("L24").Copy - Numbers
Sheets("Sheet11").Range("B8:B372").PasteSpecial PasteValues.Offset(0,1) (paste into relevant cell in Column C)
Else, 0

I would then like the relevant cell in Column D to be activated. IE: Offset(0,2) so the user can then enter their relevant data - more numbers.

View 9 Replies View Related

How To Stop PasteSpecial Popup Message

Jul 17, 2013

I have the below code to iterate and copy/paste data.

Application.DisplayAlerts = False
Dim r As Range
Set r = ThisWorkbook.Sheets("POList").Range("A2:A150")


This works fine, but I always get a message saying:

"Data on the Clipboard is not the same size and shape as the selected area. Do you want to paste the data anyway?"

The method completes when I click OK, but I am having to do this hundreds of time as there are a lot of files to be created. Is there a way to stop this message?

View 3 Replies View Related

How To Force Pastespecial To Ignore Message

May 21, 2014

the code below keeps coming up with the "fields are not the same size do you still want to paste" message when I do the pastespecial command - how can I force it to ignore all messages/force the paste? I am running this via an automation script and would prefer not to have a check to click "ok" each time I do this - as all my other pastes don't have this issue

Set objCB= CreateObject("Mercury.Clipboard")
Set objSheet = oEngine.Sheets.Item("vw_Client_RegulatoryDesignation")
With objSheet
Visible = True
End with
objSheet.PasteSpecial Paste =xlValues

View 4 Replies View Related

Pastespecial Of Range Class Failed!

Aug 10, 2009

I wrote the following macro to copy some values from a master workbook to a new one. It works superb on my small test sheet but once I try to implement this on my big mastersheet I only get the "Pastespecial of range class failed" on the second pastespecial operation. Why does it work on my small test sheet and not my big master sheet?

Sub ReportGenerator()
Dim NewWorkbookFileName As String
NewWorkbookFileName = ActiveSheet.Name & " report" & " as of " & ThisWorkbook.BuiltinDocumentProperties("Last Save Time")
'Debug.Print NewWorkbookFileName
Application.CutCopyMode = False
Workbooks.Add xlWBATWorksheet
Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteValues, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Application.GetSaveAsFilename (NewWorkbookFileName)
End Sub

View 9 Replies View Related

Copy & PasteSpecial Failed Error

Jul 18, 2007

This is the code I have:

Range("A" & rr).Copy
Range(Cells(rr, 1), Cells(rr, 38)).Delete Shift:=xlUp
Range("A" & rr).Select
ActiveCell.PasteSpecial xlValues

this code, should, find a cell in row A, copy the contents, then delete the whole row, and place the contents in the cell it lands on after the deletion.

But i get a: 'PasteSpecial method of range class failed'

View 5 Replies View Related

Copyrights 2005-15, All rights reserved