点击下载此附件
分两步,第一步数据导入到ACCESS,第二步是代码实现行转列
Private Sub 导入_Click()
Dim rstSH As DAO.Recordset
Dim rstMX As DAO.Recordset
CurrentDb.Execute ("delete * from tbl销售订单明细")
'导入EXCEL数据
Set dlgOpen = Application.FileDialog(3)
With dlgOpen
.AllowMultiSelect = 0
.Show
End With
If dlgOpen.SelectedItems.Count > 0 Then
For i = 1 To dlgOpen.SelectedItems.Count
DoCmd.TransferSpreadsheet acImport, 8, "sheet1", dlgOpen.SelectedItems(i), True, ""
Next i
Set dlgOpen = Nothing
End If
Set rstMX = CurrentDb.OpenRecordset("tbl销售订单明细")
Set rstSH = CurrentDb.OpenRecordset("sheet1", dbOpenDynaset)
Do Until rstSH.EOF
For Each fld In rstSH.Fields
If fld.Name <> "门店名称" And fld.Name <> "日期" And fld.Name <> "ID" Then
' Debug.Print fld.Name
rstMX.AddNew
rstMX!订货日期 = rstSH!日期
rstMX!门店名称 = rstSH!门店名称
rstMX!商品名称 = fld.Name
rstMX!预定数量 = rstSH(fld.Name)
rstMX.Update
End If
Next
rstSH.MoveNext
Loop
rstMX.Close
rstSH.Close
Set rstMX = Nothing
Set rstSH = Nothing
MsgBox "导入完成!"
End Sub