Controlling Excel from Access

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have to perform an import from an excel file to Access. The file comes
originally as a csv file. I have to delete some rows and columns, then
change the formatting of some columns. I am using Windows 2000, Office 2000

I have been able to do everything but fill a column with zeros

Here is some of the code attached to a command button, I am using, it works
except I can't figure out how to fill down one column. In excel it uses the
Selection.FillDown command but I can't find that in the Excel Reference in
Access. Any other ideas.

with xlssheet
.Columns("E:E").Insert
.Range("E1").FormulaR1C1 = "Free Credit"
.Range("e2").FormulaR1C1 = "0"
this is where I will insert modified code.
end with

Essentially, I want to copy cell E2 to the last row in this column. This is
what I use in Excel that works, how can it be translated into Access??
Selection doesn't seem to be an available option in Access nor does
ActiveCell appear.


range("A1").select
Selection.End(xlDown).Select
lastrow = ActiveCell.Row
Range("e2:e" & lastrow).Select
Selection.FillDown

Once this is done the file is then saved in an Excel format and closed and
then imported into Access.

Thanks for your help
 
Hi Lynn,

This is an Excel problem, not an Access one, so it would have made more
sense to ask in an Excel forum. Basically you need to use Excel's
Application.Intersect method to return the range you need. It's easiest
to first fill down from row 1 to the bottom and then overwrite row 1
with the column header. This is air code but shows the general idea:


Dim C As Excel.Range
Dim lngCol As Long

lngCol = 5 ' same as E
With xlsSheet
.Columns(lngCol).Insert xlShiftToRight
For Each C In _
.Parent.Parent.Intersect(.UsedRange, .Columns(lngCol))
C.Formula = "0"
Next
.Cells(1, lngCol).Formula = "Free Credit"

'If you have instantiated an Excel Application object as well
'as well as the Worksheet object use that rather than the
 
This is an Excel problem, not an Access one, so it would have made more
sense to ask in an Excel forum.
Click to expand...

It sounds to me that Excel may not be required.

Unwanted rows could be filtered out using an appropriate WHERE clause.
Unwanted columns could simply be omitted from the SELECT clause. And
creating a column of zeros is simplicity itself e.g.

SELECT 0 AS [Free Credit]

Formatting is not imported into MS Access anyhow, unless the intention
is to affect a column's data type, in which case this could be done
using a schema.ini file or import specs.

The OP would need to post some sample data and expected results for us
to come up with an appropriate query.

Jamie.

--
 
Thanks for your responses. I understand that you might think that this is an
excel issue but I don't believe it is. I have automated the procedure in
Excel already and am now trying to convert that code to the Access equivalent
so that anyone could modify the csv file in Excel before importing it into
accesss without having to do it manually.

All the code is now stored in an Access module as I am trying to simplify a
procedure. I don't want the user to open excel to do this anymore. I wanted
to ensure that file names are stored in a consistent location and that the
changes to the csv file are performed consistently and correctly.

I have been able to accomplish all the manipulation of the excel spreadsheet
through code in Access except adding zeros all the way down a column that has
been inserted.

I have already written a simple update query to replace all null values with
zeros but I would really like to figure out how to control using autofill in
Excel from Access for future reference.

I have enclosed the code that I use in Access to perform all the other
manipulation, maybe that will trigger some ideas.

Private Sub cmdFiletoModify_Click()
On Error GoTo OpenExcelWorkbook_Err

Dim strFilter As String
Dim strInputFileName As String
Dim xlsApp As Excel.Application
Dim xlsWorkbook As Workbook
Dim xlsSheet As Worksheet
Dim mystrfilename As String
Dim lastrow As Variant

If fIsAppRunning("Excel") Then
Set xlsApp = GetObject(, "Excel.Application")
boolXL = False
Else
Set xlsApp = CreateObject("Excel.Application")
boolXL = True
End If


With xlsApp
.Visible = True
.Workbooks.Open strInputFileName

End With

Set xlsWorkbook = ActiveWorkbook
Set xlsSheet = ActiveSheet

mystrfilename = strInitDir & "INVOICED_" & xlsSheet.Name & ".xls"
xlsSheet.Parent.SaveAs FileName:=mystrfilename, FileFormat:=xlNormal
txtImportFile.Visible = False
txtImportFile = mystrfilename


'Run File Correction Stuff

' removes the existing heading rows
With xlsSheet
.Rows("1:13").Delete xlUp
.Rows("1:1").Insert xlDown
End With
'finds the total lines and deletes them
With xlsSheet
.Cells.Find(What:="**TOTAL", After:=ActiveCell, LookIn:=xlFormulas,
Lookat:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False).Activate
.Rows(ActiveCell.Row).Delete
.Cells.Find(What:="**CREDIT", After:=ActiveCell, LookIn:=xlFormulas,
Lookat:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False).Activate
.Rows(ActiveCell.Row).Delete
.Cells.Find(What:="**SOFTWARE", After:=ActiveCell,
LookIn:=xlFormulas, Lookat:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext, MatchCase:=False).Activate
.Rows(ActiveCell.Row).Delete
.Cells.Find(What:="**GRAND", After:=ActiveCell, LookIn:=xlFormulas,
Lookat:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False).Activate
.Rows(ActiveCell.Row).Delete
End With

'Delete unnecessary columns'
With xlsSheet
.Columns("A:B").Delete
.Columns("B:B").Delete
.Columns("C:E").Delete

'Inserts new row headings
.Range("a1").FormulaR1C1 = "SerialNo"
.Range("B1").FormulaR1C1 = "Unit"
.Range("C1").FormulaR1C1 = "Charge A"
.Range("D1").FormulaR1C1 = "Charge B"
.Range("E1").FormulaR1C1 = "Charge c"
.Range("A1").Select

'Insert Free Credit Column
.Columns("E:E").Insert
.Range("E1").FormulaR1C1 = "Free Credit"
.Range("e2").FormulaR1C1 = "0"
.Range("A1").Select
' .Selection.End(xlDown).Select
' lastrow = ActiveCell.Row
' .Range("e2:e" & lastrow).Select
' .Selection.FillDown Selection.FillDown

' aligns all data to the left as text
.Columns("A:A").Select
.Columns("A:A").EntireColumn.AutoFit
.Columns("A:A").NumberFormat = "@"
.Columns("A:A").HorizontalAlignment = xlLeft
.Range("a1").Select

End With

OpenExcelWorkbook_Exit:
Set xlsApp = Nothing
Set xlsWorkbook = Nothing
Set xlsSheet = Nothing
Exit Sub

OpenExcelWorkbook_Err:
MsgBox "Something happened, try again" & vbCrLf & Err.Number & " - " &
Err.Description
Resume OpenExcelWorkbook_Exit


Thanks again for your input.

Lynn

End Sub
Jamie Collins said:
This is an Excel problem, not an Access one, so it would have made more
sense to ask in an Excel forum.
Click to expand...

It sounds to me that Excel may not be required.

Unwanted rows could be filtered out using an appropriate WHERE clause.
Unwanted columns could simply be omitted from the SELECT clause. And
creating a column of zeros is simplicity itself e.g.

SELECT 0 AS [Free Credit]

Formatting is not imported into MS Access anyhow, unless the intention
is to affect a column's data type, in which case this could be done
using a schema.ini file or import specs.

The OP would need to post some sample data and expected results for us
to come up with an appropriate query.

Jamie.
Click to expand...
 
FYI, I was given the equivalent in Access

With xlsSheet
.Columns("E:E").Insert
.Range("E1").FormulaR1C1 = "Free Credit"
.Range("e2").FormulaR1C1 = "0"
.Range("A1").Select
lastrow = xlsSheet.Range("A1").End(xlDown).Row
.Range("e2:e" & lastrow).FillDown
end with


Jamie Collins said:
This is an Excel problem, not an Access one, so it would have made more
sense to ask in an Excel forum.
Click to expand...

It sounds to me that Excel may not be required.

Unwanted rows could be filtered out using an appropriate WHERE clause.
Unwanted columns could simply be omitted from the SELECT clause. And
creating a column of zeros is simplicity itself e.g.

SELECT 0 AS [Free Credit]

Formatting is not imported into MS Access anyhow, unless the intention
is to affect a column's data type, in which case this could be done
using a schema.ini file or import specs.

The OP would need to post some sample data and expected results for us
to come up with an appropriate query.

Jamie.
Click to expand...
 
FYI, I was given the equivalent in Access
With xlsSheet
.Columns("E:E").Insert
.Range("E1").FormulaR1C1 = "Free Credit"
.Range("e2").FormulaR1C1 = "0"
.Range("A1").Select
lastrow = xlsSheet.Range("A1").End(xlDown).Row
.Range("e2:e" & lastrow).FillDown
end with
Click to expand...



With xlsSheet
.Columns("E:E").Insert
.Range("E1").FormulaR1C1 = "Free Credit"
.Range("A1").Select
lastrow = xlsSheet.Range("A1").End(xlDown).Row
.Range("e2:e" & lastrow).value = "0"
end with
 
Hi Lynn,

Having seen your code I agree with Jamie that there's nothing that
really requires Excel. If it's a proper CSV file with the same fields in
each line then a fairly straightforward query will do the job; if the
header and totals lines have odd structures then it may be necessary to
write some VBA code to dispose of them.

Thanks for your responses. I understand that you might think that this is an
excel issue but I don't believe it is. I have automated the procedure in
Excel already and am now trying to convert that code to the Access equivalent
so that anyone could modify the csv file in Excel before importing it into
accesss without having to do it manually.

All the code is now stored in an Access module as I am trying to simplify a
procedure. I don't want the user to open excel to do this anymore. I wanted
to ensure that file names are stored in a consistent location and that the
changes to the csv file are performed consistently and correctly.

I have been able to accomplish all the manipulation of the excel spreadsheet
through code in Access except adding zeros all the way down a column that has
been inserted.

I have already written a simple update query to replace all null values with
zeros but I would really like to figure out how to control using autofill in
Excel from Access for future reference.

I have enclosed the code that I use in Access to perform all the other
manipulation, maybe that will trigger some ideas.

Private Sub cmdFiletoModify_Click()
On Error GoTo OpenExcelWorkbook_Err

Dim strFilter As String
Dim strInputFileName As String
Dim xlsApp As Excel.Application
Dim xlsWorkbook As Workbook
Dim xlsSheet As Worksheet
Dim mystrfilename As String
Dim lastrow As Variant

If fIsAppRunning("Excel") Then
Set xlsApp = GetObject(, "Excel.Application")
boolXL = False
Else
Set xlsApp = CreateObject("Excel.Application")
boolXL = True
End If


With xlsApp
.Visible = True
.Workbooks.Open strInputFileName

End With

Set xlsWorkbook = ActiveWorkbook
Set xlsSheet = ActiveSheet

mystrfilename = strInitDir & "INVOICED_" & xlsSheet.Name & ".xls"
xlsSheet.Parent.SaveAs FileName:=mystrfilename, FileFormat:=xlNormal
txtImportFile.Visible = False
txtImportFile = mystrfilename


'Run File Correction Stuff

' removes the existing heading rows
With xlsSheet
.Rows("1:13").Delete xlUp
.Rows("1:1").Insert xlDown
End With
'finds the total lines and deletes them
With xlsSheet
.Cells.Find(What:="**TOTAL", After:=ActiveCell, LookIn:=xlFormulas,
Lookat:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False).Activate
.Rows(ActiveCell.Row).Delete
.Cells.Find(What:="**CREDIT", After:=ActiveCell, LookIn:=xlFormulas,
Lookat:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False).Activate
.Rows(ActiveCell.Row).Delete
.Cells.Find(What:="**SOFTWARE", After:=ActiveCell,
LookIn:=xlFormulas, Lookat:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext, MatchCase:=False).Activate
.Rows(ActiveCell.Row).Delete
.Cells.Find(What:="**GRAND", After:=ActiveCell, LookIn:=xlFormulas,
Lookat:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False).Activate
.Rows(ActiveCell.Row).Delete
End With

'Delete unnecessary columns'
With xlsSheet
.Columns("A:B").Delete
.Columns("B:B").Delete
.Columns("C:E").Delete

'Inserts new row headings
.Range("a1").FormulaR1C1 = "SerialNo"
.Range("B1").FormulaR1C1 = "Unit"
.Range("C1").FormulaR1C1 = "Charge A"
.Range("D1").FormulaR1C1 = "Charge B"
.Range("E1").FormulaR1C1 = "Charge c"
.Range("A1").Select

'Insert Free Credit Column
.Columns("E:E").Insert
.Range("E1").FormulaR1C1 = "Free Credit"
.Range("e2").FormulaR1C1 = "0"
.Range("A1").Select
' .Selection.End(xlDown).Select
' lastrow = ActiveCell.Row
' .Range("e2:e" & lastrow).Select
' .Selection.FillDown Selection.FillDown

' aligns all data to the left as text
.Columns("A:A").Select
.Columns("A:A").EntireColumn.AutoFit
.Columns("A:A").NumberFormat = "@"
.Columns("A:A").HorizontalAlignment = xlLeft
.Range("a1").Select

End With

OpenExcelWorkbook_Exit:
Set xlsApp = Nothing
Set xlsWorkbook = Nothing
Set xlsSheet = Nothing
Exit Sub

OpenExcelWorkbook_Err:
MsgBox "Something happened, try again" & vbCrLf & Err.Number & " - " &
Err.Description
Resume OpenExcelWorkbook_Exit


Thanks again for your input.

Lynn

End Sub
Jamie Collins said:
John Nurick said:
I have to perform an import from an excel file to Access. The file comes
originally as a csv file. I have to delete some rows and columns, then
change the formatting of some columns.
I have been able to do everything but fill a column with zeros
Click to expand...
This is an Excel problem, not an Access one, so it would have made more
sense to ask in an Excel forum.
Click to expand...

It sounds to me that Excel may not be required.

Unwanted rows could be filtered out using an appropriate WHERE clause.
Unwanted columns could simply be omitted from the SELECT clause. And
creating a column of zeros is simplicity itself e.g.

SELECT 0 AS [Free Credit]

Formatting is not imported into MS Access anyhow, unless the intention
is to affect a column's data type, in which case this could be done
using a schema.ini file or import specs.

The OP would need to post some sample data and expected results for us
to come up with an appropriate query.

Jamie.
Click to expand...
Click to expand...
 
FYI, I was given the equivalent in Access

With xlsSheet
.Columns("E:E").Insert
.Range("E1").FormulaR1C1 = "Free Credit"
.Range("e2").FormulaR1C1 = "0"
.Range("A1").Select
lastrow = xlsSheet.Range("A1").End(xlDown).Row
.Range("e2:e" & lastrow).FillDown
end with
Click to expand...

There's nothing specific to Access about that, and automation code
should in general avoid using the Select method or Selection object
because these may not work as expected. For instance, this
.Range("A1").Select
Click to expand...
fails when the Excel window is hidden - which one normally wants it to
be for this sort of work.
 
Back
Top