Import from Access to Excel

[ravaz]

Hi

Am importing from SQL to Excel. The Data Type of the field in access is text though it contains numeric Stock Barcodes.

Many of the codes start with a zero but when I pull this into Excel the preceeding zero does not appear. The code I am using is below:

Sub Stock()
Dim Cn As ADODB.Connection
Dim Server_Name As String
Dim Database_Name As String
Dim User_ID As String
Dim Password As String
Dim SQLStr As String
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset

Server_Name = "" ' Enter your server name here
Database_Name = "" ' Enter your database name here
User_ID = "" ' enter your user ID here
Password = "" ' Enter your password here
SQLStr = "SELECT cast(IW.StockCode as bigint),IW.StockCode,  cast(IM.ProductClass as int), sum(IW.QtyOnHand) " & _
"FROM InvWarehouse as IW, InvMaster as IM " & _
"where IW.StockCode = IM.StockCode " & _
"Group By cast(IM.ProductClass as int),cast(IW.StockCode as bigint) , IW.StockCode  " & _
"Order By cast(IM.ProductClass as int),cast(IW.StockCode as bigint),IW.StockCode "


Set Cn = New ADODB.Connection
Cn.CommandTimeout = 300
Cn.Open "Driver={SQL Server};Server=" & Server_Name & ";Database=" & Database_Name & _
";Uid=" & User_ID & ";Pwd=" & Password & ";"

rs.Open SQLStr, Cn, adOpenStatic
' Dump to spreadsheet
With Worksheets("Stk").Range("a2") ' Enter your sheet name and range here
    .ClearContents
    .CopyFromRecordset rs
End With
'            Tidy up
rs.Close
Set rs = Nothing
Cn.Close
Set Cn = Nothing

End Sub

 

[galt13]

Hi

Am importing from SQL to Excel. The Data Type of the field in access is text though it contains numeric Stock Barcodes.

Many of the codes start with a zero but when I pull this into Excel the preceeding zero does not appear. The code I am using is below:

Sub Stock()
Dim Cn As ADODB.Connection
Dim Server_Name As String
Dim Database_Name As String
Dim User_ID As String
Dim Password As String
Dim SQLStr As String
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset

Server_Name = "" ' Enter your server name here
Database_Name = "" ' Enter your database name here
User_ID = "" ' enter your user ID here
Password = "" ' Enter your password here
SQLStr = "SELECT cast(IW.StockCode as bigint),IW.StockCode,  cast(IM.ProductClass as int), sum(IW.QtyOnHand) " & _
"FROM InvWarehouse as IW, InvMaster as IM " & _
"where IW.StockCode = IM.StockCode " & _
"Group By cast(IM.ProductClass as int),cast(IW.StockCode as bigint) , IW.StockCode  " & _
"Order By cast(IM.ProductClass as int),cast(IW.StockCode as bigint),IW.StockCode "


Set Cn = New ADODB.Connection
Cn.CommandTimeout = 300
Cn.Open "Driver={SQL Server};Server=" & Server_Name & ";Database=" & Database_Name & _
";Uid=" & User_ID & ";Pwd=" & Password & ";"

rs.Open SQLStr, Cn, adOpenStatic
' Dump to spreadsheet
With Worksheets("Stk").Range("a2") ' Enter your sheet name and range here
    .ClearContents
    .CopyFromRecordset rs
End With
'            Tidy up
rs.Close
Set rs = Nothing
Cn.Close
Set Cn = Nothing

End Sub

Excel is dropping the leading zero because it's storing it as a number. If you precede each number with a single quote ' it will convert the number to text and not drop the zero. I'm sure there's a code method but this is all I could come up with this morning.