Exporting or Importing to Excel

[kuhlkmk]

Hello - I have to pull data from a Notes DB into Excel. I have Crystal Rpts, which I can use for the pull, but when exporting to Excel it only exports up to 255 characters. So I'm looking for a more direct route to connecting to the Notes database & getting a subset of the data into Excel. Thanks for any help you can offer!

 

[pmonett]

I can give you a little library that outputs to an Excel sheet.

'LSExcelExport: 

Option Public



Const boldface_on = True
Const boldface_off = False
Const underline_on = True
Const underline_off = False

Const justify_left = 0
Const justify_center = 1
Const justify_right = 2
Const justify_full = 3
Sub NewExcelSheet(msexcel As Variant, xlSheet As Variant, IsVisible As Variant)
'msexcel is for passing back control of the instance of word an the new word document respectively
'IsVisible is for determining whether on not to make the instance of Word visible 
	
'Create an instance of Excel
	Set msexcel = CreateObject("Excel.Application")
	msexcel.DisplayAlerts = False
	msexcel.visible = True
	msexcel.Workbooks.Add '// Open a new Excel file
	'Set xlsheet = msexcel.ActiveWorkbook.ActiveSheet
	
End Sub
Sub setcellfont(msexcel As Variant, cellrange As Variant, fontname As String,fontsize As Integer,boldface As Variant,underline As Variant)
	
	With msexcel.activeworkbook.activesheet.range(cellrange)
		.Font.Name = fontname
		.Font.Size = fontsize
		.Font.Bold = boldface
		.Font.underline = underline
		.Font.Italic = False
%REM
		.Font.StrikeThrough = False
		.Font.DoubleStrikeThrough = False
		.Font.Outline = False
		.Font.Emboss = False
		.Font.Shadow = False
		.Font.Hidden = False
		.Font.SmallCaps = False
		.Font.AllCaps = False
		.Font.ColorIndex = 2 ' Blue - value of wdBlue MS Word Constant
		.Font.Engrave = False
		.Font.Superscript = False
		.Font.Subscript = False
		.Font.Spacing = 0
		.Font.Scaling = 100
		.Font.Position = 0
		.Font.Kerning = 0
		.Font.Animation = 6 ' Same as constant: wdAnimationShimmer
		.TypeText Chr$(13) 
%ENDREM
	End With
End Sub


Sub fillcell(msexcel As Variant, posy As Variant, posx As Variant, cellvalue As Variant)
	
	msexcel.ActiveWorkbook.ActiveSheet.cells(posx,posy) = cellvalue
End Sub


Sub setcellwidth(msexcel As Variant, posy As Integer, cellwidth As Variant)
'	activesheet.Columns("A:A").ColumnWidth = 11.57
	msexcel.activeworkbook.activesheet.Columns(posy).ColumnWidth = cellwidth
End Sub

Sub setstdcellwidth(msexcel As Variant, cellwidth As Variant)
	msexcel.activeworkbook.activesheet.standardwidth=cellwidth
End Sub

Sub namecellrange(msexcel As Variant, cellrange As String,rangename As String)
	msexcel.activeworkbook.activesheet.Range(cellrange).Value = rangename 
End Sub
Sub setCellFormat(msexcel As Variant, posy As Variant, posx As Variant, cellformat As Variant)
'	.Cells(r,c).NumberFormat = "#,###" (or .Selection.NumberFormat = "#,###")
	msexcel.ActiveWorkbook.ActiveSheet.cells(posx,posy).numberformat = cellformat
End Sub
Sub fillcellformula(msexcel As Variant,posy As Variant,posx As Variant,cellformula As String)
	
	msexcel.ActiveWorkbook.ActiveSheet.cells(posx,posy).formula = cellformula
End Sub
Sub setcellfontbold(msexcel As Variant,posy As Variant,posx As Variant)
	
	msexcel.activeworkbook.activesheet.cells(posx,posy).font.bold = True
End Sub

Sub setcellfontunderline(msexcel As Variant,posy As Variant,posx As Variant)
	
	msexcel.activeworkbook.activesheet.cells(posx,posy).font.underline = True
End Sub

Sub setcellfontitalic(msexcel As Variant,posy As Variant,posx As Variant)
	
	msexcel.activeworkbook.activesheet.cells(posx,posy).font.italic = True
End Sub

Sub setcellalignment(msexcel As Variant,posy As Variant,posx As Variant,justifytype As Integer)
	
	msexcel.activeworkbook.activesheet.cells(posx,posy).HorizontalAlignment = justifytype
End Sub

Have fun !

Pascal.

 

[kuhlkmk]

Hello Pascal - thank you for the information. I had not thought of doing a routine like that. Is that in VB? I don't have visual basic on my work machine, but perhaps I can do something like that from within Excel VB code. I hadn't thought of that. I ended up being able to use the Excel Query tool with a little luck as well & a macro to do the formatting. But a VB routine may be more straightforward. Thank you!

 

[pmonett]

No, it is a Lotus Script library. To use it, you create a new library in the Notes db and paste it in. Then you create an agent that uses the functions to export the data to Excel.

I take it that you don't do Notes programming very often ?
If so, you might need some example code using the above library.

Awaiting your reply.

Pascal.