|

楼主 |
发表于 2018-11-11 13:36:33
|
显示全部楼层
本帖最后由 SengChuary 于 2018-11-11 13:42 编辑
兄弟还得麻烦你一下,我想让格式按照这种格式储存既=日期_时间_操作员(文本框内字符任意)_ID.xlsx,ID是唯一,如果读ID是一样的,就把界面其它文本框的数据保存这个表,格式样式如下
Format(Year(Date$), "00") & Format(Month(Date$), "00") & Format(Day(Date$), "00") & "_" & Format(Hour(Time$), "00") & Format(Minute(Time$), "00") & Format(Second(Time$), "00") & "_" & txtOp.Text & "_" & txtSampleId.Text & ".xlsx"
例如文档名是20181111_131312_xiaoming_ID123456.xlsx ,其中txtOp.Text 文本是对应操作员xiaoming输入的内容,txtSampleId.Text 是输入的ID123456,且是唯一的文档,下次再输ID123456时检测打开这个文档继续把窗体内其它文本框内的数据再保存这个文档,就是储存不会搞了,麻烦兄弟帮看看应该怎样定。
Private Sub btnSave_Click()
Set xlapp = CreateObject("excel.application") '第一层
Set xlbook = xlapp.Workbooks.Add
xlapp.Visible = False
Nrec = 1
If Dir("D:\Data\" = Format(Year(Date$), "00") & Format(Month(Date$), "00") & Format(Day(Date$), "00") & "_" & Format(Hour(Time$), "00") & Format(Minute(Time$), "00") & Format(Second(Time$), "00") & "_" & txtOp.Text & "_" & txtSampleId.Text & ".xlsx") = "" Then '判断此路径下是否已存在文件,如需要存放到其它路径,请更改此处D:\
xlbook.Sheets(1).Range("a1") = "No."
xlbook.Sheets(1).Range("b1") = "Date"
xlbook.Sheets(1).Range("c1") = "Time"
xlbook.Sheets(1).Range("d1") = "Operator"
xlbook.Sheets(1).Range("e1") = "Sample ID"
xlbook.Sheets(1).Range("f1") = "Other Info"
xlbook.Sheets(1).Range("g1") = "Data1-Wrap"
xlbook.Sheets(1).Range("h1") = "Data2-Thick"
xlbook.Sheets(1).Range("a2") = Nrec
xlbook.Sheets(1).Range("b2") = txtDate.Text
xlbook.Sheets(1).Range("c2") = txtTime.Text
xlbook.Sheets(1).Range("d2") = UCase(txtOp.Text)
xlbook.Sheets(1).Range("e2") = UCase(txtSampleId.Text)
xlbook.Sheets(1).Range("f2") = UCase(txtOtherInfo.Text)
xlbook.Sheets(1).Range("g2") = txtDataWrap.Text
xlbook.Sheets(1).Range("h2") = txtDataThick.Text
xlbook.SaveAs "D:\Data\" = Format(Year(Date$), "00") & Format(Month(Date$), "00") & Format(Day(Date$), "00") & "_" & Format(Hour(Time$), "00") & Format(Minute(Time$), "00") & Format(Second(Time$), "00") & "_" & txtOp.Text & "_" & txtSampleId.Text & ".xlsx" '如需要存放到其它路径,请更改此处D:\
MsgBox ("创建文件成功,文件保存在D盘根目录下")
Else
Set xlbook = xlapp.Workbooks.Open("D:\Data\" = Format(Year(Date$), "00") & Format(Month(Date$), "00") & Format(Day(Date$), "00") & "_" & Format(Hour(Time$), "00") & Format(Minute(Time$), "00") & Format(Second(Time$), "00") & "_" & txtOp.Text & "_" & txtSampleId.Text & ".xlsx") '打开EXCEL工作簿
Set xlsheet = xlbook.Worksheets(1) '打开EXCEL工作表
xlsheet.Activate '激活工作表
r = xlbook.Worksheets(1).UsedRange.Rows.Count
r1 = "a" & r + 1
r2 = "b" & r + 1
r3 = "c" & r + 1
r4 = "d" & r + 1
r5 = "e" & r + 1
r6 = "f" & r + 1
r7 = "g" & r + 1
r8 = "h" & r + 1
xlbook.Sheets(1).Range(r1) = r
xlbook.Sheets(1).Range(r2) = txtDate.Text
xlbook.Sheets(1).Range(r3) = txtTime.Text
xlbook.Sheets(1).Range(r4) = UCase(txtOp.Text)
xlbook.Sheets(1).Range(r5) = UCase(txtSampleId.Text)
xlbook.Sheets(1).Range(r6) = UCase(txtOtherInfo.Text)
xlbook.Sheets(1).Range(r7) = txtDataWrap.Text
xlbook.Sheets(1).Range(r8) = txtDataThick.Text
MsgBox ("已存在该ID的文件,内容已保存")
End If
xlbook.Close (True) '关闭EXCEL工作簿
xlapp.Quit '关闭EXCEL
Set xlsheet = Nothing
Set xlbook = Nothing
Set xlapp = Nothing '释放EXCEL对
End Sub
|
|