set excel cell value

8

8oclockbean

i'm no expert at vba and have been having a hard time trying to send a
value from an open access form to a specific cell in excel. I've been writing
a module in Ac2000 that opens excel, names the sheet, and sends some Access
table data over.
Thanks to Bob Larsons code I was able to do most of this. After a week of
trial and error i'm now also able to format the sheet the way it needs to be,
plus some conditional formatting that seems to be working out great- now i'm
very badly stuck on trying to send a value from an open access form to a
particular cell in the sheet. Any attempts with something like

xlWSh.Range("D1").Value = Me.cust.Value

-or- "" = Me!cust.Value

-or- "" = [Forms]![Reporting]![cust].Value

-or- With ApXL.Selection
.Value = Reporting.cust.Value
End With

end up with an error "Improper use of Me" or any number of other errors.
I've tried a number of different ways to reference this combo-box that
resides in the active access form- this is driving me nuts- i've spent 5 full
days trying to figure this out on my own and have gotten nowhere- can anyone
help me with what i'm missing? this seems like it should be so simple but i
am running around in circles- any help would be greatly appreciated.

Public Function SendTQ2Excel(tmpDated As String, Optional DatedReport As
String)

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim sSQL As String
Dim ApXL As Object
Dim xlWBk As Object
Dim xlWSh As Object
Dim fld As Field
Const xlCenter As Long = -4108
Const xlBottom As Long = -4107

'On Error GoTo err_handler
Set dbs = CurrentDb
Set rst = CurrentDb.OpenRecordset(tmpDated)
Set ApXL = CreateObject("Excel.Application")
Set xlWBk = ApXL.Workbooks.Add
ApXL.Visible = True

Set xlWSh = xlWBk.Worksheets("Sheet1")
If Len(DatedReport) > 0 Then
xlWSh.Name = Left(DatedReport, 34)
End If
xlWSh.Range("A4").Select


For Each fld In rst.Fields
ApXL.ActiveCell = fld.Name
ApXL.ActiveCell.Offset(0, 1).Select
Next

rst.MoveFirst
xlWSh.Range("A5").CopyFromRecordset rst
xlWSh.Range("1:1000").Select

With ApXL.Selection.Font
.Name = "Calibri"
.Size = 11

End With
xlWSh.Range("1:1000").Select

'ApXL.Selection.Font.Bold = True
With ApXL.Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.MergeCells = False

End With

ApXL.ActiveSheet.Range("F5:F1000,G5:G1000,H5:H1000,J5:J1000,K5:K1000").
Select
ApXL.Selection.NumberFormat = "$#,##0.00"
ApXL.ActiveSheet.Range("J5:J1000").Select

With ApXL.Selection.Font
.Color = 32768

End With
ApXL.ActiveSheet.Range("J4").Select

With ApXL.Selection.Font
.Color = 32768
End With

ApXL.ActiveSheet.Range("K5:K1000").Select

With ApXL.Selection.Font
.Color = 255
End With

ApXL.ActiveSheet.Range("K4").Select

With ApXL.Selection.Font
.Color = 255
End With

' try to color the action values
Set ColorAction = Range("I5:I1000")

' start checking each cell in the target range for PAY or FIGHT
For Each Cell In ColorAction

If Cell.Value = "PAY" Then ' color it red
Cell.Font.ColorIndex = 3
ElseIf Cell.Value = "FIGHT" Then ' color it green
Cell.Font.ColorIndex = 10
ElseIf Cell.Value = "REDUCED" Then ' color it green
Cell.Font.ColorIndex = 10
Else ' remove all color
End If
Next

xlWSh.Range("A1").Select
' selects all of the cells
ApXL.ActiveSheet.Cells.Select
' does the "autofit" for all columns
ApXL.ActiveSheet.Cells.EntireColumn.AutoFit

' selects the first cell to unselect all cells
xlWSh.Range("A1").Select



rst.Close

Exit Function
err_handler:
DoCmd.SetWarnings True
MsgBox Err.Description, vbExclamation, Err.Number
Exit Function

End Function
 
S

Stephen sjw_ost

I have been doing some automation with Access to Excel. I am not the best at
it either but Have had a lot of success so far. This is what I would suggest;

Add in to your Access References the Microsoft Excel Object Library.
From the VB window goto Tools>References>scroll down to Microsoft Excel ##.#
Object Library and check it to use it.

In your code you will want to;
Dim ApXL As Excel.Application
Dim xlWBK As Excel.Workbook
Dim xlWSH As Excel.Worksheet

Then you can use the Excel options in your Access code and do something like
this;

Sub Test()
Dim ApXL As Excel.Application
Dim xlWBK As Excel.Workbook
Dim xlWSH As Excel.Worksheet

Set ApXL = GetObject(, "Excel.Application")
Set xlWBK = ApXL.ActiveWorkbook
Set xlWSH = xlWBK.ActiveWorkSheet

xlWBK.Sheets("Sheet1").Select 'To select your worksheet
xlWSH.Cells(1, 2).value = Me.yourformvalue

End Sub

I hope this helps and good luck.

--
Stephen


8oclockbean said:
i'm no expert at vba and have been having a hard time trying to send a
value from an open access form to a specific cell in excel. I've been writing
a module in Ac2000 that opens excel, names the sheet, and sends some Access
table data over.
Thanks to Bob Larsons code I was able to do most of this. After a week of
trial and error i'm now also able to format the sheet the way it needs to be,
plus some conditional formatting that seems to be working out great- now i'm
very badly stuck on trying to send a value from an open access form to a
particular cell in the sheet. Any attempts with something like

xlWSh.Range("D1").Value = Me.cust.Value

-or- "" = Me!cust.Value

-or- "" = [Forms]![Reporting]![cust].Value

-or- With ApXL.Selection
.Value = Reporting.cust.Value
End With

end up with an error "Improper use of Me" or any number of other errors.
I've tried a number of different ways to reference this combo-box that
resides in the active access form- this is driving me nuts- i've spent 5 full
days trying to figure this out on my own and have gotten nowhere- can anyone
help me with what i'm missing? this seems like it should be so simple but i
am running around in circles- any help would be greatly appreciated.

Public Function SendTQ2Excel(tmpDated As String, Optional DatedReport As
String)

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim sSQL As String
Dim ApXL As Object
Dim xlWBk As Object
Dim xlWSh As Object
Dim fld As Field
Const xlCenter As Long = -4108
Const xlBottom As Long = -4107

'On Error GoTo err_handler
Set dbs = CurrentDb
Set rst = CurrentDb.OpenRecordset(tmpDated)
Set ApXL = CreateObject("Excel.Application")
Set xlWBk = ApXL.Workbooks.Add
ApXL.Visible = True

Set xlWSh = xlWBk.Worksheets("Sheet1")
If Len(DatedReport) > 0 Then
xlWSh.Name = Left(DatedReport, 34)
End If
xlWSh.Range("A4").Select


For Each fld In rst.Fields
ApXL.ActiveCell = fld.Name
ApXL.ActiveCell.Offset(0, 1).Select
Next

rst.MoveFirst
xlWSh.Range("A5").CopyFromRecordset rst
xlWSh.Range("1:1000").Select

With ApXL.Selection.Font
.Name = "Calibri"
.Size = 11

End With
xlWSh.Range("1:1000").Select

'ApXL.Selection.Font.Bold = True
With ApXL.Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.MergeCells = False

End With

ApXL.ActiveSheet.Range("F5:F1000,G5:G1000,H5:H1000,J5:J1000,K5:K1000").
Select
ApXL.Selection.NumberFormat = "$#,##0.00"
ApXL.ActiveSheet.Range("J5:J1000").Select

With ApXL.Selection.Font
.Color = 32768

End With
ApXL.ActiveSheet.Range("J4").Select

With ApXL.Selection.Font
.Color = 32768
End With

ApXL.ActiveSheet.Range("K5:K1000").Select

With ApXL.Selection.Font
.Color = 255
End With

ApXL.ActiveSheet.Range("K4").Select

With ApXL.Selection.Font
.Color = 255
End With

' try to color the action values
Set ColorAction = Range("I5:I1000")

' start checking each cell in the target range for PAY or FIGHT
For Each Cell In ColorAction

If Cell.Value = "PAY" Then ' color it red
Cell.Font.ColorIndex = 3
ElseIf Cell.Value = "FIGHT" Then ' color it green
Cell.Font.ColorIndex = 10
ElseIf Cell.Value = "REDUCED" Then ' color it green
Cell.Font.ColorIndex = 10
Else ' remove all color
End If
Next

xlWSh.Range("A1").Select
' selects all of the cells
ApXL.ActiveSheet.Cells.Select
' does the "autofit" for all columns
ApXL.ActiveSheet.Cells.EntireColumn.AutoFit

' selects the first cell to unselect all cells
xlWSh.Range("A1").Select



rst.Close

Exit Function
err_handler:
DoCmd.SetWarnings True
MsgBox Err.Description, vbExclamation, Err.Number
Exit Function

End Function

.
Click to expand...
 
R

ryguy7272

There are a couple ways to control Excel from Access. Stephen showed you one
way. Another way is explained here:
http://www.consulting-group360.com/Code.aspx

Look for the code right at the top of the page; pay attention to this part:
' Your Excel code begins here. . .



--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


Stephen sjw_ost said:
I have been doing some automation with Access to Excel. I am not the best at
it either but Have had a lot of success so far. This is what I would suggest;

Add in to your Access References the Microsoft Excel Object Library.
From the VB window goto Tools>References>scroll down to Microsoft Excel ##.#
Object Library and check it to use it.

In your code you will want to;
Dim ApXL As Excel.Application
Dim xlWBK As Excel.Workbook
Dim xlWSH As Excel.Worksheet

Then you can use the Excel options in your Access code and do something like
this;

Sub Test()
Dim ApXL As Excel.Application
Dim xlWBK As Excel.Workbook
Dim xlWSH As Excel.Worksheet

Set ApXL = GetObject(, "Excel.Application")
Set xlWBK = ApXL.ActiveWorkbook
Set xlWSH = xlWBK.ActiveWorkSheet

xlWBK.Sheets("Sheet1").Select 'To select your worksheet
xlWSH.Cells(1, 2).value = Me.yourformvalue

End Sub

I hope this helps and good luck.

--
Stephen


8oclockbean said:
i'm no expert at vba and have been having a hard time trying to send a
value from an open access form to a specific cell in excel. I've been writing
a module in Ac2000 that opens excel, names the sheet, and sends some Access
table data over.
Thanks to Bob Larsons code I was able to do most of this. After a week of
trial and error i'm now also able to format the sheet the way it needs to be,
plus some conditional formatting that seems to be working out great- now i'm
very badly stuck on trying to send a value from an open access form to a
particular cell in the sheet. Any attempts with something like

xlWSh.Range("D1").Value = Me.cust.Value

-or- "" = Me!cust.Value

-or- "" = [Forms]![Reporting]![cust].Value

-or- With ApXL.Selection
.Value = Reporting.cust.Value
End With

end up with an error "Improper use of Me" or any number of other errors.
I've tried a number of different ways to reference this combo-box that
resides in the active access form- this is driving me nuts- i've spent 5 full
days trying to figure this out on my own and have gotten nowhere- can anyone
help me with what i'm missing? this seems like it should be so simple but i
am running around in circles- any help would be greatly appreciated.

Public Function SendTQ2Excel(tmpDated As String, Optional DatedReport As
String)

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim sSQL As String
Dim ApXL As Object
Dim xlWBk As Object
Dim xlWSh As Object
Dim fld As Field
Const xlCenter As Long = -4108
Const xlBottom As Long = -4107

'On Error GoTo err_handler
Set dbs = CurrentDb
Set rst = CurrentDb.OpenRecordset(tmpDated)
Set ApXL = CreateObject("Excel.Application")
Set xlWBk = ApXL.Workbooks.Add
ApXL.Visible = True

Set xlWSh = xlWBk.Worksheets("Sheet1")
If Len(DatedReport) > 0 Then
xlWSh.Name = Left(DatedReport, 34)
End If
xlWSh.Range("A4").Select


For Each fld In rst.Fields
ApXL.ActiveCell = fld.Name
ApXL.ActiveCell.Offset(0, 1).Select
Next

rst.MoveFirst
xlWSh.Range("A5").CopyFromRecordset rst
xlWSh.Range("1:1000").Select

With ApXL.Selection.Font
.Name = "Calibri"
.Size = 11

End With
xlWSh.Range("1:1000").Select

'ApXL.Selection.Font.Bold = True
With ApXL.Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.MergeCells = False

End With

ApXL.ActiveSheet.Range("F5:F1000,G5:G1000,H5:H1000,J5:J1000,K5:K1000").
Select
ApXL.Selection.NumberFormat = "$#,##0.00"
ApXL.ActiveSheet.Range("J5:J1000").Select

With ApXL.Selection.Font
.Color = 32768

End With
ApXL.ActiveSheet.Range("J4").Select

With ApXL.Selection.Font
.Color = 32768
End With

ApXL.ActiveSheet.Range("K5:K1000").Select

With ApXL.Selection.Font
.Color = 255
End With

ApXL.ActiveSheet.Range("K4").Select

With ApXL.Selection.Font
.Color = 255
End With

' try to color the action values
Set ColorAction = Range("I5:I1000")

' start checking each cell in the target range for PAY or FIGHT
For Each Cell In ColorAction

If Cell.Value = "PAY" Then ' color it red
Cell.Font.ColorIndex = 3
ElseIf Cell.Value = "FIGHT" Then ' color it green
Cell.Font.ColorIndex = 10
ElseIf Cell.Value = "REDUCED" Then ' color it green
Cell.Font.ColorIndex = 10
Else ' remove all color
End If
Next

xlWSh.Range("A1").Select
' selects all of the cells
ApXL.ActiveSheet.Cells.Select
' does the "autofit" for all columns
ApXL.ActiveSheet.Cells.EntireColumn.AutoFit

' selects the first cell to unselect all cells
xlWSh.Range("A1").Select



rst.Close

Exit Function
err_handler:
DoCmd.SetWarnings True
MsgBox Err.Description, vbExclamation, Err.Number
Exit Function

End Function

.
Click to expand...
Click to expand...
 
8

8oclockbean via AccessMonster.com

thanks for the reply- i tried that xlWSH.Cells(1, 2).value = Me.yourformvalue
and still get an "Improper use of ME" error
 
8

8oclockbean via AccessMonster.com

as you guys can see from the code in my initial post, i'm using all the
methods you guys mention but still get 'improper use of Me' - i'm also
checking my references and changing priorities to every which way i can think
of it still doesn't like ME (no pun intended). i've also tried different
syntax and still no luck. thanks for trying though.
 
T

tbs

the problem of your codes lies here:

Set ColorAction = Range("I5:I1000")

' start checking each cell in the target range for PAY or FIGHT
For Each Cell In ColorAction

If Cell.Value = "PAY" Then ' color it red
Cell.Font.ColorIndex = 3
ElseIf Cell.Value = "FIGHT" Then ' color it green
Cell.Font.ColorIndex = 10
ElseIf Cell.Value = "REDUCED" Then ' color it green
Cell.Font.ColorIndex = 10
Else ' remove all color
End If
Next

try changing

Set ColorAction = Range("I5:I1000")
to
Set ColorAction = xlwhs.Range("I5:I1000")

also, you'll need to declare ColorAction as Range and Cell object as well.

e.g.
dim ColorAction as Range, objCell as Cell

For Each objCell In ColorAction.cells
If objCell.Value = "PAY" Then ' color it red
objCell.Font.ColorIndex = 3
...
next objCell



8oclockbean said:
i'm no expert at vba and have been having a hard time trying to send a
value from an open access form to a specific cell in excel. I've been writing
a module in Ac2000 that opens excel, names the sheet, and sends some Access
table data over.
Thanks to Bob Larsons code I was able to do most of this. After a week of
trial and error i'm now also able to format the sheet the way it needs to be,
plus some conditional formatting that seems to be working out great- now i'm
very badly stuck on trying to send a value from an open access form to a
particular cell in the sheet. Any attempts with something like

xlWSh.Range("D1").Value = Me.cust.Value

-or- "" = Me!cust.Value

-or- "" = [Forms]![Reporting]![cust].Value

-or- With ApXL.Selection
.Value = Reporting.cust.Value
End With

end up with an error "Improper use of Me" or any number of other errors.
I've tried a number of different ways to reference this combo-box that
resides in the active access form- this is driving me nuts- i've spent 5 full
days trying to figure this out on my own and have gotten nowhere- can anyone
help me with what i'm missing? this seems like it should be so simple but i
am running around in circles- any help would be greatly appreciated.

Public Function SendTQ2Excel(tmpDated As String, Optional DatedReport As
String)

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim sSQL As String
Dim ApXL As Object
Dim xlWBk As Object
Dim xlWSh As Object
Dim fld As Field
Const xlCenter As Long = -4108
Const xlBottom As Long = -4107

'On Error GoTo err_handler
Set dbs = CurrentDb
Set rst = CurrentDb.OpenRecordset(tmpDated)
Set ApXL = CreateObject("Excel.Application")
Set xlWBk = ApXL.Workbooks.Add
ApXL.Visible = True

Set xlWSh = xlWBk.Worksheets("Sheet1")
If Len(DatedReport) > 0 Then
xlWSh.Name = Left(DatedReport, 34)
End If
xlWSh.Range("A4").Select


For Each fld In rst.Fields
ApXL.ActiveCell = fld.Name
ApXL.ActiveCell.Offset(0, 1).Select
Next

rst.MoveFirst
xlWSh.Range("A5").CopyFromRecordset rst
xlWSh.Range("1:1000").Select

With ApXL.Selection.Font
.Name = "Calibri"
.Size = 11

End With
xlWSh.Range("1:1000").Select

'ApXL.Selection.Font.Bold = True
With ApXL.Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.MergeCells = False

End With

ApXL.ActiveSheet.Range("F5:F1000,G5:G1000,H5:H1000,J5:J1000,K5:K1000").
Select
ApXL.Selection.NumberFormat = "$#,##0.00"
ApXL.ActiveSheet.Range("J5:J1000").Select

With ApXL.Selection.Font
.Color = 32768

End With
ApXL.ActiveSheet.Range("J4").Select

With ApXL.Selection.Font
.Color = 32768
End With

ApXL.ActiveSheet.Range("K5:K1000").Select

With ApXL.Selection.Font
.Color = 255
End With

ApXL.ActiveSheet.Range("K4").Select

With ApXL.Selection.Font
.Color = 255
End With

' try to color the action values
Set ColorAction = Range("I5:I1000")

' start checking each cell in the target range for PAY or FIGHT
For Each Cell In ColorAction

If Cell.Value = "PAY" Then ' color it red
Cell.Font.ColorIndex = 3
ElseIf Cell.Value = "FIGHT" Then ' color it green
Cell.Font.ColorIndex = 10
ElseIf Cell.Value = "REDUCED" Then ' color it green
Cell.Font.ColorIndex = 10
Else ' remove all color
End If
Next

xlWSh.Range("A1").Select
' selects all of the cells
ApXL.ActiveSheet.Cells.Select
' does the "autofit" for all columns
ApXL.ActiveSheet.Cells.EntireColumn.AutoFit

' selects the first cell to unselect all cells
xlWSh.Range("A1").Select



rst.Close

Exit Function
err_handler:
DoCmd.SetWarnings True
MsgBox Err.Description, vbExclamation, Err.Number
Exit Function

End Function

.
Click to expand...
 
T

tbs

Regarding your problem with setting excel cell value,
are you calling the function, SendTQ2Excel, from the access form? If you
have written your function in a module, using of "Me" would gives you error.
You'll have to use something like form_<form name>.<control name> or pass in
the control you're trying to access.

e.g.
public function SendTQ2Excel(CntlName as TextBox, tmpDated as string,
Optional DatedReport as string)
...
if isnull(CntlName) then
CntlName = ""
end if
xlwhs.Range("D1").value = CntlName.value
...
End Function

in your form,
sDate) said:
i'm no expert at vba and have been having a hard time trying to send a
value from an open access form to a specific cell in excel. I've been writing
a module in Ac2000 that opens excel, names the sheet, and sends some Access
table data over.
Thanks to Bob Larsons code I was able to do most of this. After a week of
trial and error i'm now also able to format the sheet the way it needs to be,
plus some conditional formatting that seems to be working out great- now i'm
very badly stuck on trying to send a value from an open access form to a
particular cell in the sheet. Any attempts with something like

xlWSh.Range("D1").Value = Me.cust.Value

-or- "" = Me!cust.Value

-or- "" = [Forms]![Reporting]![cust].Value

-or- With ApXL.Selection
.Value = Reporting.cust.Value
End With

end up with an error "Improper use of Me" or any number of other errors.
I've tried a number of different ways to reference this combo-box that
resides in the active access form- this is driving me nuts- i've spent 5 full
days trying to figure this out on my own and have gotten nowhere- can anyone
help me with what i'm missing? this seems like it should be so simple but i
am running around in circles- any help would be greatly appreciated.

Public Function SendTQ2Excel(tmpDated As String, Optional DatedReport As
String)

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim sSQL As String
Dim ApXL As Object
Dim xlWBk As Object
Dim xlWSh As Object
Dim fld As Field
Const xlCenter As Long = -4108
Const xlBottom As Long = -4107

'On Error GoTo err_handler
Set dbs = CurrentDb
Set rst = CurrentDb.OpenRecordset(tmpDated)
Set ApXL = CreateObject("Excel.Application")
Set xlWBk = ApXL.Workbooks.Add
ApXL.Visible = True

Set xlWSh = xlWBk.Worksheets("Sheet1")
If Len(DatedReport) > 0 Then
xlWSh.Name = Left(DatedReport, 34)
End If
xlWSh.Range("A4").Select


For Each fld In rst.Fields
ApXL.ActiveCell = fld.Name
ApXL.ActiveCell.Offset(0, 1).Select
Next

rst.MoveFirst
xlWSh.Range("A5").CopyFromRecordset rst
xlWSh.Range("1:1000").Select

With ApXL.Selection.Font
.Name = "Calibri"
.Size = 11

End With
xlWSh.Range("1:1000").Select

'ApXL.Selection.Font.Bold = True
With ApXL.Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.MergeCells = False

End With

ApXL.ActiveSheet.Range("F5:F1000,G5:G1000,H5:H1000,J5:J1000,K5:K1000").
Select
ApXL.Selection.NumberFormat = "$#,##0.00"
ApXL.ActiveSheet.Range("J5:J1000").Select

With ApXL.Selection.Font
.Color = 32768

End With
ApXL.ActiveSheet.Range("J4").Select

With ApXL.Selection.Font
.Color = 32768
End With

ApXL.ActiveSheet.Range("K5:K1000").Select

With ApXL.Selection.Font
.Color = 255
End With

ApXL.ActiveSheet.Range("K4").Select

With ApXL.Selection.Font
.Color = 255
End With

' try to color the action values
Set ColorAction = Range("I5:I1000")

' start checking each cell in the target range for PAY or FIGHT
For Each Cell In ColorAction

If Cell.Value = "PAY" Then ' color it red
Cell.Font.ColorIndex = 3
ElseIf Cell.Value = "FIGHT" Then ' color it green
Cell.Font.ColorIndex = 10
ElseIf Cell.Value = "REDUCED" Then ' color it green
Cell.Font.ColorIndex = 10
Else ' remove all color
End If
Next

xlWSh.Range("A1").Select
' selects all of the cells
ApXL.ActiveSheet.Cells.Select
' does the "autofit" for all columns
ApXL.ActiveSheet.Cells.EntireColumn.AutoFit

' selects the first cell to unselect all cells
xlWSh.Range("A1").Select



rst.Close

Exit Function
err_handler:
DoCmd.SetWarnings True
MsgBox Err.Description, vbExclamation, Err.Number
Exit Function

End Function

.
Click to expand...
 
8

8oclockbean via AccessMonster.com

Awesome!-thank you, I will try that immediately- yes i am calling it from a
button on my form.
 
8

8oclockbean via AccessMonster.com

first i changed this
Public Function SendTQ2Excel (cust As ComboBox, tmpDated As String, Optional
DatedReport As String)

....
...

then i changed the call to
Call SendTQ2Excel (cust As ComboBox, tmpDated As String, [DatedReport As
String])

when debugging the call I get a "Compile error: syntax error" -or- i get
"Compile Error Expected: list seperator or )" on the 1st As
maybe my syntax is bad?
 
8

8oclockbean via AccessMonster.com

here's where i'm currently at, i get a compile error on objCell As Cell -
"User-defined type not defined"

Public Function SendTQ2Excel(Cust As ComboBox, tmpDated As String, Optional
DatedReport As String)

Dim rst As DAO.Recordset
Dim ApXL As Object
Dim xlWBk As Object
Dim xlWSh As Object
Dim fld As Field
Const xlCenter As Long = -4108
Const xlBottom As Long = -4107

'On Error GoTo err_handler
Set db = CurrentDb
Set rst = CurrentDb.OpenRecordset(tmpDated)
Set ApXL = CreateObject("Excel.Application")
Set xlWBk = ApXL.Workbooks.Add
ApXL.Visible = True

Set xlWSh = xlWBk.Worksheets("Sheet1")
If Len(DatedReport) > 0 Then
xlWSh.Name = Left(DatedReport, 34)
End If
xlWSh.Range("A4").Select


For Each fld In rst.Fields
ApXL.ActiveCell = fld.Name
ApXL.ActiveCell.Offset(0, 1).Select
Next

rst.MoveFirst
xlWSh.Range("A5").CopyFromRecordset rst
xlWSh.Range("1:1000").Select

With ApXL.Selection.Font
.Name = "Calibri"
.Size = 11

End With
xlWSh.Range("1:1000").Select

'ApXL.Selection.Font.Bold = True
With ApXL.Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.MergeCells = False

End With

ApXL.ActiveSheet.Range("F5:F1000,G5:G1000,H5:H1000,J5:J1000,K5:K1000").
Select
ApXL.Selection.NumberFormat = "$#,##0.00"
ApXL.ActiveSheet.Range("J5:J1000").Select

With ApXL.Selection.Font
.Color = 32768

End With
ApXL.ActiveSheet.Range("J4").Select

With ApXL.Selection.Font
.Color = 32768
End With

ApXL.ActiveSheet.Range("K5:K1000").Select

With ApXL.Selection.Font
.Color = 255
End With

ApXL.ActiveSheet.Range("K4").Select

With ApXL.Selection.Font
.Color = 255
End With

' try to color the action values


' start checking each cell in the target range for PAY or FIGHT
Dim ColorAction As Range, objCell As Cell
Set ColorAction = xlWSh.Range("I5:I1000")

For Each objCell In ColorAction.Cells

If objCell.Value = "PAY" Then ' color it red
objCell.Font.ColorIndex = 3
ElseIf objCell.Value = "FIGHT" Then ' color it green
objCell.Font.ColorIndex = 10
ElseIf Cell.Value = "REDUCED" Then ' color it green
objCell.Font.ColorIndex = 10
Else ' remove all color
End If
Next

'try to put open form combo value to D1
If IsNull(Cust) Then
Cust = ""
End If
xlwhs.Range("D1").Value = Cust.Value


xlWSh.Range("A1").Select
' selects all of the cells
ApXL.ActiveSheet.Cells.Select
' does the "autofit" for all columns
ApXL.ActiveSheet.Cells.EntireColumn.AutoFit

xlWSh.Range("D1").Select
' selects the first cell to unselect all cells
xlWSh.Range("A1").Select

rst.Close

Exit Function
err_handler:
DoCmd.SetWarnings True
MsgBox Err.Description, vbExclamation, Err.Number
Exit Function

End Function
 
8

8oclockbean via AccessMonster.com

here's where i'm currently at, i get a compile error on objCell As Cell -
"User-defined type not defined"

Public Function SendTQ2Excel(Cust As ComboBox, tmpDated As String, Optional
DatedReport As String)

Dim rst As DAO.Recordset
Dim ApXL As Object
Dim xlWBk As Object
Dim xlWSh As Object
Dim fld As Field
Const xlCenter As Long = -4108
Const xlBottom As Long = -4107

'On Error GoTo err_handler
Set db = CurrentDb
Set rst = CurrentDb.OpenRecordset(tmpDated)
Set ApXL = CreateObject("Excel.Application")
Set xlWBk = ApXL.Workbooks.Add
ApXL.Visible = True

Set xlWSh = xlWBk.Worksheets("Sheet1")
If Len(DatedReport) > 0 Then
xlWSh.Name = Left(DatedReport, 34)
End If
xlWSh.Range("A4").Select


For Each fld In rst.Fields
ApXL.ActiveCell = fld.Name
ApXL.ActiveCell.Offset(0, 1).Select
Next

rst.MoveFirst
xlWSh.Range("A5").CopyFromRecordset rst
xlWSh.Range("1:1000").Select

With ApXL.Selection.Font
.Name = "Calibri"
.Size = 11

End With
xlWSh.Range("1:1000").Select

'ApXL.Selection.Font.Bold = True
With ApXL.Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.MergeCells = False

End With

ApXL.ActiveSheet.Range("F5:F1000,G5:G1000,H5:H1000,J5:J1000,K5:K1000").
Select
ApXL.Selection.NumberFormat = "$#,##0.00"
ApXL.ActiveSheet.Range("J5:J1000").Select

With ApXL.Selection.Font
.Color = 32768

End With
ApXL.ActiveSheet.Range("J4").Select

With ApXL.Selection.Font
.Color = 32768
End With

ApXL.ActiveSheet.Range("K5:K1000").Select

With ApXL.Selection.Font
.Color = 255
End With

ApXL.ActiveSheet.Range("K4").Select

With ApXL.Selection.Font
.Color = 255
End With

' try to color the action values


' start checking each cell in the target range for PAY or FIGHT
Dim ColorAction As Range, objCell As Cell
Set ColorAction = xlWSh.Range("I5:I1000")

For Each objCell In ColorAction.Cells

If objCell.Value = "PAY" Then ' color it red
objCell.Font.ColorIndex = 3
ElseIf objCell.Value = "FIGHT" Then ' color it green
objCell.Font.ColorIndex = 10
ElseIf Cell.Value = "REDUCED" Then ' color it green
objCell.Font.ColorIndex = 10
Else ' remove all color
End If
Next

'try to put open form combo value to D1
If IsNull(Cust) Then
Cust = ""
End If
xlwhs.Range("D1").Value = Cust.Value


xlWSh.Range("A1").Select
' selects all of the cells
ApXL.ActiveSheet.Cells.Select
' does the "autofit" for all columns
ApXL.ActiveSheet.Cells.EntireColumn.AutoFit

xlWSh.Range("D1").Select
' selects the first cell to unselect all cells
xlWSh.Range("A1").Select

rst.Close

Exit Function
err_handler:
DoCmd.SetWarnings True
MsgBox Err.Description, vbExclamation, Err.Number
Exit Function

End Function
 
8

8oclockbean via AccessMonster.com

dao is indeed in the references, tried priorty changes for the ref but still
same error
 
T

tbs

the syntax is wrong when calling the function.

should be something like
Call SendTQ2Excel(cust, tmpDated, DatedReport)

note that cust should be the name of the comboBox in your form.
 
T

tbs

sorry, my bad... didn't realize cell is not a valid object. try doing this
instead

' start checking each cell in the target range for PAY or FIGHT
Dim objCell as Range, iRow as integer
iRow = 5

For iRow = 5 to 1000
set objCell = xlWsh.Range("I" & iRow)
If objCell.Value = "PAY" Then ' color it red
objCell.Font.ColorIndex = 3
ElseIf objCell.Value = "FIGHT" Then ' color it green
objCell.Font.ColorIndex = 10
ElseIf Cell.Value = "REDUCED" Then ' color it green
objCell.Font.ColorIndex = 10
Else ' remove all color
End If
Next
 
8

8oclockbean via AccessMonster.com

yes, thanks- i got the call fixed earlier but am still trapping out on Dim
objCell As Cell in the module. I get a compile error "User-Defined type not
defined" - i checked my references and DAO is there along with Excel- i also
tried changing the reference priority to no avail.
 
8

8oclockbean via AccessMonster.com

sorry, tripping over my own posts- have made the changes and the other errors
have gone away except a 'run time 424- object required' on xlwhs.Range("D1").
Value = cust.Value - thank you for your time- it's greatly appreciated
 
8

8oclockbean via AccessMonster.com

-PROBLEM SOLVED- thanks to tbs! i got it now, it was misstype. Works great
now!- major thanks to you tbs! i really do appreciate the help- i've been
banging my head on this for a while. Thanks again tbs!!!

the only part that's left for me to figure out is how to put some 'footer'
info into this sheet. The number of records exported to this sheet will vary
each time it's run- i have to somehow find the row that is 3 blank rows after
the last row of records and then throw in some 'footer' stuff. Not sure if i
should make a new post for that bit of magic.
 
T

tbs

my guess is either cust object or xlwhs object is invalid. when you debug
till the line that generate the error (highlight in yellow), hover your mouse
pointer over to cust then followed by xlwhs. it'll usually tell you which is
invalid. also, I'll recommend you to add this line:
Option Explicit

at the top of your form and module to reduce the chances of invalid/unknown
object declaration. note that this line should stay outside of your function
and procedure.
 
T

tbs

you can retrieve the number of records using rst.recordcount.
just do the maths and you should be able to set the footer.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

VBA-Table Word 2019 - Move a selected text, from a cell to the adjacent cell to its right, by pressing the ENTER key 0
Word macro that can call cells out of Excel for a find and replace 0
Drop down list control value in vba 2
Pasting a cell into bookmarked table from excel vba 1
Macro help 0
Change row colors based on a cell value 1
Currenct issue with a text box 1
Getting values from Excel worksheet 4

Top