UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V  1 2 >  (Go to first unread post)
   Reply to this topicStart new topic
> Importing Excel Via Vba, Access 2013    
 
   
LeeAnn
post Nov 15 2019, 02:22 PM
Post#1



Posts: 1,291
Joined: 2-February 00
From: Mississippi USA Central Time Zone


I have an Excel spreadsheet with several sheets that I need to import into Access with a click of a button. When I import the data in Access it makes some of the fields import as a number. Some of the data though errors out on import because it thinks it is a text (even though it is definingly a number). I open the Excel spreadsheet and I do see that it states that "The number in this cell is formatted text or proceeded by an apostrophe" and I know that this is what is causing the problem. Is there some way I can tell access when importing how I want the fields to be imported as and not try to guess. I will be importing several excel sheets and they can all have data like this. I would like to tell access that all the fields are text and not numbers (that way at least I can get ALL the data in, then I can fix it as I need to), but there is no way in TransferSpreadsheet that I see that you can tell Access how to import your data, it just guesses what the field type is and if the data can't be imported in it ignores it and import what it can. This is what I have always hated about importing data from Excel into Access via VBA. If you manually do it you can specify that the data can be treated as text but you cannot do so in VBA.

Thanks for any assistance that can be provided. I hope I made it clear what I need.

--------------------
Lee Ann Davidson
Go to the top of the page
 
theDBguy
post Nov 15 2019, 02:35 PM
Post#2


UA Moderator
Posts: 76,828
Joined: 19-June 07
From: SunnySandyEggo


Hi. If you're stuck with importing the data from Excel rather than a Text file, then one approach is to modify the Excel file first and add one or two empty rows at the top to help Access "guess" the columns are Text. Hope it helps...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
LeeAnn
post Nov 15 2019, 02:48 PM
Post#3



Posts: 1,291
Joined: 2-February 00
From: Mississippi USA Central Time Zone


Does not work, the field still tries to import it in as a number field. This is making me VERY frustrated. Microsoft has made my life the last few weeks a bit of a living hot place....

--------------------
Lee Ann Davidson
Go to the top of the page
 
theDBguy
post Nov 15 2019, 02:57 PM
Post#4


UA Moderator
Posts: 76,828
Joined: 19-June 07
From: SunnySandyEggo


Okay, since you are able to modify the Excel file first, what happens if you tried the following:

1. Highlight all columns and set the Format to Text, or
2. Add an empty row at the top but populate all columns with a space character or a single apostrophe

Try each of the above one at a time to see if they work individually. Let us know...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
LeeAnn
post Nov 15 2019, 03:06 PM
Post#5



Posts: 1,291
Joined: 2-February 00
From: Mississippi USA Central Time Zone


That works but I cannot use that unless Access can place a row with am apostrophe before it attempts the import. Like I said it needs to be completely automated, no human effort. Why did Microsoft now allow for users to specify how they want their data imported in????

--------------------
Lee Ann Davidson
Go to the top of the page
 
tina t
post Nov 15 2019, 03:11 PM
Post#6



Posts: 6,181
Joined: 11-November 10
From: SoCal, USA


LeeAnn, i wonder if it's possible to automate inserting a row, with Excel VBA. you might try posting in an Excel forum. if it can be automated in Excel, then perhaps you use the same (or almost the same) code to manipulate the Excel file remotely from VBA running in Access.

another possibility, i'm wondering if the file can be remotely opened and saved as a text file. if that's possible, you could then create an import specification in Access to manage the import of the text file, that will allow you much more control than importing an Excel file type.

hth
tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 
MadPiet
post Nov 15 2019, 03:12 PM
Post#7



Posts: 3,364
Joined: 27-February 09



Is it not an option to pre-build the destination table and then just use an import specification?
Go to the top of the page
 
LeeAnn
post Nov 15 2019, 03:25 PM
Post#8



Posts: 1,291
Joined: 2-February 00
From: Mississippi USA Central Time Zone


This is getting my head hurting really bad. This should be a no brainer (really, all I need to tell Access to do is import ALL the data as text, stop trying to guess what I want it to import it as). MadPiet that sounds like you have a good idea, but how can I make one specification work for all my sheets. Is that possible, it looks like you have to have a sperate import specification for each sheet (every sheet has the same build).

I may try your idea tina, to open, save as a text and then import it as a text.

Again this seems to me something that would be quite easy to do but is not. I have seen where things that are going to be hard but then turn out pretty easy, tipsy turvy world we live in as my grandmother would have said. Can drive me insane sometimes.

Thanks for the help.


--------------------
Lee Ann Davidson
Go to the top of the page
 
theDBguy
post Nov 15 2019, 03:31 PM
Post#9


UA Moderator
Posts: 76,828
Joined: 19-June 07
From: SunnySandyEggo


Doing this thing manually is cumbersome; but once automated, all the user has to do is hit a button. All the ideas presented so far is very possible using code. If you'll also notice, I hinted on using a Text file earlier, because then you can create an import spec.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
MadPiet
post Nov 15 2019, 03:42 PM
Post#10



Posts: 3,364
Joined: 27-February 09



Import to an existing table with all the fields defined as text?
Go to the top of the page
 
LeeAnn
post Nov 15 2019, 03:43 PM
Post#11



Posts: 1,291
Joined: 2-February 00
From: Mississippi USA Central Time Zone


OK how would I get access to convert the file to text? I agree since Access wants to control how my data is imported into it via Excel, maybe text is a way to go. So I suppose I would open the file in Access, save the sheet as a text, close Excel, import the file into Access and whamo!!! I agree that I can get this done but it is WAY too much then what is should be. Just saying. It should be much easier to control.

Thanks again for the help.

--------------------
Lee Ann Davidson
Go to the top of the page
 
theDBguy
post Nov 15 2019, 04:01 PM
Post#12


UA Moderator
Posts: 76,828
Joined: 19-June 07
From: SunnySandyEggo


Hi Lee Ann. Are you familiar with Excel Automation? You can use the WorkSheet.SaveAs method to save an Excel sheet into a CSV file.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
LeeAnn
post Nov 15 2019, 05:02 PM
Post#13



Posts: 1,291
Joined: 2-February 00
From: Mississippi USA Central Time Zone


OK I think I can get this licked. I am opening the Excel File from Access, selecting the needed sheet, saving it into a text file, importing the text file into Access, then deleting the text file, moving to next sheet and doing the import. It looks like it might work. But it would have been so much easier if I could tell Access how to import the Excel information. I did something like this a long time ago and it was a pain as well. Just wish Microsoft can fix that issue. I have had a lot of issues when importing data from Excel and not all the data would come over because Access puked on the data. But with text files it looks like I will be able to do what I need.

Thanks again, all of you helped me think outside the box a bit. I hope I can get it to work (as it looks like I can) but I will continue the quest on Monday. I usually do this process for my customer as I manually do all the steps. I would like to get the customer not to be so dependent on me unless there is a serious issue. I have to import these files about every month or so. Would like for the customer to click a button and have it process for him. One other issue is that the spreadsheets they get tend to change somewhat but I am going to tell the customer to tell the people who provide him the spreadsheets that little issue will stop once this gets into production. That is one reason why I usually have to import the data because they change the format. The thing is one day I may not be here and I would hate for my customer to be up a creek without a paddle.

Thanks again and as usual uarulez2.gif and it is time for this cowgirl to mosey on to the bunkhouse to grab a few cheers.gif . Have a great and wonderful weekend.

--------------------
Lee Ann Davidson
Go to the top of the page
 
theDBguy
post Nov 15 2019, 05:10 PM
Post#14


UA Moderator
Posts: 76,828
Joined: 19-June 07
From: SunnySandyEggo


Hi Lee. Glad to hear you're seeing the light at the end of the tunnel now. Have a great weekend and good luck on Monday and the rest of the week. Cheers!

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
LeeAnn
post Nov 18 2019, 09:51 AM
Post#15



Posts: 1,291
Joined: 2-February 00
From: Mississippi USA Central Time Zone


I am still having issues. I can make the text file fine. But when it comes to exporting the file I get an error stating that the file is locked. Here is the whole function and near the bottom I have marked where the error is occurring. I cannot see how it can be locked as I have closed Excel down before I do the transfer. Any ideas would be helpful as I am scratching my head on why this is so difficult.... pullhair.gif Help mw UtteAccess you are my only hope.....



CODE
Function ImportCardInformation()

On Error GoTo Err_Trap

Dim obj As Object
Dim AppExcel As Excel.Application 'Excel app
Dim myWorkBook As Excel.Workbook 'workbook
Dim TCCDatabase As dao.Database 'current database
Dim iSheetCount As Integer 'count of the sheets that are in the workbook
Dim booFoundSheet As Integer ' Did we find the worksheet?
Dim CurrentSheetID As Integer 'current id of the current sheet
Dim TCCSpreadSheetTabs As dao.Recordset 'recordset that holds the name of the sheets to be imported





Set TCCDatabase = CurrentDb
Set TCCSpreadSheetTabs = TCCDatabase.OpenRecordset("tSpreadSheetName") 'tabl that holds the spreadsheet names to be processed.

'Excel Automation
Set AppExcel = CreateObject("Excel.Application")
Set myWorkBook = AppExcel.Workbooks.Open(CurrentProject.Path & Chr(92) & "CardFile.xlsx")


currentFile = CurrentProject.Path & Chr(92) & "CardFile.xlsx" 'location of the spreadsheet

If Dir(currentFile) = "" Then 'No file found, tell user and get out
    MsgBox "No CardFile.xlsx exists in the current folder, please check to see if the file is there and is named correctly (must be CardFile.xlsx, no spaces)", vbOKOnly, "File Does Not Exists"
    Exit Function
End If


'warn user that the current cardfile table will be destoyed and do they want to go.
If MsgBox("Do you wish to import the spreadsheet CardFile into the Application?  This will destroy the current TCC Data table and rebuild it.", vbYesNo, "Rebuild TCC Data table") = vbYes Then
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "qryDeleteTTCDataInformation" 'Kansas is about to go bye bye
Else
    DoCmd.Quit
End If





TCCSpreadSheetTabs.MoveFirst
If TCCSpreadSheetTabs.RecordCount = 0 Then 'if no records found tell user and exit
    MsgBox "Please Input the spreadsheet tab names to import into the table displayed on the form.", vbOKOnly, "No Records Found."
Else

    
While Not TCCSpreadSheetTabs.EOF

    mySheetName = TCCSpreadSheetTabs!SpreadSheetName
    booFoundSheet = 0
    For iSheetCount = 1 To AppExcel.Sheets.Count 'run through the sheets to see if the sheet is present
        
        If (AppExcel.Sheets(iSheetCount).Name) = mySheetName Then
            booFoundSheet = 1
            
        End If
    Next
    If booFoundSheet = 0 Then 'if no sheet found tell user that it is not there and do they want to continue
        If MsgBox("No " & mySheetName & " tab found, do you wish to continue?", vbYesNo, "Continue?") = vbNo Then
            Exit Function
        End If
    End If
TCCSpreadSheetTabs.MoveNext
Wend

'close out Excel so we can process it to make the text file and import it in
'Set mySheet = Nothing
Set myWorkBook = Nothing
AppExcel.Quit
Set AppExcel = Nothing


'Now we actually process the file
TCCSpreadSheetTabs.MoveFirst
While Not TCCSpreadSheetTabs.EOF

   Set AppExcel = CreateObject("Excel.Application")
   Set myWorkBook = AppExcel.Workbooks.Open(CurrentProject.Path & Chr(92) & "CardFile.xlsx")
   mySheetName = TCCSpreadSheetTabs!SpreadSheetName
     booFoundSheet = 0
     'go throug the sheets
    For iSheetCount = 1 To AppExcel.Sheets.Count
        
        If (AppExcel.Sheets(iSheetCount).Name) = mySheetName Then
            booFoundSheet = 1
            CurrentSheetID = iSheetCount
        End If
    Next
    If booFoundSheet = 1 Then
        
        AppExcel.Sheets(CurrentSheetID).Select 'Grab the sheet
        ActiveWorkbook.SaveAs FileName:=CurrentProject.Path & Chr(92) & "CardFile.txt", FileFormat:=xlTextWindows 'make text file
        Set myWorkBook = Nothing
        AppExcel.Quit
        Set AppExcel = Nothing
        DoCmd.TransferText acImportDelim, , "CardFile", CurrentProject.Path & Chr(92) & "CardFile.txt", True '****** ERROR HERE STATING THAT THE FILE IS LOCKED
        DoCmd.OpenQuery "qryImportCardFile" 'insert the records into the file
        DoCmd.DeleteObject acTable, "CardFile" 'delete the current cardfile table
        Kill (CurrentProject.Path & Chr(92) & "CardFile.txt") 'Delete the text file so we can process the next one
    End If

TCCSpreadSheetTabs.MoveNext 'move to the next sheet ti import
Wend
'Close all objects
'Set mySheet = Nothing
Set myWorkBook = Nothing
'AppExcel.Quit
Set AppExcel = Nothing

TCCSpreadSheetTabs.Close
Set TCCSpreadSheetTabs = Nothing
Set TCCDatabase = Nothing
MsgBox "Import complete.  Verfiy the records have been imported by openbing the Front End and going through the Trial Event Form.", vbOKOnly, "Import Done!"

End If



Exit Function

Err_Trap:

MsgBox "Error " & Err.Number & ": " & Err.Description, vbOKOnly, "Error Encountered."
Set mySheet = Nothing
Set myWorkBook = Nothing
AppExcel.Quit
Set AppExcel = Nothing

TCCSpreadSheetTabs.Close
Set TCCSpreadSheetTabs = Nothing
Set TCCDatabase = Nothing
Exit Function


End Function

--------------------
Lee Ann Davidson
Go to the top of the page
 
PaulBrand
post Nov 18 2019, 09:57 AM
Post#16



Posts: 1,743
Joined: 4-September 02
From: Oxford UK


You don't need to all this. You can use an Import Specification in the Access import wizard

--------------------
Paul
Go to the top of the page
 
cheekybuddha
post Nov 18 2019, 10:02 AM
Post#17


UtterAccess Moderator
Posts: 11,893
Joined: 6-December 03
From: Telegraph Hill


Lee Ann,

You turn off the warnings and I don't see where you turn them back on again. You won't see any errors afterwards. blush.gif

There are a lot of other oddities (eg you only check whether the file exists after you have tried to open it!)

--------------------


Regards,

David Marten
Go to the top of the page
 
LeeAnn
post Nov 18 2019, 10:40 AM
Post#18



Posts: 1,291
Joined: 2-February 00
From: Mississippi USA Central Time Zone


Not sure Paul what you mean I don't need this. I need it as the sheets can change names.

Got you on the opening of the file, that should be done before the Excel properties are set, I will fix that and will turn the warnings back on at the very end.

But can anyone explain why Excel is holding on the text file even though I have closed Excel down? This is where I need help with. Everything works until I try to import the file, the text file is locked and I cannot import it in. I am still at a loss as to why this is so difficult. I think my logic is sound, right???? I check to see of the file is there, if not exit. I open Excel, I check to see if the sheets exists, if they don't I warn the user that hey do not and if they want to continue. I close Excel.

I start going through the list, I open Excel, Grab the data, save it, close excel and go to the next sheet and start the next sheet until all the sheets are processed. For some reason Excel closes but thinks that it should hold on to text file and prohibit me from using it.

--------------------
Lee Ann Davidson
Go to the top of the page
 
LeeAnn
post Nov 18 2019, 01:07 PM
Post#19



Posts: 1,291
Joined: 2-February 00
From: Mississippi USA Central Time Zone


After much hair pulling and just so much frustration of something that I thought would be a no brainer. I did have some major issues on how I was making the text file, I should be using = instead of := and I had to close the worksheet after saving it to text (something I was unaware of). So here is the code that actually works like I want. Will test it when the next file comes in but it works with one of the old files. Still I think that Access does not do a good job when importing from Excel, I wish you could tell it how you want the data imported in rather than it making assumptions, but this works. uarulez2.gif and thanks.gif

CODE
Function ImportCardInformation()

On Error GoTo Err_Trap

Dim obj As Object
Dim AppExcel As Excel.Application 'Excel app
Dim myWorkBook As Excel.Workbook 'workbook
Dim TCCDatabase As dao.Database 'current database
Dim iSheetCount As Integer 'count of the sheets that are in the workbook
Dim booFoundSheet As Integer ' Did we find the worksheet?
Dim CurrentSheetID As Integer 'current id of the current sheet
Dim TCCSpreadSheetTabs As dao.Recordset 'recordset that holds the name of the sheets to be imported
Dim strFileName As String




Set TCCDatabase = CurrentDb
Set TCCSpreadSheetTabs = TCCDatabase.OpenRecordset("tSpreadSheetName") 'tabl that holds the spreadsheet names to be processed.

'Excel Automation
Set AppExcel = CreateObject("Excel.Application")
Set myWorkBook = AppExcel.Workbooks.Open(CurrentProject.Path & Chr(92) & "CardFile.xlsx")


currentFile = CurrentProject.Path & Chr(92) & "CardFile.xlsx" 'location of the spreadsheet
strFileName = CurrentProject.Path & Chr(92) & "CardFile.txt" 'name of text file

If Dir(currentFile) = "" Then 'No file found, tell user and get out
    MsgBox "No CardFile.xlsx exists in the current folder, please check to see if the file is there and is named correctly (must be CardFile.xlsx, no spaces)", vbOKOnly, "File Does Not Exists"
    Exit Function
End If


'warn user that the current cardfile table will be destoyed and do they want to go.
If MsgBox("Do you wish to import the spreadsheet CardFile into the Application?  This will destroy the current TCC Data table and rebuild it.", vbYesNo, "Rebuild TCC Data table") = vbYes Then
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "qryDeleteTTCDataInformation" 'Kansas is about to go bye bye
    DoCmd.SetWarnings True
Else
    DoCmd.Quit
End If





TCCSpreadSheetTabs.MoveFirst
If TCCSpreadSheetTabs.RecordCount = 0 Then 'if no records found tell user and exit
    MsgBox "Please Input the spreadsheet tab names to import into the table displayed on the form.", vbOKOnly, "No Records Found."
Else

    
While Not TCCSpreadSheetTabs.EOF

    mySheetName = TCCSpreadSheetTabs!SpreadSheetName
    booFoundSheet = 0
    For iSheetCount = 1 To AppExcel.Sheets.Count 'run through the sheets to see if the sheet is present
        
        If (AppExcel.Sheets(iSheetCount).Name) = mySheetName Then
            booFoundSheet = 1
            
        End If
    Next
    If booFoundSheet = 0 Then 'if no sheet found tell user that it is not there and do they want to continue
        If MsgBox("No " & mySheetName & " tab found, do you wish to continue?", vbYesNo, "Continue?") = vbNo Then
            Exit Function
        End If
    End If
TCCSpreadSheetTabs.MoveNext
Wend

'close out Excel so we can process it to make the text file and import it in
'Set mySheet = Nothing
Set myWorkBook = Nothing
AppExcel.Quit
Set AppExcel = Nothing


'Now we actually process the file
TCCSpreadSheetTabs.MoveFirst
While Not TCCSpreadSheetTabs.EOF

   Set AppExcel = CreateObject("Excel.Application")
   Set myWorkBook = AppExcel.Workbooks.Open(CurrentProject.Path & Chr(92) & "CardFile.xlsx")
   mySheetName = TCCSpreadSheetTabs!SpreadSheetName
     booFoundSheet = 0
     'go throug the sheets
    For iSheetCount = 1 To AppExcel.Sheets.Count
        
        If (AppExcel.Sheets(iSheetCount).Name) = mySheetName Then
            booFoundSheet = 1
            CurrentSheetID = iSheetCount
        End If
    Next
    If booFoundSheet = 1 Then
        
        AppExcel.Sheets(CurrentSheetID).Select 'Grab the sheet
        DoCmd.SetWarnings False
        AppExcel.ActiveWorkbook.SaveAs strFileName, xlTextWindows 'changed it to a string value
        AppExcel.ActiveWorkbook.Saved = True 'added this but I probably really dont need it but keeping it in
        AppExcel.ActiveWorkbook.Close (False) 'closing the workboot
        
        Set myWorkBook = Nothing
        AppExcel.Quit
        
        Set AppExcel = Nothing
        
        
        DoCmd.TransferText acImportDelim, "CardFile Import Specification", "CardFile", CurrentProject.Path & Chr(92) & "CardFile.txt", True '****** NO MORE ERROR!!!!!!!!!
        DoCmd.OpenQuery "qryImportCardFile" 'insert the records into the file
        DoCmd.DeleteObject acTable, "CardFile" 'delete the current cardfile table
        Kill (CurrentProject.Path & Chr(92) & "CardFile.txt") 'Delete the text file so we can process the next one
        DoCmd.SetWarnings True
    End If

TCCSpreadSheetTabs.MoveNext 'move to the next sheet to import
Wend
'Close all objects
'Set mySheet = Nothing
Set myWorkBook = Nothing
'AppExcel.Quit
Set AppExcel = Nothing

TCCSpreadSheetTabs.Close
Set TCCSpreadSheetTabs = Nothing
Set TCCDatabase = Nothing
DoCmd.SetWarnings False
DoCmd.OpenQuery "QryDeleteBlankRecords" 'remove any records that do not have a valid event number"
DoCmd.SetWarnings True
MsgBox "Import complete.  Verfiy the records have been imported by openbing the Front End and going through the Trial Event Form.", vbOKOnly, "Import Done!"

End If



Exit Function

Err_Trap:
'We have hit an error
MsgBox "Error " & Err.Number & ": " & Err.Description, vbOKOnly, "Error Encountered."
Set mySheet = Nothing
Set myWorkBook = Nothing
Set AppExcel = Nothing

TCCSpreadSheetTabs.Close
Set TCCSpreadSheetTabs = Nothing
Set TCCDatabase = Nothing
Exit Function


End Function

--------------------
Lee Ann Davidson
Go to the top of the page
 
theDBguy
post Nov 18 2019, 01:10 PM
Post#20


UA Moderator
Posts: 76,828
Joined: 19-June 07
From: SunnySandyEggo


Hi Lee Ann. Glad to hear you got it sorted out. Regarding your concern with how Access imports Excel files, have you considered submitting it to Microsoft via the Access User Voice? Good luck with your project.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
2 Pages V  1 2 >


Custom Search


RSSSearch   Top   Lo-Fi    9th December 2019 - 11:45 AM