Importing information from a odbc source

[mikelam]

I currently have a macro that runs which collects data from a few workbooks and summarizes it all in one workbook as a report. There is additional information which is obtained from a odbc datasource using Crystal reports and then manually added to the report. I am trying to import the information from within excel as part of the macro. Has anyone attempted this in the past?

 

[babs]

Yes, you can do it. Try it with Tools > External Data to start with.

For the commands - try recording macro with all the steps starting from Tools - External Data and then selecting your ODBC connection.

IT works great !!

 

[babs]

Yes, you can do it. Try it with Tools > External Data to start with.

For the commands - try recording macro with all the steps starting from Tools - External Data and then selecting your ODBC connection.

IT works great !!

 

[babs]

Yes, you can do it. Try it with Tools > External Data to start with.

For the commands - try recording macro with all the steps starting from Tools - External Data and then selecting your ODBC connection.

IT works great !!

It should look alike following :

With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DSN=prod;UID=;PWD=;DBQ=PROD;DBA=W;APA=T;EXC=F;FEN=T;QTO=T;FRC=10;FDL=10;LOB=T;RST=T;GDE=F;FRL=F;BAM=IfAllSuccessful;MT" _
), Array("S=F;MDI=F;CSR=F;FWC=F;PFC=10;TLO=0;")), Destination:=Range("G9"))
.Sql = sqlname & Chr(13) & "" & Chr(10) & ""
.FieldNames = False
.RefreshStyle = xlDeleteCells
.RowNumbers = False
.FillAdjacentFormulas = False
.RefreshOnFileOpen = False
.HasAutoFormat = False
.BackgroundQuery = True
.TablesOnlyFromHTML = True
.Refresh BackgroundQuery:=False
.SavePassword = True
.SaveData = False
End With