use access to open excel

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

Guest

i need to automate a process where a user will import different excel books
to access. the problem is that the first 2 columns need to be text, because
the user has alpha numeric data in those columns. currently, when importing
access thinks it is numeric and as a result i get conversion errors.

i tried this code but did not work, as i still got conversion errors.
'Set xlapp = CreateObject("excel.application")
'Set xlbookformatted = xlapp.workbooks.Open("g:\aaa_rmsco\fee schedule
updates\facility.xls", 3)
'Set xlsheet = xlbookformatted.worksheets("rate sheet")
'With xlapp
' .Visible = False
' .xlbookformatted.xlsheet.Columns("A").numberformat = "@"
' .xlbookformatted.Save
' .xlbookformatted.Close
' .Close
' .Visible = True
' End With

'Set xlapp = Nothing
'Set xlbookformatted = Nothing

however, when i generated an excel macro whereby i did a text to column, i
got success.

what i need to know is how i can take this text to column logic that came
from excel, and build in access vb. i do not have documentation that shows
how to build this logic.

here is the excel generated code

Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False,
FieldInfo _
:=Array(1, 2)


also is there a way, i can rename the tab on the excel sheet to be rate sheet.

all the books are labelled differently, and want to change that name to rate
sheet,

to follow the code above


thanks for your help
 
xlSheet.Columns(1).TextToColumns
xlSheet.Name = "Rate Sheet"

or....
xlSheet.Columns(1).NumberFormat = "@"
 
when i coded the vb in access with these concepts, i got an out of range
subscript.

i have to open the excel book in access vb, change the workbook, close the
workbook, then do a transferspreadsheet to access.

here is what i have

DoCmd.OpenQuery "qry delete records in rmsco file"
DoCmd.OpenQuery "qry delete records in update file"
Set xlapp = CreateObject("excel.application")
Set xlbookformatted = xlapp.workbooks.Open("c:\aaa_rmsco\fee schedule
updates\facility.xls", 3)
'Set xlsheet = xlbookformatted.worksheets("rate sheet")
' the above xlsheet was the orig code
With xlapp
.Visible = False
.xlbookformatted.xlsheet.columns(1).texttocolumns
.xlbookformatted.xlsheet.Name = "rate sheet"
.xlbookformatted.Save
.xlbookformatted.Close
.Close
.Visible = True
End With

Set xlapp = Nothing
Set xlbookformatted = Nothing

numberformat = "@" for some reason does not translate everything because i
still get conversion errors.


so , i am having trouble on how to set the code inside access vb for text to
column

thanks for your help
 
Delete what you have and use:

Set xlApp = CreateObject("Excel.Application")
Set xlSheet = xlApp.Workbooks.Open(strFile).Sheets(1)
xlSheet.Name = "Rate Sheet"
xlSheet.Columns(1).NumberFormat = "@"
Set xlSheet = Nothing
Set xlApp = Nothing
 
Make that:

Set xlApp = CreateObject("Excel.Application")
Set xlSheet = xlApp.Workbooks.Open(strFile).Sheets(1)

xlSheet.Name = "Rate Sheet"
xlSheet.Columns(1).NumberFormat = "@"

xlApp.ActiveWorkbook.Save
xlApp.ActiveWorkbook.Close
xlApp.Quit

Set xlSheet = Nothing
Set xlApp = Nothing
 
here is all my code that is in access vb

'RMSCO - program processes deletes and new codes to get final data base for
RMSCO. This is not the code
' that RMSCO has. Check the data base entitled RMSCO for that code.
Public var
Dim strpathname As String
Dim strpathname2 As String
Dim strpathname3 As String
Dim strpathname4 As String
Dim deletertn As String

Dim xlapp As Object
Dim xlbookformatted As Object
Dim xlsheet As Object
Dim strfile As Object

Private Sub adds_Click()
DoCmd.OpenReport "new records", acViewPreview
End Sub

Private Sub delete_record_Click()
DoCmd.OpenReport "deleted records", acViewPreview
End Sub

Private Sub Form_Open(Cancel As Integer)
DoCmd.Maximize

End Sub



Private Sub close_form_Click()
On Error GoTo Err_close_form_Click
DoCmd.Close
DoCmd.Quit

Exit_close_form_Click:
Exit Sub

Err_close_form_Click:
MsgBox "closing form"
Resume Exit_close_form_Click

End Sub


Private Sub merge_updates_Click()

'facility processing starts here
DoCmd.SetWarnings False

DoCmd.OpenQuery "qry delete records in rmsco file"
DoCmd.OpenQuery "qry delete records in update file"
'Set strfile = "c:\aaa_rmsco\fee schedule updates\facility.xls"
Set xlapp = CreateObject("excel.application")
Set xlbookformatted = xlapp.workbooks.Open("c:\aaa_rmsco\fee schedule
updates\facility.xls").sheets(1)
'Set xlsheet = xlbookformatted.worksheets("rate sheet")
xlsheet.Name = "rate sheet"
xlsheet.columns(1).numberformat = "@"
xlapp.activeworkbook.Save
xlapp.activeworkbook.Close
xlapp.Quit
'With xlapp
' .Visible = False
' .xlbookformatted.xlsheet.columns(1).texttocolumns
' .xlbookformatted.xlsheet.Name = "rate sheet"
' .xlbookformatted.xlsheet.Columns("A").numberformat = "@"
' .xlbookformatted.Save
' .xlbookformatted.Close
' .Close
' .Visible = True
'End With

Set xlapp = Nothing
Set xlsheet = Nothing

i get an object variable or with block variable not set using this concept


i tried setting the variable strfile equal to the path and then put strfile
in the open statement that you had. got an error.

as i indicated before, number format does not work in this case. it has to
be text to column. i know that because i manually changed the file using that
concept. it then imported without conversion errors.

remember, this is not in excel vb, but access. so i think a with has to be
used.

thanks
 
Get rid of xlbookformatted. Get rid of WITH.

Use:
Set xlSheet = xlApp.Workbooks.Open(strFile).Sheets(1)

It is possible that you get a file error because you have a crashed instance
of Excel open. Open your task manager and look for any Excel processes. Get
totally out of Excel - the file cannot be open or in a crashed state.
 
hi, that worked. thanks very much

but now i get the issue, where the orig excel book is locked. when i try to
open, i get a read only option. plus it took a long time to import (45 sec)
a 61,000 record file.

thoughts?

i have a client that is not computer savy, so i have to make this totally
turnkey,.

the file is read into access, do a bunch of queries for updates, and then
give 2 excel files as output
 
It sounds like the file is not closed properly. After you finish a run,
check for open Excel processes in task manager. As for the import being
slow, do you mean it used to be fast ?
 
yes, but that is when i had prepared the file manually. at that point it was
a straight transfer
 
thought it was finally there, but i did another import of another file. still
get conversion errors with this code but when i manually do a text to column,
it works.

is there anyway to code text to column in vb access?
thanks
 
Did you use:

Set xlApp = CreateObject("Excel.Application")
Set xlSheet = xlApp.Workbooks.Open(strFile).Sheets(1)

xlSheet.Name = "Rate Sheet"
xlSheet.Columns(1).TextToColumns

xlApp.ActiveWorkbook.Save
xlApp.ActiveWorkbook.Close
xlApp.Quit

Set xlSheet = Nothing
Set xlApp = Nothing


Don't know what you expect to achieve with that, though. TextToColumns is
used to parse one column into many columns. That does not reformat as text.

What happens when you manually apply NumberFormat="@" and then import ?
That is the way to go.
 
hi, yes to the first part. i am indicating text to column because that is
what appears on the data drop down on excel. when i go thru the 3 screens in
excel and choose text at the end, i get success.

when i manually do the numberformat, i get conversion errors when i import.
 
I wouldn't want to rely on TextToColumn personally, since you don't know how
your column will be parsed out. You're asking for trouble with that approach.

Since you are having so many problems with statements that should work
properly, why don't you just write a little import function to read each
Excel line, and then write it to the table with the formats that you want.
That will guarantee the import will work in any situation, and that the data
will be in the format you want. Do you know how ?

One last thing to check first, though, is that your Excel file does not have
extra NULL columns all the way to the right. To verify, press CTRL+END in
the spreadsheet and you should move to the last cell with data. Should you
be in a column that has no data, delete it, and import again.
 
Back
Top