access vba multiply a number to a column in an Excel

[jydbman]

Hi Gurus,

I need to write a access vba function to pass the excel file name, sheet name, column letter as parameters and then multiply a minus sign number to all the cells in that column. How can I do that? I wrote a not working code. Would you please change it for me to make it work? Thank you very much!


Public Function multiplycolumn (ByVal sourcename As String, ByVal srcsheetname As String, ByVal columnltr As String, byval multiplevalue as long) As Boolean

Dim oXL As Excel.Application
Dim oSheet As Excel.Worksheet

Dim oWKSource As Excel.Workbook

Dim oBook As Excel.Workbook
Dim strSourceBook As String

Dim strSourceSheet1 As String

Dim strSourceSheet2 As String

Dim raSource As Excel.Range
dim coltr as string
dim mulval as long


strSourceBook = sourcename

'Get instance of Excel and open the workbooks
Set oXL = CreateObject("Excel.Application")

Set oWKSource = oXL.Workbooks.Open(strSourceBook, , ReadOnly = True)


Dim LR As Long, i As Long
LR = Cells(Rows.Count, 1).End(xlUp).Row
Application.ScreenUpdating = False

Cells(Rows.Count, 1).End(xlUp).Offset(0, 0).PasteSpecial Paste:=xlPasteValues
For i = 1 To LR
if isnumeric(cells( & columnltr, i)) then


Cells(& columnltr, i).Value = Cells(& columnltr, i).Value * & multiplevalue

end if
Next i

Application.CutCopyMode = False
Application.ScreenUpdating = True


'Finish
'Save workbook we modified
oXL.DisplayAlerts = False



For Each oBook In oXL.Workbooks
oBook.Close False
Next
oXL.DisplayAlerts = True

oXL.Quit
Set oXL = Nothing
Set oWKSource = Nothing


Set oBook = Nothing

End Function

 

[Rabbie]

Do you expect us to do your work for you without being paid? Why don't you tell us what is going wrong with your code. Does it compile correctly? If so what is it doing wrong when you run it? More info please

 

[hk1]

Yeah, I'm not sure either where to start on a question like this. Try to narrow it down to a specific problem.

What you're asking is already somewhat suspect. No offense but it looks to me like you're cobbling together some kind of solution that will be clumsy and unmanageable. Is there some reason you have to use an Excel spreadsheet? Some reason you have to manipulate it from Access? Why not write your VBA code inside Excel?

 

[jydbman]

Do you expect us to do your work for you without being paid? Why don't you tell us what is going wrong with your code. Does it compile correctly? If so what is it doing wrong when you run it? More info please

Sorry for my stupidity. I don't mean by that. I apologize that I only have very limited knowledge in VBA especially when working from Access to automate some tasks in Excel. My knowledge in Excel is even worse than Access. I do need help to learn little by little. If you can recommend a book for me to learn Access VBA to automate Excel, it will be greatly appreciated.


I put the function in a access module to make it public and reusable. Then, I have a button on the form to invoke the function:

Private Sub cmdmultiply_Click()

Dim src, sht, coltr As String
Dim mvalue As Long
Dim mok As Boolean

src = "c:\sortedtest.xls"
sht = "sheet 1"
coltr = "U"
mvalue = -1
mok = multiplycolumn(src, sht, coltr, mvalue)

End Sub

Option Compare Database

Public Function multiplycolumn(ByVal sourcename As String, ByVal srcsheetname As String, ByVal columnltr As String, ByVal multiplevalue As Long) As Boolean

Dim oXL As Excel.Application
Dim oSheet As Excel.Worksheet

Dim oWKSource As Excel.Workbook

Dim oBook As Excel.Workbook
Dim strSourceBook As String

Dim strSourceSheet1 As String

Dim strSourceSheet2 As String

Dim raSource As Excel.Range
Dim coltr As String
Dim mulval As Long


strSourceBook = sourcename

'Get instance of Excel and open the workbooks
Set oXL = CreateObject("Excel.Application")

Set oWKSource = oXL.Workbooks.Open(strSourceBook, , ReadOnly = True)


Dim LR As Long, i As Long
LR = Cells(Rows.Count, 1).End(xlUp).row
Application.ScreenUpdating = False

Cells(Rows.Count, 1).End(xlUp).Offset(0, 0).PasteSpecial Paste:=xlPasteValues
For i = 1 To LR
If IsNumeric(Cells(columnltr, i)) Then


Cells(columnltr, i).Value = Cells(columnltr, i).Value * multiplevalue

End If
Next i

'Application.CutCopyMode = False
'Application.ScreenUpdating = True


'Finish
'Save workbook we modified
oXL.DisplayAlerts = False



For Each oBook In oXL.Workbooks
oBook.Close False
Next
oXL.DisplayAlerts = True

oXL.Quit
Set oXL = Nothing
Set oWKSource = Nothing


Set oBook = Nothing

End Function


When I click the button, it errors out
Compile error: Method or data member not found
and point to the line : Application.ScreenUpdating = False

I commented that line then click the button again.
It errors out : Run-time error '1004'
PasteSpecial method of Range class failed

and it point to:
Cells(Rows.Count, 1).End(xlUp).Offset(0, 0).PasteSpecial Paste:=xlPasteValues

Then I comment it out, click button again, it errors out
Type mismatch: and point to the line : If IsNumeric(Cells(columnltr, i))

 

[jydbman]

Yeah, I'm not sure either where to start on a question like this. Try to narrow it down to a specific problem.

What you're asking is already somewhat suspect. No offense but it looks to me like you're cobbling together some kind of solution that will be clumsy and unmanageable. Is there some reason you have to use an Excel spreadsheet? Some reason you have to manipulate it from Access? Why not write your VBA code inside Excel?

I have to convert a text file with space, tab, and - as delimiters. In order to convert the text file to excel and then to sort the file by the columns with numeric values, I have to replace the tab and - to space first and then use space as delimiter then convert to Excel. the problem I have is there are columns with negative numbers and the minus signs also were replaced with space with other dash strings. That's the reason I am hoping I can multiply a -1 to the numeric columns if the cells are numbers not strings. (the columns do have few cells with strings not numbers).

I am developing a Access database application and everything has to be in Access. Besides, I don't have enough knowledge in Excel to do that.

 

[hk1]

So the file uses a mixture of delimiters? You can't go just by spaces or just by tabs?

 

[jydbman]

So the file uses a mixture of delimiters? You can't go just by spaces or just by tabs?

Right. I can not go just by spaces or just by tabs. I have to eliminate the - in order to sort by those columns. Thanks.

 

[hk1]

Sorry but I can't help but get hung up on what you're trying to do with that text file. You don't have any control over how the text file is output?

Importing a file that doesn't have decent delimiters is really tricky business. I would normally try to convert the "-" in the negative numbers to a different character first (like an @ sign), then replace all other "-" with space or tab, then go back and replace all the @ signs with a dash again.

To do this you would probably need to use the File System Object to open the file, read it line by line and then write, use the replace statement or some other custom function to do all the replacements, then write it all out once you are finished. It can be fairly intense to write this kind of procedure and it can also take a while if the file is very large and/or the computer running it is slow.

 

[jydbman]

Sorry but I can't help but get hung up on what you're trying to do with that text file. You don't have any control over how the text file is output?

Importing a file that doesn't have decent delimiters is really tricky business. I would normally try to convert the "-" in the negative numbers to a different character first (like an @ sign), then replace all other "-" with space or tab, then go back and replace all the @ signs with a dash again.

To do this you would probably need to use the File System Object to open the file, read it line by line and then write, use the replace statement or some other custom function to do all the replacements, then write it all out once you are finished. It can be fairly intense to write this kind of procedure and it can also take a while if the file is very large and/or the computer running it is slow.

Thank you for reply!
I no longer pursue The text - Excel conversion issue. The issue I am having now is to "add back the minus signs" (multiply -1) back to the numeric cells in the specific column. That column originally has - signs for all the cells which have numbers but the - signs were replaced with space during the conversion. There are one column with - to concatenate 2 elements(text strings), I need to remove the - in between to make 2 separate columns so I can use one of the column to sort. It's no longer an issue. But I do need to add - back to the numeric column though.

 

[hk1]

One thing that still isn't clear, does every entry in the numeric column always have the "-" sign or do just some entries?

 

[jydbman]

One thing that still isn't clear, does every entry in the numeric column always have the "-" sign or do just some entries?

Thanks for replying!

That numeric column originally should all have - signs if they are numeric. There are probably a couple hundreds of rows in the sheet. Maybe a few of the cells (less than 5) in the column with the cell value = "n/a" (not numeric)
That's why I would like to check if isnumeric then multiply by -1.
Actually, it doesn't matter to me if has to add a - to n/a cells or not.