S
skc
Hi,
I have a Access 2000 database and a Form with a dummy
table driving it. The form has a button with an onclick
macro...
I have created a File Import (.xls) Macro, and I wish to
enhance this with the option of browsing for a Excel sheet
and automatically binding this sheet to the form (after
the import). The Form is just like a mailmerge, i.e. it
will list and print all jobs for certain sales staff.
-----------------------------
Private Sub Command393_Click()
Dim j As Integer
Dim sheetcnt As Integer
On Error GoTo err_startExcel
Set xlapp = GetObject(, "Excel.Application")
xlapp.Visible = False
Set xlwb = xlapp.Workbooks.Open("C:\23.04.04.xls")
sheetcnt = xlapp.ActiveWorkbook.Sheets.Count
For z = 1 To sheetcnt
Set sheet = xlapp.ActiveWorkbook.Sheets(z)
If (sheet.Name = "Electricity") Then
DoCmd.TransferSpreadsheet
transfertype:=acImport, _
tablename:="elec", _
Filename:="C:\23.04.04.xls",
Hasfieldnames:=False, _
Range:="a1:s53",
SpreadsheetType:=acSpreadsheetTypeExcel9
MsgBox "Imported Successfully"
End If
'If (sheet.Name = "Gas") Then
' DoCmd.TransferSpreadsheet
transfertype:=acImport, _
' tablename:="gas", _
' Filename:="C:\23.04.04.xls",
Hasfieldnames:=False, _
' Range:="OPTION!b9:s53",
SpreadsheetType:=acSpreadsheetTypeExcel9
'End If
Next z
'sheet.Application.ActiveWorkbook.Save
sheet.Application.ActiveWorkbook.Close
xlapp.Quit
Set xlapp = Nothing
Set xlwb = Nothing
Set sheet = Nothing
End
Exit Sub
err_startExcel:
If Err.Number = 429 Then 'No current instance of Excel
start up Excel
Set xlapp = GetObject("", "Excel.Application")
Resume Next
Else
MsgBox Err.Description, vbExclamation, "Error: " &
Err.Number, Err.HelpFile, Err.HelpContext
Exit Sub
End If
End Sub
--------------------------------
Also, I want to create another Form for invoicing - so
that it will itemise all jobs in the table and put them in
a line-by-line format.
I am a newbie I need help!!!
Please help.
skc
I have a Access 2000 database and a Form with a dummy
table driving it. The form has a button with an onclick
macro...
I have created a File Import (.xls) Macro, and I wish to
enhance this with the option of browsing for a Excel sheet
and automatically binding this sheet to the form (after
the import). The Form is just like a mailmerge, i.e. it
will list and print all jobs for certain sales staff.
-----------------------------
Private Sub Command393_Click()
Dim j As Integer
Dim sheetcnt As Integer
On Error GoTo err_startExcel
Set xlapp = GetObject(, "Excel.Application")
xlapp.Visible = False
Set xlwb = xlapp.Workbooks.Open("C:\23.04.04.xls")
sheetcnt = xlapp.ActiveWorkbook.Sheets.Count
For z = 1 To sheetcnt
Set sheet = xlapp.ActiveWorkbook.Sheets(z)
If (sheet.Name = "Electricity") Then
DoCmd.TransferSpreadsheet
transfertype:=acImport, _
tablename:="elec", _
Filename:="C:\23.04.04.xls",
Hasfieldnames:=False, _
Range:="a1:s53",
SpreadsheetType:=acSpreadsheetTypeExcel9
MsgBox "Imported Successfully"
End If
'If (sheet.Name = "Gas") Then
' DoCmd.TransferSpreadsheet
transfertype:=acImport, _
' tablename:="gas", _
' Filename:="C:\23.04.04.xls",
Hasfieldnames:=False, _
' Range:="OPTION!b9:s53",
SpreadsheetType:=acSpreadsheetTypeExcel9
'End If
Next z
'sheet.Application.ActiveWorkbook.Save
sheet.Application.ActiveWorkbook.Close
xlapp.Quit
Set xlapp = Nothing
Set xlwb = Nothing
Set sheet = Nothing
End
Exit Sub
err_startExcel:
If Err.Number = 429 Then 'No current instance of Excel
start up Excel
Set xlapp = GetObject("", "Excel.Application")
Resume Next
Else
MsgBox Err.Description, vbExclamation, "Error: " &
Err.Number, Err.HelpFile, Err.HelpContext
Exit Sub
End If
End Sub
--------------------------------
Also, I want to create another Form for invoicing - so
that it will itemise all jobs in the table and put them in
a line-by-line format.
I am a newbie I need help!!!
Please help.
skc