• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

User Form Question

Akankssha Gupta

New Member
Hello Guys,
I'm fairly new to coding, and have just begun working on a project which requires macros.
The data that I'm working with is a very large file (>40MB); basically a SAP data download.
We wish to automate some of the analysis; and here's where I begin.
When we receive the data from clients, its very unorganized, I want to be able to extract the relevant columns into a new workbook.
As the columns for most of the data would be changing in different client databases; how can I go about accomplishing this?
 
Hi,

You have mentioned the below point in your first post

"As the columns for most of the data would be changing in different client databases"

1) Does that mean you need to pull data from multiple files
2) If so does all th files have same headers but in different columns
3) Also you have highlighted some of the headers in the file which means you need data from only these headers
 
Hi Sathish,

These are headers from one client; we keep working on data from different clients. What happens is, due to different customizations, the positions of the columns changes, and there are +/- 5 columns
The highlighted columns are the ones that will be there in all client files, and I want to pull only those columns into a new workbook.
 
Hi,

Use the attached file to retrieve data from the selected file

Just browse and select the base file and hit retrieve
 

Attachments

  • Excel Query.xlsm
    35.3 KB · Views: 6
Thanks Sathish..
I used this to retrieve the data, but receiving an error (view attachment)
Also, can you let me know what you have done to achieve this.. I don't know if I'll be able to replicate it, but just for my understanding..

Thank you so much,
 

Attachments

  • Error.PNG
    Error.PNG
    7.2 KB · Views: 3
Hi Sathish,
Thank you a million, bazillion times over!
I changed the name of the sheet in the module, and it retrieved all the data.. It was brilliant!!

Can you please tell me what this object is called so that I can try and learn how to code it.. any direction you can give on how I can go about learning this..

Thank you so much!!!
Best,
Akankssha
 
Hi,

I have used an SQL query in VBA using an ADODB connection

Just go through the remarks mentioned in the below code

Please write back if you are not clear with any of the remarks

Code:
Sub Query_Data()
 
'Defining the Connection String
Dim cn As ADODB.Connection, strQuery As String, rst As ADODB.Recordset, strConn As String, Header As Boolean
 
'Creating New ADODB Connection
Set cn = New ADODB.Connection
 
'Defining the last blanck row as lr to paste retrieved data
Dim lr As Long
lr = Range("A" & Rows.Count).End(xlUp).Row + 1
 
'Defining the path in the text box as our database file path
Dim source1 As String
source1 = Sheet4.TextBox1.Text
 
'Creating a Connection String
'Provider is based on the Excel Version we are using
'Data Source is the file path of the data base
'Extended Properties is based on the Excel Version we are using
'HDR is to confirm if the first row of our data base is headers. If not then HDR=No
strCon = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
  "Data Source=" & source1 & ";" & _
  "Extended Properties=""Excel 12.0;HDR=Yes"";"
cn.Open strCon
 
'Creating an SQL query
'SELECT (the columns headers which we need to retrieve) FROM (Sheet Name$Range of Database)
strQuery = "SELECT [PO Number], [Posting Date], [Supplier ID], [Supplier Name], [Quantity], [Unit of Measure], [Net Amount], [Currency], [Item Description], [Material Group], [Organization Code], [Location Code], [Plant ] FROM [Sheet1$A:EB];"
 
'Creating a recordset and executing the above SQL query combined with the Connection String created earlier
Set rst = New ADODB.Recordset
rst.Open strQuery, cn, adOpenStatic, adLockReadOnly, adCmdText
 
'Pasting the queried data to the last blank row
Sheets("Output").Range("A" & lr).CopyFromRecordset rst
'Just scrolling to the 2nd row of the active sheet
ActiveWindow.ScrollRow = 2
 
'Closing the connection and exiting the SQL query
rst.Close
Set rst = Nothing
Set cn = Nothing
 
End Sub
 
Hi Sathish,

I am on my way to create more macros on the project; but I noted that it is not copying all the rows. There are 118731 rows in my database; but the sheet is only retrieving some 65536 rows.
Could this be due to a limit on clipboard?
 
Hi,

There is no clipboard involved in this as it is an SQL query system

It is working fine for me and it is retrieving more than 1,00,000 records

Check if you have mentioned any range with your sheet name in the code
 
I checked, and this is what I have in the code :

Code:
Sub Query_Data()

Dim cn As ADODB.Connection, strQuery As String, rst As ADODB.Recordset, strConn As String, Header As Boolean
Set cn = New ADODB.Connection
Dim lr As Long
Dim source1 As String
source1 = Sheet4.TextBox1.Text

strCon = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                        "Data Source=" & source1 & ";" & _
                        "Extended Properties=""Excel 12.0;HDR=Yes"";"
cn.Open strCon



strQuery = "SELECT [PO Number], [Posting Date], [Supplier ID], [Supplier Name], [Quantity], [Unit of Measure], [Net Amount], [Currency], [Item Description], [Material Group], [Organization Code], [Location Code], [Plant ] FROM [PO SAP$A:EB];"
Set rst = New ADODB.Recordset
rst.Open strQuery, cn, adOpenStatic, adLockReadOnly, adCmdText

lr = Range("A" & Rows.Count).End(xlUp).Row + 1

Sheets("Output").Range("A" & lr).CopyFromRecordset rst
ActiveWindow.ScrollRow = 2
rst.Close
Set rst = Nothing
' cn.Close
Set cn = Nothing


End Sub
 
Back
Top